When working with large databases in Microsoft Excel—such as a list of email subscribers, inventory SKUs, or employee ID numbers—identifying duplicate entries is a critical step in data cleaning. While Excel has a tool to instantly delete duplicates (Data > Remove Duplicates), you rarely want to delete data without reviewing it first. Highlighting duplicates allows you to visually identify recurring values so you can investigate why they exist before deciding how to handle them.
Why Highlight Instead of Delete?
Data entry errors are common, but a duplicate value is not always an error. For example, if you are analyzing a list of customer orders, seeing a customer’s name appear twice might mean they placed two separate orders, which is valuable information. If you immediately use the “Remove Duplicates” tool, you delete that second order. Highlighting the duplicates with a bright color brings them to your attention, allowing you to manually verify the surrounding data before taking destructive action.
Step-by-Step: Highlighting Duplicates with Conditional Formatting
Excel handles this task beautifully using its Conditional Formatting engine.
- Open your Excel worksheet.
- Highlight the specific column or range of cells you want to check for duplicates. (If you want to check an entire column, click the column letter at the top, like ‘A’ or ‘B’).
- Navigate to the Home tab on the ribbon.
- Look for the Styles group and click the Conditional Formatting button.
- Hover your mouse over Highlight Cells Rules in the drop-down menu.
- Select Duplicate Values… from the sub-menu.
- A dialogue box will appear. The left drop-down will be set to ‘Duplicate’. The right drop-down allows you to choose a color scheme. The default is usually “Light Red Fill with Dark Red Text”, which provides excellent visibility.
- Click OK.
Excel will instantly apply the red formatting to any cell in your selected range that contains a value matching another cell in that same range. As this is conditional formatting, it is dynamic; if you change a duplicate value so it is unique, the red highlighting will automatically disappear.
How to Find Unique Values Instead
Occasionally, you need to find the opposite: identifying the outliers that only appear once in a list.
- Highlight your data range.
- Go to Conditional Formatting > Highlight Cells Rules > Duplicate Values…
- In the dialogue box, change the first drop-down menu from ‘Duplicate’ to Unique.
- Choose a color and click OK.
Now, only the items that appear exactly once will be highlighted.
Troubleshooting Common Mistakes
If the Conditional Formatting tool is highlighting cells you believe are unique, or failing to highlight obvious duplicates, check the following:
- Trailing Spaces: Excel’s duplicate detector is exact. If cell A2 contains “Smith” and cell A3 contains “Smith ” (with a hidden space at the end), Excel considers them unique values and will not highlight them. You must clean your data using the TRIM function to remove accidental spaces before checking for duplicates.
- Highlighting across multiple columns: If you highlight columns A and B and apply the Duplicate rule, Excel searches the entire block. If “John” is in A2 and “John” is in B5, both will be highlighted. If you want to find entire rows that are duplicates (where John Smith in Row 2 matches John Smith in Row 5), standard conditional formatting cannot do this easily. You would need to create a helper column that concatenates A and B (e.g.,
=A2&B2) and then check that new column for duplicates.
By leveraging Conditional Formatting to highlight duplicates, you retain full control over your data auditing process, ensuring accuracy before making permanent deletions.