How to Find & Highlight Duplicates in Google Sheets [5 Methods]

In this article, we will learn how to find & highlight duplicates in google sheets.

We have previously seen how to find unique values in google sheets and how to display them with their occurrence today we will do it for duplicates, we will learn how to find them and how to highlight them using various methods. We will also see how to color them using conditional formatting and countif function. So, without further delay, let’s move further.

Use case of finding & highlighting duplicates in google sheets

Most of the time, we have large data sets that have a lot of duplications created, its normal to have duplications in our data, but we must know how to manage the redundancies and maintain normalization in our data, for this purpose we have some methods in google sheets to find out duplicates and highlight them. We need to learn these methods to manage our data duplication and highlight the duplicity to differentiate it from the unique data.

We will see various methods that can be used for different purposes. Let’s get into the step-by-step procedure with screenshots to learn how to find & highlight duplicates in google sheets.

How to Find & Highlight Duplicates in Google Sheets

You have a large data set that has student’s record, their department, id, subjects, etc. We can have a scenario where we have duplications of data for student names, their marks, subjects, etc. We want to find out the repeated entries and highlight them to diversify with the actual data that is unique and non-repeated.

We have various methods to find duplicates and highlight them using multiple formatting and style options.

We can use the google sheets remove duplicates feature to remove duplicates from the range, a range could be a column or multiple columns, this function works accurately for single and multiple column ranges.

We have a dataset that has some student names and their id, there are some duplicate entries we will see how can we use google sheets to “remove duplicate” features.

To understand it, please see the step-by-step procedure below.

Step 1

Identify and select the range of data from which you want to review and remove duplicates

Step 2

Go to Data in the main menu > Data clean-up > Remove duplicates.

Step 3

Click on “Remove Duplicates”

Step 4

Duplicates will be removed and, a pop-up will appear with the information of duplicates removed.

Google sheets remove duplicate features is best when you want to remove the duplicates but many times you only need to highlight them and not remove them directly.

For highlighting we have the below method “Highlight Duplicates using Colors for Easy Removal

Now in this section, we will see how to highlight duplicates using colors for easy removal in google sheets. Now, this is the best method to highlight the duplications to review them, this method does not automatically remove the duplicates. To understand this method simply follow the step-by-step procedure below.

Step 1

Identify and select the range you want to format

Step 2

Go to Format in the main menu > Conditional Formatting

Step 3

Select the range of data.

Step 4

Use Format rules, in the “Format cell if” dropdown, select “Custom formula is”

Step 5

Write the formula / condition

=countif(A:A,A1)>1

Step 6

In the “Formatting Style” section, select any fill color for the duplicates and click on “Done”, and you’re done.

This is how you can highlight the duplicates for easy removal in google sheets.

In this section, we will see how to display unique values in google sheets, in the last section we were showing the count of the unique values, but in this particular section, we will see how we can display each unique value of a range using a unique formula. Let’s see how it is done in a few steps

It’s simple, we only need to count from the above formula and now it will display instead of count only.

Step 1

Identify the range from which you want to display unique values only.

Step 2

Start writing the formula

=UNIQUE(A1:A19)

This is the same formula as we used in the previous section, the only difference is the count keyword which is eliminated to display the unique values.

Step 3

Hit Enter and unique values will be displayed

This is how you can use this simple formula to display only the unique values from a range.

you may have noticed that some duplicates are left by the condition, it is the natural behavior of this function because it detects the duplication from the first column and not for the second, that’s why it may leave some duplicates behind.

In this section, we will see a very simple method to remove duplicates safely. Copy unique cells in google sheets for easy removal of duplicates, this is the safest method that helps us prevent the loss of our data mistakenly. In this method in copy unique data and delete the rest of the data because the remaining data is duplicate data. Let’s implement this method to understand it better

Step 1

Identify the data range you want to extract the unique values from

Step 2

Select any other cell to get the unique values

Step 3

Write the =unique(range) formula and press Enter.

This is how you can extract the unique values from your data range, now you can easily compare and remove the duplicate data safely.

We have many plugins for google sheets available on Google WorkSpace Marketplace. From them, we have a plugin named “Remove Duplicates” by Ablebits. In this section, we will see how to use a third-party Add-on to find and remove duplicates in google sheets.

You can perform many things from Remove duplicate tools, it’s a plugin you can simply install and it will automatically remove duplications using its pre-defined functions. Find, highlight, combine, and remove duplicates in Google Sheets.

To install and go on with this Add-on, simply follow the below steps.

Step 1

Go to Google Workspace Marketplace from the given link below

https://workspace.google.com/marketplace/

Step 2

Search for “Remove duplicates” in the search bar

Step 3

Install the add-on, signup, and grant permission to access sheets after reading the privacy policy

Step 4

Back to your sheet, go to Extension > Add-ons > Manage Add-ons

Step 5

Go to Extension > Add-ons > view document add-ons.

Step 6

A block of the “Remove duplicate” plugin will appear, now you can select different options to remove and highlight duplicates very easily.

Step 7

Review the data, the duplicated cells are highlighted.

This is how we can use a third-party Add-on to find and remove duplicates in Google sheets

How to Highlight Duplicates in Google Sheets – Cells in a Column

In this section, we will see how to highlight duplicates in google sheets – cell in a column. We have already seen many use cases and also we have discussed a quick plugin to find and remove duplicates, now this one is going to be the last section of today’s article. To understand these let’s move on to the step-by-step procedure with screenshots.

Step 1

Identify your range

Step 2

Go to Format > Conditional formatting

Step 3

Follow the steps as we did in the “Highlight Duplicates using Colors for Easy Removal”

Step 4

Set formatting from below formatting style section

Step 5

Click on done and you’re done.

Tutorial: How to Find & Highlight Duplicated in Google Sheets

Frequently Asked Questions

Can I Use the Same Methods to Highlight Duplicates in Google Sheets to Highlight Specific Rows and Columns?

Is There Any Method to Count Unique Values and Find Duplicates in Google Sheets?

Counting unique values and finding duplicates in Google Sheets can be accomplished with various methods. One such method involves using the COUNTUNIQUE function, which allows you to calculate the number of distinct values in a range. To identify duplicates, you can combine the COUNTIF and COUNTA functions to compare the count of a specific cell value with the count of all non-empty cells in a range. By employing these techniques, you can efficiently manage and analyze data in Google Sheets.

What Are the Different Methods for Finding and Highlighting Duplicates in Google Sheets?

To find duplicate entries with conditional formatting in Google Sheets, follow these easy methods. First, select the range where you want to check for duplicates. Then, go to Format > Conditional formatting > Custom formula is and enter =countif(A:A, A1)>1 as the formula, replacing A:A with your desired range. Finally, choose the formatting style to highlight the duplicates.

How can I use Add-on to remove duplicates?

Remove duplicate is a free add-on from google that can be installed and connected with google sheets, it has several functions just by providing the instructions you can remove, compare and highlight eh duplicates within a spreadsheet document.

How can I color the duplicate entries in multiple columns and rows?

Conditional formatting can be used for highlighting the duplicates in multiple rows and columns by cell or text colors. You can use the “custom formula is” rule for this purpose and when providing the data range, you need to provide the complete range ie, A2:D10.

How can I highlight duplicate values in Google sheets with duplicate records?

You can highlight duplicate values in google sheets with duplicate row records similarly as you did it for multiple columns, this is a little tricky because the formula includes ArrayMethod

Below is the formula for highlighting duplicate values in google sheets with duplicate records

=COUNTIF(ARRAYFORMULA($A$2:$A$10&$B$2:$B$10&$C$2:$C$10),$A2&$B2&$C2)>1

Conclusion

Wrapping up the entire learning here, we learned several methods, formulas, and use cases to understand how to find & highlight duplicates in google sheets. We discussed some related questions and answers in the FAQ section. This was a complete guide with easy steps along with screenshots for a better understanding.

I hope you like this article and have learned many things from it. If you enjoy our teaching then support us by sharing the words with your social friends and also consider subscribing Office Demy blog for upcoming learning material regarding Google sheets, MS excel, and much more. Thank you!