The simplest solution to remove duplicate entries in Excel works as follows:

• Go to “Data” in the register menu
• Mark the table in which duplicate entries are to be deleted
• Click Remove Duplicates
• Confirm with OK

Now it could happen that you get the error message that no duplicate entries were found. And that although you can even see them with your own eyes in the table. We deliberately included a few duplicate entries in our example table, but not all values ​​within the individual rows are exactly the same with another row within the selected table. With this general method, it does not work error-free.

If you go to “Duplicate Entries” you will be shown the columns within which to search for duplicates in a dialog window, and you can mark them or exclude them from the selection within which you should search for duplicates. Now if we only include column C

see fig. (click to enlarge)

So as we can see, this blanket method of removing duplicate entries in Excel also involves some pitfalls, and is prone to errors. So you would have to search each column individually. Another approach would be to let us display duplicate entries within rows and tables using conditional formatting without deleting them immediately, and then to decide which ones should be removed.

With conditional formatting, we can display duplicate or unique values within a table, and then decide whether to delete them or keep them. To do this, we proceed as follows:

• Go back to the Home tab
• Select the entire table or optionally individual columns
• From conditional formatting, go to “Row Highlighting Rules” and then to “Duplicate Values”

Here you now have the option of displaying either only unique values or double values and having the relevant cells highlighted in different colors. Whichever option you choose, both times it is an exclusion principle and you come to the solution.

See fig. (click to enlarge)

We hope that we could help you with this little tutorial. If you want to know more about the possibilities of conditional formatting in Excel, we also recommend the following article: Using conditional formatting in Excel correctly