How to Highlight and Find Duplicates in Google Sheets

This article describes three ways to identify duplicates in Google Sheets.

Find Duplicates in Google Sheets With Conditional Formatting

The easiest way to clearly see all the duplicate cells in Google Sheets is to highlight them. That way, they pop out from the rest of the data set. Here's how it works using conditional formatting:

1. Highlight the cells you want to search through. If it's a whole column or row, select the letter or number along the edge of the sheet.

2. Go to Format > Conditional formatting.

   

3. Confirm the cell range is what you selected earlier.

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

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

    =countif(A:A,A1)>1
   

6. In the Formatting style section, choose a fill color for the duplicate cells. We've chosen red in this example.
   

   

   Alternatively, you can define the text color by selecting the A in the formatting bar.

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

   

Use a Formula to Hide Duplicate Cells in Google Sheets

The UNIQUE function is handy because it removes all the duplicate data, making it super easy to select only the unique cells. Follow along to see how it works:

1. Select an empty cell next to the column you want to examine.

2. Type =UNIQUE( into the cell, and then select the column letter above your list of data. The formula will automatically add the whole range for you. Your formula will look something like this:
   

    =UNIQUE(C2:C25)
   

   

   If you don't want the first item in your list to be included, probably because it's a column heading and not actual data, feel free to edit your formula like we have (e.g., change C to C2). Or, click and drag to select the specific cells you're interested in, instead of selecting the column letter.

3. Press Enter to close up the formula. The unique data is displayed in that column, starting in the cell where you entered the formula. 

   

Find Duplicates With a Google Add-On

Add-ons are extra little programs you can run within Google Sheets. Among the many that are available are some that are built specifically for finding duplicate information.

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 copy or move 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, install Remove Duplicates by Ablebits or a similar app that allows you to find and highlight your duplicate data, copy the information to another location, and clear duplicate values or delete duplicate rows.