Large tables often have the disadvantage of containing duplicate values that are not wanted. Instead of laboriously finding the individual values, Excel offers us functions to quickly and easily solve this task and filter the data set for unique values. In this tutorial we will see how to identify duplicates and delete them with just a few clicks.
First, we select the record, table, row or column to be examined.
Then we click on the Start tab in the Styles group on the Conditional Formatting button.
Note: More information about conditional formatting can be found in this tutorial.
A menu will open. From here we click on the option “Rules for highlighting cells” and then on “Duplicate values”.
A dialog box appears. We select “light red fill” for double values and confirm by clicking OK.
A look at our dataset reveals the duplicates, which are now displayed in bright red. Values that are unique to the dataset are not shown in red.
Note: The formatting of the numbers and contents is not important here. For example, if we have the same date value in different cells, one of which is formatted as “3/12/2025” and the other as “March 12, 2025”, the values are also unique.
Removing duplicate values
After finding the duplicates, we get an overview and make sure that after removing the data we will also get the desired result. In our example, we highlight column A with an enumeration of cars in a car repair shop. Cell A1 (blue) is the column header.
Note: When you use the Remove Duplicates function, the respective duplicate data is permanently deleted. Before this happens, you should copy the original data to another worksheet so that no data is accidentally lost.
If this is the case, click the Remove Duplicates button on the Data tab in the Data Tools group.
The Remove Duplicates dialog box appears. Here the previously selected columns are listed. We see that the only selected column is listed, since we have a column heading (car stock in cell A1) we put a check mark at Data have headings and click OK.
Excel confirms our the removal of the data.