How to ☝️Highlight Duplicates in Google Sheets

Use Google Sheets like a pro and learn how to highlight duplicate entries in your dataset. 

Why would this be a helpful tool? Suppose you have a long list of items, and you know that some of the items are on there at least twice. By highlighting the duplicates, you can identify the extras quickly—and remove them if necessary.

How to Highlight Duplicates in a Single Column 

Let’s start by taking a look at how to highlight duplicate entries in a single column of data. 

highlight duplicates

1. Highlight the dataset (A2:A15).

2. Go to the Format tab.

3. Select the Conditional formatting option.

conditional formatting option

4. In the Conditional format rules task pane window, select Add another rule. 

add another rule

5. From the Format cells if… drop-down list, choose Custom formula is.

6. Enter the following formula in the box below: =countif(Range,Criteria)>1. In our example, we use the formula =countif($A$2:$A$15,A2)>1, where Range is $A$2:$A$15 and Criteria is A2.

7. Hit the Done button to save the changes.

use a formula

Voila! All the duplicates in the column have turned green. 

duplicates in green

How to Highlight and Find Duplicates in Multiple Columns

Finding and highlighting duplicates in data across multiple columns works just as easily as in one column. There are, however, a couple differences to keep in mind.

One of the key differences is that the Criteria needs to be set to an absolute value, so A2 becomes $A2.

You will also need to make sure you include the entire row, with all columns involved.

In our example, we will continue to use the top careers in healthcare for 2021 and simply add a column for the typical entry-level education for this type of career. 

highlight and find duplicates in multiple columns

To highlight the applicable rows—in this example, incorporating two columns instead of one—follow the steps below: 

1.  Highlight the dataset (A2:B14).

highlight the dataset

2. Click Format in the menu. 

3. From the drop-down menu, select Conditional Formatting. 

use conditional formatting

The Conditional format rules window will appear. 

4. Make sure you have selected the correct range (A2:B14).

5. In the Format rules section, under the Format cells if… drop-down menu, scroll down to choose Custom formula is.

6. In the formula box, enter the duplicate-finding formula. Be sure to add the “$” sign before A2 to adjust the Criteria to an absolute value. It will look like this: =countif($A$2:$A$15,$A2)>1.

7. Click Done. 

use formula to find and highlight duplicates

The new changes have been applied! Now you can see all the duplicates that span both columns. 

If you want, you can change the color of the highlights.

Under the Formatting style box, click on the Fill color icon and apply the color you want. 

change color of highlights

How to Remove Duplicates in Google Sheets

Removing duplicates in Google Sheets is a very simple process. Google Sheets even offers a unique tool to accomplish this. 

Let’s take a look at how to do it. 

1. Highlight the cell range that contains the duplicates you want to remove (A2:B14).

highlight dataset to remove duplicates

2. Navigate to the Data menu. 

3. Choose Data cleanup and select the Remove duplicates function.

remove duplicates

5. If the data has a header row, tick the box Data has a header row in the Remove duplicates box. 

6. Select the columns you want to analyze. 

7. To apply the changes, click on the Remove duplicates button. 

remove duplicates box

After clicking the Remove duplicates button, you will see the result box confirming how many duplicates were removed. 

the result box

Now that the duplicates have been removed, all you have remaining are the unique items in your list. The list has been “cleaned” of duplicates. For those looking for a way to incorporate this into their custom formulas, the UNIQUE function in Google Sheets allows you to do just that.

the dataset without duplicates
More Google Sheets Tutorials
More Microsoft Excel Tutorials
Share This Post
Daniel Smith
Daniel Smith
Daniel Smith is automation consultant with a passion for technology, data, AI, and machine learning. Daniel loves to learn about new technologies and how they can be applied to solve complex problems. He is also a big fan of productivity hacks and enjoys finding ways to automate tasks to make organizations more efficient.