# Identifying Duplicate Values in an Excel List

## solution_tech_kai_zhang.jpg

kai zhang/iStock
Columnist
31

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.

Figure 1:
Excel's Conditional Formatting feature makes it easy to identify duplicate values in a list.

You can then isolate just the duplicates, as shown in Figure 2:
• Right-click on one of the duplicate cells, choose Filter, and then Filter by Selected Cell's Color.
• This collapses the list to show just the duplicate values, which you can copy and paste to another worksheet, or otherwise manage, as shown in the inset in Figure 2.
Figure 2: Excel 2007 and later offer the ability to filter cells based on color.

To remove the conditional formatting, one approach is to click the Conditional Formatting button, choose Clear Rules, and then Clear Rules from Entire Worksheet, as shown in Figure 3.

Figure 3: You can easily clear conditional formatting from a worksheet in Excel 2007 and later.

If you're using Excel 2003,  you don't have the ability to filter by color, but you can isolate duplicate values by way of the COUNTIF worksheet function. COUNTIF has two arguments:
• Range: This is a range of two or more cells that you wish to test.
• Criteria: The value that you're seeking within the range.
Building on the example shown in Figure 1, you can add the following formula to cell C2:

=COUNTIF(A:A,A2)

Once you've entered the formula, double-click on the Fill Handle in cell C2 to copy the formula down the column. The Fill Handle is the little notch that most users drag down a column when copying formulas.

You can then filter the list for any values greater than 1:

Excel 2007 and later
1. Click on cell A1 and then choose Filter on the Data tab of Excel's ribbon.
2. Click the Filter arrow in cell C1, choose Number Filters, and then Greater Than.
3. Enter 1, and then click OK to filter the list to for duplicate values.
Excel 2003 and earlier
1. Click on cell A1, then choose Data, Filter, and then AutoFilter.
2. Click the Filter arrow in cell C1 and then choose Custom.
3. Change Equals to Greater Than, Enter 1, and then click OK.
4.

David H. Ringstrom, CPA heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at [email protected]. David also presents monthly Excel webcasts for AccountingWEB partner CPE Link.

### Replies

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

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.

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

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

Hi David,
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

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.

Cheers, really handy!

Thanks, Matt! I appreciate the feedback.

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.

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 so much. You are wonderful! :)

My pleasure!

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...

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

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.

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

Wonderful! Thank you.

Am i missing something here.

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

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.

Different sheet how to check duplicate(Invoice Number)

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

kindly help me

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

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?

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

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

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.

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

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.

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/