How to Find & Highlight Duplicates in Google Sheets (5 Methods)

how to find & highlight duplicates in google sheets 47

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.

See also  How To Make a Schedule on Google Sheets [2 Methods]

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.

1

Use Google Sheets Remove Duplicates Feature

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

how to find & highlight duplicates in google sheets 1

Step 2

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

how to find & highlight duplicates in google sheets 2

Step 3

Click on “Remove Duplicates

how to find & highlight duplicates in google sheets 3

Step 4

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

how to find & highlight duplicates in google sheets 4

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

See also  How to Open an Excel file in Google Sheets [Easy Guide]
2

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

how to find & highlight duplicates in google sheets 5

Step 2

Go to Format in the main menu > Conditional Formatting

how to find & highlight duplicates in google sheets 6

Step 3

Select the range of data.

how to find & highlight duplicates in google sheets 7

how to find & highlight duplicates in google sheets 8

how to find & highlight duplicates in google sheets 9

Step 4

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

how to find & highlight duplicates in google sheets 10

how to find & highlight duplicates in google sheets 11

Step 5

Write the formula / condition

=countif(A:A,A1)>1

how to find & highlight duplicates in google sheets 12

how to find & highlight duplicates in google sheets 13

Step 6

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

how to find & highlight duplicates in google sheets 14

how to find & highlight duplicates in google sheets 15

how to find & highlight duplicates in google sheets 16

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

See also  How to Move Rows & Columns in Google Sheets [3 Methods]
3

Using UNIQUE to display the unique values

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.

how to find & highlight duplicates in google sheets 17

Step 2

Start writing the formula

=UNIQUE(A1:A19)

how to find & highlight duplicates in google sheets 19

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

how to find & highlight duplicates in google sheets 20

how to find & highlight duplicates in google sheets 21

how to find & highlight duplicates in google sheets 22

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.

See also  How to Insert Google Sheets Data into Google Docs (Best Practice)
4

Copy Unique Cells in Google Sheets for Easy Removal of Duplicates

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

how to find & highlight duplicates in google sheets 44

 

Step 2

Select any other cell to get the unique values

how to find & highlight duplicates in google sheets 45

Step 3

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

how to find & highlight duplicates in google sheets 46

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

See also  How to Convert Text to Numbers in Google Sheets
5

Use a Third-Party Add-On to Find and Remove Duplicates in Google Sheets

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/

how to find & highlight duplicates in google sheets 23

Step 2

Search for “Remove duplicates” in the search bar

how to find & highlight duplicates in google sheets 24

how to find & highlight duplicates in google sheets 25

Step 3

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

how to find & highlight duplicates in google sheets 26

how to find & highlight duplicates in google sheets 27

how to find & highlight duplicates in google sheets 28

how to find & highlight duplicates in google sheets 29

Step 4

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

how to find & highlight duplicates in google sheets 30

how to find & highlight duplicates in google sheets 31

how to find & highlight duplicates in google sheets 32

Step 5

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

how to find & highlight duplicates in google sheets 33

 

Step 6

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

how to find & highlight duplicates in google sheets 34

how to find & highlight duplicates in google sheets 35

how to find & highlight duplicates in google sheets 36

how to find & highlight duplicates in google sheets 37

how to find & highlight duplicates in google sheets 38

how to find & highlight duplicates in google sheets 39

Step 7

Review the data, the duplicated cells are highlighted.

how to find & highlight duplicates in google sheets 40

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

See also  How to Hide Zero Values in Google Sheets [3 Methods]

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

how to find & highlight duplicates in google sheets 41

Step 2

Go to Format > Conditional formatting

how to find & highlight duplicates in google sheets 42

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.

how to find & highlight duplicates in google sheets 43

To recap, we learned how to find & highlight duplicates in google sheets. This is a vast topic and we saw various methods to find duplicates, we saw how to find duplicates? How to highlight duplicates? How to highlight duplicates in multiple rows and columns using countif? How to use conditional formatting to color duplicate entries? How to use the “Remove duplicates” add-on to automatically remove and highlight duplicates in a spreadsheet document. We discuss how to highlight the cell colors of the columns that have duplicate values, and much more.

See also  How to Make Line Charts in Google Sheets (Step-by-Step Guide)

Tutorial: How to Find & Highlight Duplicated in Google Sheets

how to find & highlight duplicates in google sheets Animation

Frequently Asked Questions

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

See also  How to Connect Google Forms to Google Sheets (With Examples)

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!

Content Protection by DMCA.com

M. Shaiq Ansari

Hi, I am Shaiq. A young and self-motivated content writer having years of experience expertise in MS Office suite, Google docs Editor Suite. I have a technical education background that empowers me to stand out in today's digital world. I am currently a freelance content creator and a part of a local digital marketing agency in Karachi Pakistan.

OfficeDemy.com
Logo
Enable registration in settings - general