Identifying Duplicate Values in an Excel List

solution_tech
kai zhang/iStock
33

From time to time, you may need to identify duplicate values with a list in Excel. Modern versions, including Excel 2007 and later, provide this capability with just a few mouse clicks.

For good measure, I'll also describe a worksheet function-based approach that works in any version of Excel.

As shown in Figure 1, you can easily identify duplicate invoice numbers in a list. To do so:

  • Select the range of cells you wish to test. One way to do so is to click on a single cell and then press Ctrl-A.
  • On Excel's Home tab, choose Conditional Formatting, Highlight Cells Rules, and then Duplicate Values.
  • Click OK within the Duplicate Values dialog box to identify the duplicate values.
  • Duplicate values in the list will now be identified.

Please Login or Register to read the full article

To access all of the content on our site, register (it's free!) or login to your existing account.

Share this content

Replies

Please login or register to join the discussion.

avatar
By anon
Jun 26th 2015 01:11

Neat. Take a look at ASAP Utilities for a collection of handy tricks such as this. It's been a real time saver.

Thanks (0)
avatar
By David Ringstrom
Authors response
Jun 26th 2015 01:11

Anon, yes, there are numerous free and paid utilities that can enhance Excel, but I tend to focus exclusively on built-in functionality as not everyone has the wherewithal or ability to install add-ins.

Thanks (0)
avatar
By navneet
Jun 26th 2015 01:11

ultimate..!!! i've been wandering for hours just to detect duplicate values in my list of 2595 values. haha.. got 9 duplicates. Thankssssssssssssssssss...

Thanks (0)
avatar
By matteo
DellaCarol Murphy
Jun 26th 2015 01:11

try 20,000 values, almost 1,000 of which are dupes. wow, that was awful

Thanks (0)
avatar
By David Ringstrom
BigVeteran
Jun 26th 2015 01:11

I'm glad you both found my article helpful!

Thanks (0)
avatar
By Mohd Yasir
hannahlucy
Jun 26th 2015 01:11

Hi David,
Please with your work.
i have a problem with my worksheet i have total 45000 lines and in which we have repetaed data in one column.
with different values in corresponding cell.
i have another sheet that have 500 lines in this i have to make all values that are coming in first sheet

Please help me resolve my issues and if possible share your mail ID so that i can explain u further

Thanks (0)
avatar
By David Ringstrom
Jun 26th 2015 01:11

Your question is beyond the scope of what I offer in response to my articles. Use the search term "Excel user forum" to locate any of several locations where you can get free assistance with your situation.

Thanks (0)
David Ringstrom
Jan 12th 2016 16:32

Dear David,

First of all, let me say thank you!
You save the hours of my life!
Your method is working not with numbers only but with words and expressions too.
I've got a list with names and descriptions of more than 10 000 PowerPoint templates and all of them must be unique.
I spent a lot of time to find the easy and sharp description how to solve this problem and your article has opened my eyes.

Thank you very much!

Joanna Miller

PoweredTemplate.com

Thanks (0)
avatar
By Matt
Jun 26th 2015 01:11

Cheers, really handy!

Thanks (0)
avatar
By David Ringstrom
Rob Nance
Jun 26th 2015 01:11

Thanks, Matt! I appreciate the feedback.

Thanks (0)
avatar
By knuknu yu
Jun 26th 2015 01:11

HELP PLEASE!!! I have a list of several names in column B. I want to identify each time that name was duplicated without changing the format of the original entered data. Example: I selected column B, selected conditional formatting, new rule, unique or duplicate value, format, picked a color green, clicked ok. This changed the color to all duplicates making it impossible to find when the name was first entered because it is green as well. I need to first entry to keep the original format but highlight each time it's entered afterwards.

Thanks (0)
avatar
By David Ringstrom
Tax Accountants Sydney
Jun 26th 2015 01:11

In Excel 2007 and later, select column B, choose Conditional Formatting from the Home tab, choose New Rule, and then Use a Formula to Determine which Cells to Format. Enter this formula =COUNTIF($B$1:B1,B1)>1 then click the Format button and choose a color from the Fill tab. Click OK twice. This is a great question, and I'll write up the steps in detail in a forthcoming article that I'll link to this thread.

Thanks (0)
avatar
By knuknu yu
Bunny Jackson
Jun 26th 2015 01:11

Thanks so much. You are wonderful! :)

Thanks (0)
avatar
By David Ringstrom
Josh
Jun 26th 2015 01:11

My pleasure!

Thanks (0)
avatar
By David Ringstrom
Accounting Newark
Jun 26th 2015 01:11

As promised, here's the link to a new article that expands on my comment above. Thanks for giving me a great idea to write about! http://www.accountingweb.com/a...

Thanks (0)
avatar
By dubg
Jun 26th 2015 01:11

Is there a way to search for duplicates for a particular cell say "B3" within a workbook with over 100 worksheets?

Thanks (0)
avatar
By David Ringstrom
Bonnie
Jun 26th 2015 01:11

The only way that comes to mind is to create a macro in Excel that would loop through the sheets and compile a list of any duplicate values. To do this I'd create a collection in my code, which only allows a value to be added once. If that line of code didn't complete cleanly, I'd log the sheet name in my list of duplicates. Hopefully this will help you in your cause, but it'll be meaningless if you're not familiar with Visual Basic for Applications in Excel. The COUNTIF function that I typically use only works within a single sheet, not through multiple sheets like SUM can.

Thanks (0)
avatar
By Preethi
Jun 26th 2015 01:11

Thank you so much.... :) it's helped me a lot... Awesome job... thanks again... :)

Thanks (0)
avatar
By David Ringstrom
Jordan
Jun 26th 2015 01:11

Wonderful! Thank you.

Thanks (0)
avatar
By Heath
Jun 26th 2015 01:11

Am i missing something here.

In the duplicates column the numbers are all different, therefore they are not duplicate?

Thanks (0)
avatar
By David Ringstrom
Accountant Seneca Falls
Jun 26th 2015 01:11

In my illustrations column A has duplicate numbers. You're correct that column B has unique values, but I'm not testing anything in column B, but rather showing how to identify duplicate check numbers, invoice numbers, etc. that might appear such as shown in column A. Thank you for your question, and just let me know if anything is still unclear after this explanation.

Thanks (0)
avatar
By Makesh
Jun 26th 2015 01:11

Different sheet how to check duplicate(Invoice Number)

Thanks (0)
avatar
By ahsan
Jun 26th 2015 01:11

dear sir ! how to got different colors cells for specific duplicites

Thanks (0)
avatar
By ahsan
Jun 26th 2015 01:11

kindly help me

Thanks (0)
avatar
By arun
Jun 26th 2015 01:11

PLS HELP
I have a list of links and i want to check duplicates.

Thanks (0)
avatar
By Wahyu Bram
Jun 26th 2015 01:12

Help me....
I have 4 days member's data. It's thousand data for each day.
I already know how to mark the members who always active for that 4 days. By use format duplicate value.
But i have a problem how to mark the member who not active for the last day (at the 4th day).

Can you help me?
I already try to block the cell which contain members ID who active in the 1st day until the 3rd day and order to found that not equal in the 4th day data.

But the excel said that 'the reference cannot be used in a conditional formating formula. Change the reference to a single cell ora use the reference with a worksheet function, such as =SUM(A1:E5).

What wrong with my step?

Thanks (0)
avatar
By David Ringstrom
Jun 26th 2015 01:12

See part 2 of this article here http://www.accountingweb.com/a.... Use the COUNTIF function as described to test where the result is not equal to 4. If the member ID is in column A, then the criteria for the Conditional Formatting would be: =COUNTIF(A:A,A1)<>4

Thanks (0)
avatar
By Wahyu Bram
Jun 26th 2015 01:12

Members who active in the 1st until 3rd day but not active in the 4th day.

Thanks (0)
avatar
By David Ringstrom
Jun 26th 2015 01:12

You'll do best to create a "helper" column that uses an IF statement or other means to identify the users in question. I've provided as much detail as I can in this venue. You can try posting your question at www.accountingweb.com/forum where it may get attention from other users who can weigh in.

Thanks (0)
avatar
By Prashant Priyadarshi
Jun 26th 2015 01:12

Hello Sir,
I'm wanting an excel spreadsheet to be designed for properties to be inputted so that it shows if the property has a duplicate so for example if we installed double glazing on 1 milton avenue, L14 6TF
and it is installed again 2 weeks later
it will show up on the database that it has been installed and the install date

Thanks (0)
avatar
By David Ringstrom
Jun 26th 2015 01:12

Your question is beyond the scope of what I offer in response to my articles. Use the search term "Excel user forum" to locate any of several locations where you can get free assistance with your situation.

Thanks (0)
avatar
By Slidemodel
Jun 26th 2015 01:12

There are cases when you need to identify values using different criteria. For example, if you have two data lists (i.e. pending users in one list and active users in the other list) you may want to compare and find duplicates from the other list.

In this situation, you can use different comparison criteria, for example finding duplicates by looking at the left part of the email address, the first name and last name. In my case, I use COUNTIF() function in a new column for each condition or criteria I want to consider and defining the range to consider. It will show a positive integer if there is any duplicate. Then, you can sum all these integers in a new column and then you can filter the data to show those that are greater than 0.

Regards,
Julian
http://slidemodel.com/

Thanks (0)
Apr 11th 2016 18:02

This feature is awesome.
Really.
But if you need to select a duplicate phrase or data in cell test the best way is to use filter by column.
Anyway thank you very much for your article !

Thanks (0)