How to show duplicates in Excel

A very common request in any of my Excel courses is “how to show duplicates in Excel”. When we are working with any data set you may need to be alerted when duplicate records exist. This process is surprisingly simple to set up and have Excel alert you with a change in formatting when a duplicate exists.

This first process will only alert or show you where duplicates exist. If you want to then delete any double up in records, you can use a different function to do that (which we’ll also go through).

The first process we will look at involves using one of my favourite functions in Excel, conditional formatting. Conditional formatting allows you have text or a cell change formatting based on criteria, or rules that you set. I have quite a few conditional formatting based posts on the site already so be sure to check those out as well:

• How to use conditional formatting in Excel
• Excel Conditional Formatting Based on Dates (Highlight Older Than Today)
• Use conditional formatting to identify dates within a specific range
• How to apply conditional formatting to an entire row based on dates older than today

To show duplicates in Excel:

1. Open Microsoft Excel.
2. Open an existing workbook containing the data you want to check.
3. Highlight the column or row containing the data you want to check for duplicates:

4. From the Home tab on the ribbon, click the Conditional Formatting button:

5. From the menu go to Highlight Cell Rules, then choose Duplicate Values.
6. The Duplicate Values dialog will appear:

7. By default, this will highlight any Duplicate values. You can also change it to highlight all Unique values.
8. Excel will automatically choose to higlight duplicates in Light Red Fill with Dark Red Text.
9. Use the drop-down menu to change to another formatting option or select Custom Format to choose your own colours.
10. Once you have chosen the formatting, click OK.
11. Any duplicate values will now be highlighted in the formatting you chose:

All you have done now is format the duplicate records with a colour. What if you want to physically delete the duplicated copies of these records? Let’s look at the next option.

To remove the conditional formatting:

1. Highlight the column or row which contains the conditional formatting.
2. From the Home tab on the ribbon, click the Conditional Formatting button.
3. Choose Clear Rules > Clear Rules from Selected Cells.
4. The conditional formatting will be removed.

Remove duplicates in Excel

One of the features that Excel includes is the use of a Table. A table provides the ability to delete duplicate records automatically. This removes the need to manually go through your data set to identify and then remove records which can be time consuming.

Formatting your data as a table in Excel makes it easier to work with compared to a standard range because it adds built-in structure and tools. Tables automatically include filtering and sorting options on every column, and they expand as you add new data, so you don’t need to keep updating ranges. They also apply consistent formatting, which makes your data easier to read and helps you quickly spot patterns like duplicate values.

Tables also make data management more reliable. Features like “Remove Duplicates” work more cleanly because Excel understands the table as a defined dataset, and formulas use clear column names instead of cell references. This reduces errors, makes your spreadsheets easier to maintain, and helps ensure you don’t accidentally miss or exclude data when cleaning up duplicates.

I ALWAYS recommend checking for duplicates using conditional formatting FIRST, before having Excel remove it as part of the Table feature. Excel does NOT ask you to confirm which records it will delete – so it’s good to visually check yourself first to make sure you are happy for Excel to go ahead and delete them.

To remove duplicates in a Table:

1. Click inside the data area.
2. From the Home tab on the ribbon, click the Format as Table button (directly to the right of the Conditional Formatting button):

3. Choose a design from the options.
4. The Create Table dialog box will appear:

5. Make sure Excel has found the entire data area.
6. Click OK.
7. Now that the data is formatted as a table, select the Table Design tab from the ribbon.
8. Click the Remove Duplicates button:

9. The Remove Duplicates dialog box will appear:

10. You now need to select which column(s) you want to check for duplicates. If you want the data across all columns to have to be a duplicate, then leave all columns selected. If you are only looking for duplicate information in one column e.g. duplicate DOB then just select the required column.
11. Click OK.
12. You will receive a confirmation notice to advise you on how many duplicates were found and removed, and how many unique values remain:

13. Click OK.
14. The duplicate records have now been deleted.

Using tables alongside tools like conditional formatting gives you two great ways to manage duplicate data in Excel. By combining these approaches, you can spend less time managing your data and more time using it with confidence.

Feel free to leave any questions below or check out some of my other Microsoft Excel tips and tricks.