Table of Contents
In order to find duplicate values in Excel without deleting them, one can use the built-in “Conditional Formatting” tool. This tool allows users to highlight duplicate values within a selected range of cells, making it easier to identify and work with these values. Additionally, the “Remove Duplicates” feature can be used to extract the duplicate values into a separate column, without deleting the original values. This allows users to have a clear overview of the duplicate values in their data, while still retaining the original data for analysis and reference purposes.
Excel: Find Duplicate Values without Deleting Them
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.
Additional Resources
The following tutorials explain how to perform other common operations in Excel: