In Excel, you are usually working with a large dataset, and encountering a duplicate value is really common. It may lead to incorrect formulas or calculations making the data inconsistent or inaccurate.

You can easily spot and highlight duplicates in Excel using a really versatile feature – Conditionally Formatting. If you are not unfamiliar with this feature, it would be a good idea to go through it before we proceed. Click here to learn all about Conditional Formatting.

In this article, you will be covering the following topics in detail –

Let’s look at each of these topics one by one!

Download the Excel Workbook below to follow along and understand how to highlight duplicates in Excel –

DOWNLOAD EXCEL WORKBOOK

 

Highlight Duplicates using Conditional Formatting

There is an in-built feature in conditional formatting that can be used to highlight duplicates. In this example, you have a list of employee names in Column A and you want to highlight the duplicate ones in red. Follow the steps below to apply it –

STEP 1: Select the range that contains duplicate values.
5 Epic Ways to Highlight Duplicates in Excel

STEP 2: Go to the Home Tab > Click on Conditional Formatting in the Styles group.

See also  CTRL + BACKSPACE: Go Back to the Active Cell

5 Epic Ways to Highlight Duplicates in Excel

STEP 3: Select Highlight Cells Rules > Duplicate Values.

5 Epic Ways to Highlight Duplicates in Excel

STEP 4: In the Duplicate Values dialog box, select Duplicate and then the formatting that you want to apply. In this example, let’s select Light Red Fill with Dark Red Text.

5 Epic Ways to Highlight Duplicates in Excel

STEP 5: Click OK.

5 Epic Ways to Highlight Duplicates in Excel

You can see that all the duplicate values in the range will be highlighted based on the formatting that you have selected.

5 Epic Ways to Highlight Duplicates in Excel

Pro Tip – In the dropdown, you can select Unique and all the unique values will be highlighted in the range.

5 Epic Ways to Highlight Duplicates in Excel

 

Highlight Duplicates expect 1st Occurence

In the previous example, what if you want to highlight all duplicates except the first occurrence? Excel has a way around that – you need to use a combination of the COUNTIF function and conditional formatting.

Follow the steps below –

STEP 1: Select the range that contains duplicate values.
5 Epic Ways to Highlight Duplicates in Excel

STEP 2: Go to the Home Tab > Click on Conditional Formatting in the Styles group.

5 Epic Ways to Highlight Duplicates in Excel

STEP 3: Go to New Rule.

5 Epic Ways to Highlight Duplicates in Excel

STEP 4: In the New Formatting Rule dialog box, select Use a formula to determine which cells to format.

5 Epic Ways to Highlight Duplicates in Excel

STEP 5: Enter the following formula in the Edit Rule Description section –

=COUNTIF($A$2:A2,A2)>1

5 Epic Ways to Highlight Duplicates in Excel

Here, we are using a mixed reference range ($A$2:A2) -where the first cell in the reference is locked ($A$2) and the second range is left relative (A2). As you copy the formula down, the range will keep accommodating a new row. So, the COUNTIF formula will check values from the first location ($A$2) till the current location (say, A5) and will avoid the values below (from A6 to A32).

See also  Print Excel header row and column on Every Page

For example, when COUNTIF is running the formula in cell A15, it will count the occurrence of “James Didn’t” from A2 to A15 and will display the result i.e. 2. It will not take into consideration the names mentioned in the range A16 to A32.

5 Epic Ways to Highlight Duplicates in Excel

STEP 6: Click on the Format button.

5 Epic Ways to Highlight Duplicates in Excel

STEP 7: Go to the Fill tab and select a light red color.

5 Epic Ways to Highlight Duplicates in Excel

STEP 8: Go to the Font tab and select a red color.

5 Epic Ways to Highlight Duplicates in Excel

STEP 8: Double-check the formatting for duplicates in the Preview section and press OK.

5 Epic Ways to Highlight Duplicates in Excel

All the duplicates except the first occurrence has been highlighted in red font and light red fill.

5 Epic Ways to Highlight Duplicates in Excel

 

Highlight Duplicate Rows

Until now, we analyzed and highlighted duplicates based on values entered in a cell. The in-built feature of conditional formatting looks for value cell by cell only. Now, we want to look at the entire row’s content and then check for duplicates.

In this example, we want to highlight a row only when values in all three columns are the same for any 2 rows.

5 Epic Ways to Highlight Duplicates in Excel

We will be using the CONCAT function to create a helper column that will help us in highlighting duplicate rows. Follow the steps below to highlight duplicate rows –

See also  Top 3 Methods on How to Merge Excel Files - A Step-by-Step Guide

STEP 1: Go to cell D2 and enter the CONCAT function.

5 Epic Ways to Highlight Duplicates in Excel

STEP 2: Select all three cells that you want to combine. and copy the formula down.

5 Epic Ways to Highlight Duplicates in Excel

We will be using range A2:C32 to highlight duplicates and range D2:D32 to check for duplicates.

STEP 3: Select the range where you want to highlight duplicates i.e. A2:C32.

5 Epic Ways to Highlight Duplicates in Excel

STEP 4: Go to Home > Conditional Formatting > New Rule.

5 Epic Ways to Highlight Duplicates in Excel

STEP 5: In the New Formatting Rule dialog box, enter the following formula

=COUNTIF($D$2:D32,$D2)>1

Now, select the formatting that you want and press OK.

5 Epic Ways to Highlight Duplicates in Excel

All the duplicate rows have been highlighted.

5 Epic Ways to Highlight Duplicates in Excel

 

Remove Duplicates

Instead of highlighting duplicates in the list, you may sometimes want to simply remove those duplicates from the list. Let’s see how it can be done:

STEP 1: Select the range containing duplicate values.

5 Epic Ways to Highlight Duplicates in Excel

STEP 2: Go to Data Remove Duplicates.

5 Epic Ways to Highlight Duplicates in Excel

STEP 3: In the dialog box, check My data has headers and click OK.

5 Epic Ways to Highlight Duplicates in Excel

STEP 4: Excel sends an alert stating the number of duplicates that have been removed and the remaining number of unique values. Click OK.

5 Epic Ways to Highlight Duplicates in Excel

All the duplicates have been removed and the list now contains only unique values.

5 Epic Ways to Highlight Duplicates in Excel

 

Put Unique Values in Another Column

What if we don’t want to highlight duplicates or remove them, we simply want out the unique values in another column?

Excel has a function for this as well- UNIQUE. If you are unable to find this formula, you may be using an older version of Excel. Click here to download Excel 365 and enjoy the benefits of using this amazing function.

See also  Status Bar Metrics

In this example, we have a list of names in column A that contains duplicates. We want to get a list of unique names in column B without making any change in column A. Let’s see how it can be done.

STEP 1: Enter the UNIQUE function in cell B2.

5 Epic Ways to Highlight Duplicates in Excel

STEP 2: Select the data from which you want to remove duplicates.

5 Epic Ways to Highlight Duplicates in Excel

All the unique names will be listed in column B.

5 Epic Ways to Highlight Duplicates in Excel

 

In this article, we have learned how to highlight duplicate values and rows, how to highlight all except 1st occurrence of duplicates, remove duplicates and get unique values in a new column.

Click here for The BEST Microsoft Excel Tips & Tricks EVER, ranging from Formatting, Macros, Formulas, Tables, Pivot Tables, Working with Data plus Many More!

HELPFUL RESOURCES:

JOIN OUR FREE EXCEL COURSES BELOW
Click here to access these FREE Excel courses!

If you like this Excel tip, please share itEmail this to someone

email

Pin on Pinterest

Pinterest

Share on Facebook

Facebook

Tweet about this on Twitter

Twitter

Share on LinkedIn

Linkedin