How to find, highlight and remove duplicates in Google Sheets

When entering large amounts of data for long periods, it’s easy to accidentally add duplicate entries into your spreadsheet. That can alter the entire dataset you’ve been working so hard on, with calculation errors. Fortunately, there are a few easy methods available to identify duplicates. Here’s how to find, highlight, and remove duplicates in Google Sheets.

Read more: How to add, hide, or remove columns and rows in Google Sheets

How to find and highlight duplicates in Google Sheets

One way to easily spot duplicates is to highlight them with a color.

First, open the spreadsheet you want to analyze and organize the data in columns. Next, highlight the column you want to sort through. In this example, we have clicked A to highlight that column.

Click Format–>Conditional formatting. The conditional formatting menu will open on the right side of the screen.

Next, enter formula =countif(A:A,A1)>1 in the new field, and adjust the letters for the column range you selected. Finally, choose a fill color for the duplicate cells in the Formatting style section. In this example, we’ve chosen red since it stands out.

From here, you can also edit the text and font of the results of the conditional formula for better visibility. Lastly, click Done. All duplicates should now have a red-filled cell.

To remove highlighting duplicates, click on the column, and under Formatting, click Clear formatting.

Adding a conditional formula

There’s also a formula you can use to find any duplicate data entries in your spreadsheets. This method can work by column or row and copies the data without duplicates into a new column row.

Open the spreadsheet you want to analyze, and then in an empty cell, enter the formula =UNIQUE.

Then, select the column you want to find duplicates in by clicking the letter at the top of the column. The column range will then be automatically added to the formula. Lastly, press Enter to complete the formula.

The unique data is displayed in that column for you, meaning a new column containing the same information without duplicates is created in the cell where you entered the formula.

Removing a conditional formula

To remove a conditional formula, select the range of cells where you have applied conditional formatting. You will see any rules you created in the sidebar to the right.

Hover your mouse over the condition you wish to delete and click the Remove icon represented by the trash can to clear it.

If you don’t remember the exact column or row you formatted, or if you want to get rid of all formats quickly, you can select the cell range, go to the Format menu, and click Clear formatting.

How to remove duplicates in Google Sheets

Whether you are trying to find duplicates in one or two columns or an entire worksheet, the Remove Duplicates feature reliably removes cells with the same data. However, be aware that it removes all duplicates, even if they are not associated with the same data.

First, highlight the column or columns you want to check for duplicates. Then, in the menu at the top, select Data–>Data cleanup–>Remove duplicates.

In the following pop-up window, check the boxes next to each column in the list you want to analyze, check off Select All, and then Remove duplicates.

Google Sheets displays how many repetitions were found and removed so that you can ensure the process worked as intended.

To make a duplicate copy of a Google spreadsheet, right-click on a sheet name at the bottom of the document, select Copy to from the menu, then choose either New spreadsheet or Existing spreadsheet.