Table of Contents
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.
By applying conditional formatting, we have identified all of the duplicate values in column A without deleting any of them.