Often you may want to find duplicate values in an Excel sheet without deleting them.
Fortunately this is easy to do using the Highlight Cell Rules feature within the Conditional Formatting options.
The following example shows how to do so.
Example: Find Duplicates without Deleting Them in Excel
Suppose we have the following list of basketball team names in Excel:
Suppose we would like to identify all of the duplicate team names without removing them.
To do so, we can highlight the cell range A2:A13 and then click the Conditional Formatting icon on the Home tab along the top ribbon, then click Highlight Cell Rules, then click Duplicate Values from the dropdown menu:
In the new Duplicate Values window that appears, click a style to use for the conditional formatting to apply to duplicate values.
For example, we can choose the default option of Light Red Fill with Dark Red Text:
Once we click OK, all of the duplicate values in column A will be highlighted:
For example:
- The team name Mavs occurs more than once so each occurrence of Mavs is highlighted.
- The team name Warriors occurs more than once so each occurrence of Warriors is highlighted.
- The team name Nuggets only occurs once so it is not highlighted.
And so on.
By applying conditional formatting, we have identified all of the duplicate values in column A without deleting any of them.
Additional Resources
The following tutorials explain how to perform other common operations in Excel:
Excel: How to Combine Duplicate Rows and Sum
Excel: How to Highlight Duplicates in Two Columns
Excel: How to Remove Duplicate Rows Based on Three Columns
