This article explains how to highlight duplicates in Google Sheets using three methods.

What to Know

  • Highlight a column. Select Format > Conditional Formatting. Select Custom formula is in the Format cells if menu.Then, enter =countif(A:A,A1)>1 (adjust the letters for the chosen column range). Choose a color in the Formatting Style section.Other methods: Use the UNIQUE formula or an add-on.

How to Find Duplicates in Google Sheets Columns

One way to identify duplicates is to highlight them with color. You can search by column for duplicates and automatically highlight them, either by filling the cells with color or changing the text color. 

  • Open the spreadsheet you want to analyze in Google Sheets.
  • Make sure the spreadsheet has data organized by columns and each column has a heading.
  • Highlight the column you want to search through.
  • Click Format > Conditional Formatting. The Conditional Formatting menu opens on the right.
  • Confirm the cell range is what you selected in Step 2.
  • In the Format cells if drop-down menu, select Custom formula is. A new field appears below it.
  • Enter the following formula in the new field, adjusting the letters for the column range you selected:
  • =countif(A:A,A1)>1
  • In the Formatting style section, choose a fill color for the duplicate cells. In this example, we’ve chosen red.
  • Alternatively, you could change the text color in the duplicate cells instead of filling it with a color. To do this, select the text color icon (the A in the menu bar) and choose your color.
  • Select Done to apply the conditional formatting. All duplicates should now have a red-filled cell.

Find Duplicates With Formulas

You can also use a formula to find the duplicate data in your spreadsheets. This method can work by column or by row and displays the duplicate data in a new column or sheet within your file.

Open the spreadsheet you want to analyze in Google Sheets.

Make sure the spreadsheet has data organized by columns and each column has a heading.

Highlight the column you want to search through.

Click Format > Conditional Formatting. The Conditional Formatting menu opens on the right.

Confirm the cell range is what you selected in Step 2.

In the Format cells if drop-down menu, select Custom formula is. A new field appears below it.

Enter the following formula in the new field, adjusting the letters for the column range you selected:

=countif(A:A,A1)>1

In the Formatting style section, choose a fill color for the duplicate cells. In this example, we’ve chosen red.

Alternatively, you could change the text color in the duplicate cells instead of filling it with a color. To do this, select the text color icon (the A in the menu bar) and choose your color.

Select Done to apply the conditional formatting. All duplicates should now have a red-filled cell.

Find Duplicates in Columns With a Formula

Finding duplicates in columns let you examine a single column of data to see if there is anything in that column that has been duplicated.

  • Open the spreadsheet you wish to analyze.
  • Click into an open cell in the same sheet (for example, the next empty column in the sheet).
  • In that empty cell, enter the following and then press Enter.
  • =UNIQUE
  • The formula feature is activated.
  • Select the column you want to find duplicates in by clicking the letter at the top of the column. The formula will automatically add the column range for you. Your formula will look something like this:
  • =UNIQUE(C2:C25)
  • Type the closing parenthesis in the formula cell (or press Enter) to complete the formula.
  • The unique data is displayed in that column for you, starting in the cell where you entered the formula.

Find Duplicate Rows Using a Formula

The method to find duplicate rows in your spreadsheet is similar, except the range of cells you select to analyze by the formula is different.

Open the spreadsheet you wish to analyze.

Click into an open cell in the same sheet (for example, the next empty column in the sheet).

In that empty cell, enter the following and then press Enter.

=UNIQUE

The formula feature is activated.

Select the column you want to find duplicates in by clicking the letter at the top of the column. The formula will automatically add the column range for you. Your formula will look something like this:

=UNIQUE(C2:C25)

Type the closing parenthesis in the formula cell (or press Enter) to complete the formula.

The unique data is displayed in that column for you, starting in the cell where you entered the formula. 

  • Open the spreadsheet you wish to analyze.
  • Click into an open cell in the same sheet (for example, the next empty column in the sheet).
  • In that empty cell, enter the following and then press Enter.
  • =UNIQUE
  • The formula feature is activated.
  • Select the rows you wish to analyze for duplicates.
  • Press Enter to complete the formula. The duplicate rows are displayed.

Find Duplicates With a Google Add-On

You can also use a Google add-on to find and highlight duplicates in Google Sheets. These add-ons will let you do more with your duplicates, such as identify and delete them; compare data across sheets; ignore header rows; automatically copying or moving unique data to another location; and more. 

Select the rows you wish to analyze for duplicates.

Press Enter to complete the formula. The duplicate rows are displayed.

If you need to address any of these situations or if your data set is more robust than three columns, download Remove Duplicates by Ablebits or a similar app that allows you to find and highlight your duplicate data, copy the duplicate data to another location, and clear duplicate values or delete duplicate rows.

  • How do I remove duplicates in Google Sheets?
  • To remove duplicates in Google Sheets, open a spreadsheet and highlight a data range, then go to Data > Data Cleanup > Remove Duplicates.
  • How do I compare different Google spreadsheets for duplicates?
  • Install Ablebit’s Remove Duplicates add-on for Google Sheets and use the Compare Columns or Sheets tool. Go to Extensions > Remove Duplicates > Compare columns or sheets.

To remove duplicates in Google Sheets, open a spreadsheet and highlight a data range, then go to Data > Data Cleanup > Remove Duplicates.

Install Ablebit’s Remove Duplicates add-on for Google Sheets and use the Compare Columns or Sheets tool. Go to Extensions > Remove Duplicates > Compare columns or sheets.

Get the Latest Tech News Delivered Every Day