How to find and highlight duplicates in Excel

When working with complex tables, it’s rather difficult and time-consuming to find and highlight duplicate entries manually. Whether you are trying to identify an error in your data or simply want to analyse identical values in different cells, it’s helpful to know how to find duplicates in Excel.

How to identify duplicate values in Excel quick guide

1. Go to Home and select the area in your Excel table that should be checked for duplicates.
2. Click on Conditional Formatting.
3. Click on Highlight Cell Rules and then on Duplicate Values….
4. Now, choose how you want the duplicate cells to be formatted.

How to find and format duplicates in Excel

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

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

The instructions presented below can be used with Excel version 2021, 2019 and 2016 as well as with the Microsoft 365 version of Excel.

Finding duplicates in Excel

With Excel, you don’t need to go through every single row yourself to find duplicates. 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 an area that you define yourself. You can use your mouse to select an area by dragging the square box. You can also select individual cells by simply holding down the Ctrl key and then clicking on the relevant cells.

Once you have selected the area you want to analyse, you can use the conditional formatting feature in Excel to show duplicate values. To do this, follow the steps below:

1. Go to Home.
2. Click on Conditional Formatting in the Styles section.
3. Select Highlight Cell Rules and then Duplicate Values.

After you’ve selected Duplicate Values…, you’ll be given the option to select the colour that should be used to highlight the duplicates.

Finding values that appear a specific number of times

With the Duplicate Values feature, all entries that appear more than once are highlighted. This includes values that appear three or four times. If you, for example, only want to find values that appear three times in a table, you can do this by creating a new rule and using the COUNTIF function:

1. Open the Conditional Formatting menu und click on New Rule.
2. Select the option Use a formula to determine which cells to format.
3. Now, you need to enter a formula for the area in your table that you want to check. In our example, we’ll be using the following formula: =COUNTIF($A$2:$A$10,A2)=3 to check if there is a value that appears three times in the cells that we have selected in column A.
4. Click on Format and select the formatting options you want to use to highlight values that appear three times.
5. Click on OK to save the rule and apply it to your table.

The COUNTIF function uses two parameters. The first parameter indicates the area that should be analysed. The second parameter specifies the first cell of the area. The =3 at the end ensures that only those cells are formatted that contain a value that appears three times in the selected area.

Hiding other values when checking duplicates in Excel

You can also go a step further and hide all values that aren’t duplicates. Follow the steps below to use the filter function for the workbook you are using:

1. Select the column that you want to use the filter feature for.
2. In the ribbon, click on Data.
3. Click on Filter in the Sort and Filter section.

The first cell of the column will then show a drop-down menu where you can decide how to filter the data. Since we color-coded all duplicate values in the previous steps, all we have to do now is use the colours to filter the values. Once you have selected the filter criteria, Excel will only show duplicates, and all other values will be hidden.