Columnist
Tags:

# Identifying Duplicate Values in an Excel List

Feb 8th 2013
Columnist
kai zhang/iStock

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. Read Part 2 of this series to learn more ways to identify duplicate items an Excel spreadsheets.

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.

Tags:

## You might also be interested in

### Please login or register to join the discussion.

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 (1)
Replying to Authors response:
By David Ringstrom
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 (2)
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 (1)
Replying to DellaCarol Murphy:
By matteo
Jun 26th 2015 01:11

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

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

I'm glad you both found my article helpful!

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

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

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

Thanks (2)
Replying to David Ringstrom:
By PoweredTemplate
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 (1)
By Matt
Jun 26th 2015 01:11

Cheers, really handy!

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

Thanks, Matt! I appreciate the feedback.

Thanks (0)
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 (1)
Replying to Tax Accountants Sydney:
By David Ringstrom
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)
Replying to Bunny Jackson:
By knuknu yu
Jun 26th 2015 01:11

Thanks so much. You are wonderful! :)

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

My pleasure!

Thanks (0)
Replying to Accounting Newark:
By David Ringstrom
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! https://www.accountingweb.com/a...

Thanks (1)
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)
By David Ringstrom
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)
By Preethi
Jun 26th 2015 01:11

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

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

Wonderful! Thank you.

Thanks (0)
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)
Replying to Accountant Seneca Falls:
By David Ringstrom
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 (1)
By Makesh
Jun 26th 2015 01:11

Different sheet how to check duplicate(Invoice Number)

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

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

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

kindly help me

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

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

Thanks (0)
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)
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)
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 (1)
By Joanna Miller
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 (1)
By karaht
Jan 6th 2017 08:57

I faced a trouble after the below step :

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

After doing the above step, when i remove the filer, the copied / cut duplicate values show all the remaining cells below. Its not just the copied duplicate cells.

Thanks (1)
By chetan
May 13th 2017 07:19

Thank you for this very good filter

Thanks (0)
By paydayoh
Nov 10th 2017 07:31

You can also search the duplicate values using CTRL+F and put your query there. After that you will get all the results that matches your query. This is the most simple and basic task and do not need any specialized tool.

Thanks (0)
By james06henry
Jan 7th 2018 15:06

I had changed some words in my excel sheet and then the forumla I had put to make the sum total of a line and all the data was related to the February 2018 Calendar
If you want to check it then please check above. Please clear my issue soon. Thanks in Advance.

Thanks (0)
By CrystalMccarthy5
Mar 26th 2018 23:07

FINALLY!!!! I've been looking for how to do this forever. Everywhere else that shows how to remove duplicate values does so in such a confusing way

Crystal

Thanks (3)
By CrystalMccarthy5
Apr 3rd 2018 22:53

Hey David,

Is it possible for you to make a tutorial on how to count how many duplicate values exist in a data set? Or could you link to to it if you already have?

Crystal

Thanks (0)
By TanishaFlanigan
Jun 22nd 2018 18:52

Thanks (1)
By TanishaFlanigan
Jun 22nd 2018 18:52

Thanks (0)
By chirayu.raul
Dec 20th 2018 18:03

Hi Excel Master,

I have a data of more than 100 entries, out of that a single entry may have multiple lines in it, every entry has unique no, and every line has its entry no & serial no.

If a specified employee code is found in any of lines in an entry, then all lines of that entry will have a common specified rate.

How ho I form a formula for this in single cell.

Thanks (0)
By BuddyPratt1980
Feb 8th 2019 01:59

This is such a time saver!

Thanks (0)
By sarmayegan
May 29th 2019 01:37

I found something new in this accounting website:
sarmayegan.com

Thanks (0)