Identifying Duplicates in Excel

It’s easy for anyone working with large quantities of data to get lost in the sea of numbers: Values may be missing, in the wrong place or appear multiple times in the table. Sometimes these duplicates are intentionally there, but often they are mistakes. However, in both cases you may want to have these duplicates automatically displayed to you: When dealing with complex tables, it’s a real hassle to have to find these entries manually. So, it’s helpful to know how to find duplicates in Excel.

Excel: Displaying Duplicated Values

Typically, you may want to find duplicates in Excel for one of the following reasons:

  1. Duplicates are there by mistake and should be deleted.
  2. All duplicates are important for analysis and should be highlighted in Excel.
Tip

Read our separate guide to learn how to remove duplicates from datasets.

Excel can find duplicates for you. This way, you don’t need to go through every single row yourself. The spreadsheet program can automatically display duplicates for you. To do so, you first need to select the area you want to check. This could be an entire row or column, or a separate user-defined area. You can use your mouse to select an area by dragging the square box. You can also select individual cells. Simply hold down the [CTRL] key and then click on the relevant cells.

Once you have selected an area for analysis, you can then instruct Excel to identify duplicates. You can do so via Conditional Formatting. You can find this feature in the Styles section of the Home tab. Highlight Cell Rules contains the function for Duplicate Values –you can select which colour you wish to use to highlight the duplicates. You can also instruct Excel to show you the exact opposite: unique values. These are values that only appear once in a table.

The Duplicate Values function highlights all the entries that appear more than once. This means that not only double values are identified, but also values that can be found in three or more instances. If you want to find values that appear three times in a table, Excel has a function for this. All you need to do is define a rule. You can find this function in the Conditional Formatting menu. In this menu, you can define a formula to be used for formatting. If you want to find triplicates for example, you can use the COUNTIF function:

=COUNTIF($A$2:$A$10;A2)=3

You can select the area to be analysed (the first parameters in the formula). You can also specify the first cell of the section in the second parameter: If the content appears three times (=3), the corresponding cells are highlighted. Conditional formatting is automatically applied to the selected cells and checked for the content of the other cells. (For this reason, you have absolute references in the first parameter and relative references in the second.)

You can also completely hide all other cells. This simplifies working with duplicates as they can be identified with ease. A filter is used for this purpose. You can apply a filter to entire columns, which should be selected first. To activate the filter, switch to the Data tab where you’ll find the appropriate button.

The first cell of the column will then show a drop-down menu where you can define the filter criterion. (It is therefore a good idea to work with header rows.) Filter by Colour so that Excel only displays duplicates. Since Excel has color-coded all duplicate values, you can now apply this colour as the filter criterion. This enables you to instruct Excel to only show the duplicates; everything else will be hidden.

Excel with Microsoft 365 and IONOS!

Use Excel to create spreadsheets and organise your data - included in all Microsoft 365 packages!

Office Online
OneDrive with 1TB
24/7 support

We’re all in this together. At IONOS we are
#hereforyou
Coronavirus is a challenge we need to face together.
We've created special offers on key products to help
your business keep going.
3 months free
Online Shop
Be where your customers are with your own online store.