How to highlight multiple duplicate cells in excel?

With a lot of data…comes a lot of duplicate data.

Duplicates in Excel can cause a lot of troubles. Whether you import data from a database, get it from a colleague, or collate it yourself, duplicates data can always creep in. And if the data you are working with is huge, then it becomes really difficult to find and remove these duplicates in Excel.

In this tutorial, I’ll show you how to find and remove duplicates in Excel.

Duplicates in Excel can come in many forms. You can have it in a single column or multiple columns. There may also be a duplication of an entire row.

Conditional Formatting makes it simple to highlight duplicates in Excel.

Here is how to do it:

Quick Tip: Remember to check for leading or trailing spaces. For example, “John” and “John ” are considered different as the latter has an extra space character in it. A good idea would be to use the TRIM function to clean your data.

If you have data that spans multiple columns and you need to look for duplicates in it, the process is exactly the same as above.

Here is how to do it:

Finding duplicate data and finding duplicate rows of data are 2 different things. Have a look:

Finding duplicate rows is a bit more complex than finding duplicate cells.

Here are the steps:

By doing this, we have created a single string for each row. If there are duplicate rows in this dataset, then these strings would be exactly the same for it.

Now that we have the combined strings for each row, we can use conditional formatting to highlight duplicate strings. A highlighted string implies that the row has a duplicate.

Here are the steps to highlight duplicate strings:

This would highlight the duplicate values in column E.

In the above approach, we have highlighted only the strings that we created.

But what if you want to highlight all the duplicate rows (instead of highlighting cells in one single column)?

Here are the steps to highlight duplicate rows:

This formula would highlight all the rows that have a duplicate.

In the above section, we learned how to find and highlight duplicates in excel. In this section, I will show you how to get rid of these duplicates.

If you have the data in a single column and you want to remove all the duplicates, here are the steps:

This would remove all the duplicate values from the column, and you would have only the unique values.

CAUTION: This alters your data set by removing duplicates. Make sure you have a back-up of the original data set. If you want to extract the unique values at some other location, copy this dataset to that location and then use the above-mentioned steps. Alternatively, you can also use Advanced Filter to extract unique values to some other location.

Suppose you have the data as shown below:

In the above data, row #2 and #16 have the exact same data for Sales Rep, Region, and Amount, but different dates (same is the case with row #10 and #13). This could be an entry error where the same entry has been recorded twice with different dates.

To delete the duplicate row in this case:

This would remove the 2 duplicate entries.

NOTE: This keeps the first occurrence and removes all the remaining duplicate occurrences.

To delete duplicate rows, here are the steps:

Use the above-mentioned techniques to clean your data and get rid of duplicates.

Last week, we explored different ways to identify duplicates in Excel with formulas. Undoubtedly, those solutions are very useful, but highlighting duplicate entries in a certain color could make data analysis even easier.

The fastest way to find and highlight duplicates in Excel is using conditional formatting. The biggest advantage of this method is that it not only shows dupes in the existing data but automatically checks new data for duplicates right when you enter it in a worksheet.

These techniques work in all versions of Excel 365, Excel 2021, Excel 2019, Excel 2016, Excel 2013, Excel 2010 and lower.

• Select the cells you want to check for duplicates.
• Click Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.
• In the box next to values with, pick the formatting you want to apply to the duplicate values, and then click OK.