Highlight Duplicates in Google Sheets

Software

Updated August 26, 2019

When working with data in spreadsheets, chances are you've got duplicate data in it. Especially a large spreadsheet. Google Sheets has a built-in feature that will delete duplicates, but what if you only wanted to highlight the duplicate data? Fortunately, it's easy to highlight duplicates in Google Sheets using a couple of different methods.

How to Find Duplicates in Google Sheets

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

Before starting, make sure your spreadsheet has data organized by columns and each column has a heading.

Open the spreadsheet you want to analyze in Google Sheets.
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, select Custom formula is. A new field appears below it.
Enter =countif(A:A,A1)>1 in the new field, adjusting the letters for the column range you selected.
In the Formatting style section, choose a fill color for the duplicate cells. In this example, we've chosen red. You could also change the text color in the duplicate cells instead of filling it with a color. To do this, click the text color icon (the A in the menu bar) and choose your color.
Click Done to apply the conditional formatting. All duplicates should now have a red-filled cell.

Find Duplicates in Google Sheets 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 will display the duplicate data in a new column or sheet within your file.

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 int hat 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 =UNIQUE and press Enter. 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, type =UNIQUE and press Enter. 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 in Google Sheets With an 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.

If you need to address any of these situations or if your data set is more robust than three columns, consider using one of the following add-ons.

Each of these add-ons behaves slightly differently, but they all allow you to:

Find and highlight your duplicate data.
Copy the duplicate data to another location, whether that's within the same sheet or file.
Clear duplicate values or delete duplicate rows.