How to Highlight Duplicates in Google Sheets? (2024 Guide)

You can compare hundreds of data on a single document with Google Sheets. However, the Sheets may have duplicate data that affect the effectiveness of your work. 

Highlight Duplicates in Google Sheets

Conditional Formatting is a useful function of Google Sheets that allows you to highlight repeated data within the Sheet. Furthermore, there are multiple formulas you can apply to highlight duplicates in Google Sheets with various conditions.

How to Highlight Duplicates in Google Sheets?

With the conditional formatting function section, you can use the duplication formula to find duplicates in Google Sheets for repeated data. The methods to do so are slightly different if you need to highlight rows, columns, or multiple columns.

For Column

  1. Open the Google Sheet on your browser.
  2. Click on the letter at the top of the column you want to highlight.
  3. Click on Format.
  4. Click on Conditional formatting. An add-on section appears on the right side of the page.
  5. Click on the text box under Apply to range and input the text A:A.
  6. Under the Format rules section, click on the dropdown box under Format cells if
  7. Select Custom formula is at the bottom.
  8. On the textbox with Value or Formula, type in =COUNTIF(A:A,A1)>1
  9. Click on Done.

Note: On the command at steps 5 and 8, A denotes the name of the column to which you want to apply the highlighted rule. So, if you want to apply this rule to a different column, say D, replace A with that column’s name D.

For Rows

  1. Open the Google Sheet on your browser.
  2. Click on the number at the first cell of the row you want to highlight.
  3. Click on Format.
  4. Click on Conditional formatting. On the right side of the page, an Add-on for Conditional format appears.
  5. Click on the text box under Apply to range and input the text 4:4.
  6. Under the Format rules section, click on the dropdown box under Format cells if
  7. Select Custom formula is at the bottom.
  8. On the textbox with Value or Formula, type in =COUNTIF(4:4,A4)>1
  9. Click on Done.

Note: Replace 4 on the command in step 5 with the row number you wish to apply the highlight to.

On the command for duplicates mentioned above, (=COUNTIF(A:A,A1)>1), if you replace 1 at the end with 3, only the row/column with three or more duplicates will be highlighted.

Likewise, you can increase the value at the end to increase the number of duplicates to highlight. 

How to Highlight Data Repeated From Another Column?

Some Google Sheets may have data from column A (or any) repeated on column B (or other columns).

You can apply conditional formatting to a column to highlight cells that are repeated from a different column. Please follow the method mentioned below.

  1. Open the target file on Google Sheets. 
  2. Click on Format from the toolbar at the top.
  3. Click on Conditional Formatting. A conditional formatting add-on will appear on the right side of the sheet.
  4. On the textbox under Apply to range, type B2:B9
  5. Click the textbox under Format cells if 
  6. Choose Custom formula is 
  7. On the Value textbox, =MATCH(B2,A$2:A$12,0)
  8. Click on Done.

Here, B2 is the first cell (After the heading) of the column you want to highlight. Similarly, A$2:A$12 is the range you want to compare column B to. So, you can change their values to compare the columns you want.

How to Highlight Duplicates From Multiple Columns at Once?

The process of creating conditional highlighting for each column on a Google Sheet can take a while if the sheet has several columns.

You can use the steps mentioned below to highlight duplicates from multiple columns at once.

  1. Open the Google Sheet on your browser.
  2. Press and hold Ctrl and click on all the columns you want to check for duplicates.
  3. Click on Format.
  4. Click on Conditional formatting. An add-on section appears on the right side of the page.
  5. Under the Format rules section, click on the dropdown box under Format cells if
  6. Select the Custom formula is at the bottom.
  7. On the textbox with Value or Formula, type in =COUNTIF(A:A,A1)>1
  8. Click on Done.

A in the command “=COUNTIF(A:A,A1)>1” refers to the first column that you select in step 2. So, change the alphabet depending on the column you choose.

Wrapping Up on How to Highlight Duplicates in Google Sheets

So this concludes the tutorial to highlight duplicates in Google Sheets.

If this guide was helpful, we also have articles on transferring the ownership of a Google Doc, deleting all Google Photos, and many more.

Please leave a comment below if you have further queries.

Liam Johnson
Liam Johnson

Liam is a tech enthusiast with a knack for helping others use the internet and computer software. He focuses on "HOW TOs" and "FIXES" at SpllittzTech to make your experience with technology better each day.

Articles: 36

Leave a Reply

Your email address will not be published. Required fields are marked *