How to Highlight Duplicates in Google Sheets and Remove

We know that most of us always do not want to attain duplicate data entries while editing a Google spreadsheet. However, as an added advantage, you can easily highlight duplicates in google sheets. You can also customize the way google sheet highlights a duplicate match as it will make sense to the person who will later read the data. For instance, if you choose an entire row the system can highlight the whole row if it finds one column contains the duplicate value. On the other way, it can also highlight a particular cell that is repetitive and similar.

Now if you are wondering how to highlight duplicates in google sheet then continue your reading with our page.

Single Column Highlighting

First, let’s have a look at highlighting duplicates through the process of conditional formatting. With conditional formatting, highlighting duplicates in a single column seems easier. Here’s the step-by-step guide for google sheet highlight duplicates:

1. After you install google spreadsheet on your smartphone or PC devices, open it and select one particular column of the spreadsheet.

2. For instance, select column B, select a range, (B2:B7), and then go-to format, and after that choose conditional formatting.

3. As part of the format, rules click on the drop-down list and go for the custom formula.

4. In the custom formula section, enter the following value, =countif(B2:B;B2)>1.

5. After that, to change or set a colour for highlighting duplicate data, go below the formatting rules section and choose the formatting style option. In that option select the fill colour icon.

6. Once you select a colour, hit the option done. And you will find that the duplicates are highlighted with the chosen colour in that particular column.

7. Follow the same order for other columns also. Like if you want to highlight duplicates in column D apply the formula, =countif(D 1:D; D1)>1.

Highlighting Duplicates in Mid-Columns

Now, take a glance at how to highlight duplicates in google sheet for the mid-columns.

1. First, choose the middle column you want to find the duplicates. For instance, if you want to fix duplicates in the cells between D2 to D7, select the columns.

2. Then take the cursor in the format option and in that select conditional formatting.

3. In the apply the range section, type down the data range that is D2: D7.

4. In the format, rules select the drop-down list and pick the custom formula.

5. Now type your value in the custom formula, = countif(D2:D7,D2)>1.

6. After that with the similar process mentioned above select your highlight color. Once finished selecting the done option.

 For Multiple Columns

But now if you are thinking, is it possible to highlight duplicates for multiple columns with the same process? Yes, it is possible.  We have also come up with a process you can follow to highlight duplicates in google sheet for multiple columns:

Once you open a google spreadsheet select the multiple columns you wish to highlight.

1. Let’s assume that you want to select columns range A2 to D7. Select the columns and click the above option format and select conditional formatting.

2. Then go to the format rules section and choose the dropdown list and pick conditional formatting.

3. Now, enter the respective value, =countif($A$2:$D$7,A2)>1.

4. Pick the highlighting color and once finished click done.

5. In case you want to select all the columns for highlights, that is from A to Z then apply the value in the formula =countif(A1:Z; A)>1. The rest of the steps are the same.

With the conditional formatting technique, the system checks all the selected cells using the custom formula. And after verifying if the formula turns true for the cells then the duplicates in the chosen columns will be automatically highlighted. And in case if it turns false then you will find no highlighting.

Google Sheet Highlight Duplicates Using Add-Ons:

There’s another way for you to remove duplicate data from the google sheets. This process is pretty simpler than the earlier one. With the support of Google add-ons, you can highlight and remove duplicate entries.

It is an advanced way of finding duplicates because add-ons not only identify duplicates but also help with multiple features.

You can compare data with other sheets or you can also ignore the header rows by using the add-ons. By using an Addon you can also transfer the unique data to another location.

When your data entries are more than four columns then using addons will give you an upper hand. Addons like “Remove data by Ablebits” and “Power tools by Ablebits” are more advanced.

However, note that these addons to highlight duplicates in google sheet can function in a bit different way but their basic motive is to remove and highlight or copying the duplicate data.

Removing the Google Sheet Highlight Duplicates:

Now let’s move forward to the second phase of what you should do with the highlighted duplicate entries. Yes, you guessed it right. The next phase is to remove the highlighted duplicates in the sheet. The steps involved in removing duplicates are easy.

1.First, select the highlighted duplicate cells you wish to remove.

2. Then choose the data selection and then go for the remove duplicates.

3. Following that, you will soon witness a pop-up box on your screen. Here, you simply have to tick the box to next and then untick to the option that the data has a header now.

4. After that choose to remove duplicates and press the mouse button to click done.

5. Continue the similar method for other columns also.

Removing Duplicates with Add-Ons:

You can use add-ons for deleting or removing duplicates for both single or multiple columns. Let’s have a look at removing duplicates in rows.

• After highlighting duplicates, next, go to the add-on extension and then click remove duplicates. After that go to the option to find duplicate or unique rows.

• Once the add-on opens, verify the range (for example, you want A1:C100 to enter it) and go for the next option.

• On the page find: select the duplicates option and then click Next.

• Then select the columns from the search you want to remove duplicates. In case your sheet doesn’t have any header, don’t forget to untick the right side, “my table have header” option. And then go to next.

• Then press to delete and lastly click on finish.

Once done, the add-ons will tell how many duplicate rows are found and have been removed. You will notice that all duplicates have disappeared from your spreadsheet.

Summing Up:

This article will surely answer your query of how to highlight duplicates in google sheet! Finding and highlighting duplicates in a google spreadsheet is not at all a complicated task. You should only apply the formula and other step-by-step processes to easily differentiate the duplicates from the unique data. You can try an alternative by the add-ons which will assist you in finding, comparing, copying, and deleting the duplicates. And lastly, you will easily remove the duplicate by following this step-by-step guide.

Thus, finding, highlighting, and removing duplicates is pretty simple with these step-by-step follow-ups!