3 Best Ways to Highlight and Remove Duplicates in Google Sheets

As one of the popular spreadsheet applications, you can use Google Sheets for various tasks, from data entry to data analysis. You can also take help of Google Sheets for data cleaning. An example is when you have duplicates of a dataset, you can make use of Google Sheet’s data cleaning functions to clean the dataset.

Google Sheets has different functions to identify the duplicates or clear out the duplicates. You can use either of them depending on your preference. Here are the different ways you can highlight and remove duplicates from your Google Sheets:

How to Highlight Duplicates in Google Sheets Using Conditional Formatting

If you only want to identify the data duplicates in your worksheet, conditional formatting in Google Sheets’ is the best option. Most other data cleaning functions in Google Sheets remove the duplicates directly without leaving the option for removal to the user. Here’s how you can highlight duplicates in Google Sheets with conditional formatting.

Step 1: Open Google Sheets on your preferred web browser.

Open Google Sheets

Step 2: Create a new spreadsheet and enter your dataset or open your existing spreadsheet with the dataset.

Step 3: Select the dataset you would like to highlight duplicates from.

Step 4: On the Ribbon, click the Format tab.

Step 5: Click the Conditional Formatting option to launch a side panel.

Step 6: On the side panel, click the ‘Format cells if’ drop-down.

Step 7: Select ‘Custom formula is.’

Step 8: Enter the following formula in the Value or formula field:

=COUNTIF( Range,Criterion)>1

Where Range refers to the dataset you want Sheets to search from and Criterion is the pattern or test to apply to the dataset. Your formula should look similar to the below:

=COUNTIF(D26:D33,D26)>1

Step 9: In the Formatting style field, select your preferred format.

Step 10: Click Done to save your changes.

You should now see the duplicates in your dataset highlighted and remove them.

Check for Duplicates in Google Sheets Using UNIQUE

Another way you can check for duplicates in Google Sheets is by using the UNIQUE formula. The UNIQUE formula does not highlight the duplicates in your spreadsheet, but it will identify the distinct datasets in your spreadsheet and reproduce them. Here’s how it works:

Step 1: Open Google Sheets on your preferred web browser.

Step 2: Create a new spreadsheet and enter your dataset or open your existing spreadsheet with the dataset.

Step 3: Select an empty cell in the spreadsheet and type:

=UNIQUE

Step 4: Drag over the cells or enter the cell range you want to check unique data for. You should have something like below:

=UNIQUE(D26:D33)

Step 5: Press Enter and the formula should return the unique data in your dataset.

This method is preferable when dealing with a smaller dataset.

Remove Duplicates in Google Sheets Using the Remove Duplicates Tool

When working with a large dataset, you can make use of the Remove Duplicates tool in Google Sheets to clear duplicates. Here’s how to do so.

Step 1: Open Google Sheets on your preferred web browser.

Step 2: Create a new spreadsheet and enter your dataset or open your existing spreadsheet with the dataset.

Step 3: Select the dataset you would like to remove duplicates from.

Step 4: On the Ribbon, click the Data tab.

Step 5: Click the Data clean-up option.

Step 6: Select Remove duplicates. This will launch the Remove duplicates dialog box.

Step 7: Select which columns to include and whether or not the data has headers.

Step 8: Click Remove duplicates.

Google Sheets will remove duplicates and provide a summary of what was removed.

Converting Microsoft Excel Spreadsheet to Google Sheets

If you have to collaborate on a spreadsheet with other users within your organization, you can share a link with them from your OneDrive so they can access it. However, if you need to collaborate with users outside your organization and without an Office account, you can use Google Sheets. You can convert your Excel spreadsheet to Google Sheets and vice versa.