Find duplicate and unique values in Excel
In order to check tables and lists for duplicate data, you can quickly find all repeated or unique values in newer versions of Excel without having to experiment with formulas or filters.
Highlight duplicate values in Excel
- To do this, first select the data area in question in the table.
- Then click on the "Conditional Formatting" icon in the "Start" tab of your ribbon.
- There you select the menu items »Rules for highlighting cells« and then »Duplicate values« (see screenshot).
- The preview in the background already shows the standard formatting of "double" with "light red fill 2".
Adjust the format of the Excel marker
In order to check only one data area, you have already found what you are looking for here. If you click the "Cancel" dialog, your other formats will remain unchanged.
- You can now set the highlighting individually to a different format or adjust it with »user-defined format« according to your needs.
- In addition, you can use the combo box to decide whether »Double« or »Unique« fields should be marked according to your specifications before you click »OK« and Excel does the work.
Note that an exam - as described above - always extends to the entire marked area. If you want to check data column by column, execute the function one after the other for the individual columns. The highlighting also works for text data and enables you to search for duplicate names.