How to find, highlight and remove duplicates in Google Sheets

When entering large amounts of data for long periods, it’s easy to add duplicate entries into your spreadsheet accidentally. That can alter the entire dataset you’ve worked 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

You can also use a formula 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 enter the formula =UNIQUE in an empty cell.

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

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

Select the range of cells where you have applied conditional formatting to remove a conditional formula. 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 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.

Read more: How to find duplicates in Excel

FAQs