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.
1. Highlight the dataset (A2:A15).
2. Go to the Format tab.
3. Select the Conditional formatting option.
4. In the Conditional format rules task pane window, select 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.
Voila! All the duplicates in the column have turned 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.
To highlight the applicable rows—in this example, incorporating two columns instead of one—follow the steps below:
1. Highlight the dataset (A2:B14).
2. Click Format in the menu.
3. From the drop-down menu, select 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.
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.
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).
2. Navigate to the Data menu.
3. Choose Data cleanup and select the Remove duplicates function.
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.
After clicking the Remove duplicates button, you will see the result box confirming how many duplicates were removed.
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.
