How-To

How to Search for Duplicates in Excel

By Adam Davidson

Published September 21, 2022

When you’re working with large amounts of data, it’s easy to miss duplicated entries. Learn how to search for duplicates in Excel using this guide.

There’s nothing worse than entering data into an Excel spreadsheet only to realize that you’ve included some of it more than once. Now you’re going to have to trawl through all that data and find each duplicate one by one, right?

Well, you could, but you don’t have to. Excel can help you to quickly track down, count, and even delete duplicate values within your data, saving you from all the leg work.

If you think you may have some doubled-up data in your spreadsheet, here’s how to search for duplicates in Excel.

How to Find Duplicates in Excel Using Conditional Formatting

One of the most effective ways of immediately seeing which data in your spreadsheet is duplicated is to use conditional formatting. This allows you to apply a formatting theme of your choice to any cells that appear more than once. For example, you can highlight all these cells in red, or change their text to green.

The beauty of this method is that if you apply it to all the cells in your spreadsheet, you can immediately see if the new data you add is a duplicate of another cell. It will also automatically take on the formatting that you specified.

To use conditional formatting to find duplicates in Excel:

1. Highlight the cells where you want to search for duplicate values. To select all cells, press Ctrl+A on Windows or Cmd+A on Mac, or click the square in the very top left of your spreadsheet.
   
2. Under the Home menu, click the Conditional Formatting
   
   
3. Select Highlight Cell Rules.
   
4. Click Duplicate Values.
   
5. Select one of the pre-set formats or click Custom Format to create your own.
   
6. Click OK.
7. All duplicate cells are now highlighted.
   

How to Find Duplicates Without Including the First Occurrence

The method above highlights every instance of cells that have duplicates within the selected range, including the first instance of those cells. However, you may prefer to only highlight repeated instances of cells and leave the first occurrence of that cell unhighlighted. That way you can remove the duplicate values but leave the original in place.

To search for duplicates in Excel ignoring the first occurrence:

1. Highlight the cells containing the data that you want to search for duplicates.
2. In the Home menu, click Conditional Formatting.
   
3. Click New Rule.
   
4. Select Use A Formula To Determine Which Cells To Format.
   
5. Enter the following formula: =COUNTIF($B$2,$B2,B2)>1. Use the row and column values for the first cell in your range. For example, if your first cell is D4, you would use D and 4 in place of B and 2.
   
6. Click Format and select your choice of highlighting for duplicate cells.
   
7. Click OK to apply your conditional formatting.
   
8. Duplicate cells will be highlighted, but the first instance of each value will not.
   

How to Count Duplicates in Excel

If you’re working with large amounts of data, conditional formatting becomes less useful. If your spreadsheet contains columns with hundreds of rows of data, scrolling through the whole sheet looking for any cells that are highlighted can be time-consuming, and you may still miss some.

In this instance, counting the number of duplicate cells can quickly tell you whether there are any in your data or not. The

To count the number of duplicate cells in a column in Excel:

1. Select the cell next to the first value in your column and type =IF(COUNTIF($B$2:$B$23,B2)>1,1,“”) You will need to replace each ‘B’ with the column of your data, each ‘2’ with the row of your first row of data, and ‘23’ with the row of your last row of data in your range.
   
2. Press Enter.
3. This formula will count the number of duplicates of the value it is next to in your entire range of data. If there are duplicates, it will return the value 1. If there are none, it will return an empty cell.
4. Click and hold onto the small square in the bottom-right corner of the cell where you entered your formula.
   
5. Drag down to copy the formula to all the cells in your range.
6. Beneath these cells, select another cell in the same column and type =SUM(C2:C23). You will need to replace C2 and C23 with the first and last cells of your new column.
   
7. Press Enter.
8. Your cell will now show the total number of duplicate cells in the selected column. As you can see, this number matches the number of cells highlighted by our original conditional formatting rule.
   

How to Remove Duplicates in Excel

Sometimes you don’t care where duplicate values are, or how many of them there are. All you want to do is get rid of them. Thankfully, Excel makes that quick and easy to do.

To remove duplicate cells in Excel:

1. Highlight the cells to check for duplicates.
2. Click the Data menu.
   
3. Click the Remove Duplicates icon.
   
4. If you have selected multiple columns, you can check or uncheck each column that you want to remove duplicates.
   
5. If your data has headers, check My Data Has Headers to ensure that these are ignored.
   
6. Click OK.
7. You will see a summary of the number of duplicates removed.
   
8. The first instance of each duplicate value will remain; only the second or higher instances will be deleted.
   

Take Advantage of the Power of Excel

Learning how to search for duplicates in Excel allows you to quickly remove any unwanted duplicate values quickly and easily. As with many features of Excel, it can save you an enormous amount of time that would otherwise be spent tracking them down one by one.

Other useful time-saving features of Excel include finding and removing merged cells, finding circular Excel references, and using the custom sort feature on your data. The more Excel tricks you learn, the more time you can save.