Top 3 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 from Google Sheets to clean up your data. For example, when you have duplicate copies of a dataset, you can utilize Google Sheets' data cleaning features to clean up your dataset. Google Sheets has different features for identifying duplicates or clearing duplicates. You can use either of them depending on your preference. Below are the different ways you can highlight and remove duplicates from Google Sheets.

How to highlight duplicates in Google Sheets using conditional formatting

If you just want to highlight duplicate data in your worksheet, conditional formatting in Google Sheets is the best option. Most other data cleaning functions in Google Sheets remove duplicates directly without leaving the user the option to remove them. Here's how to highlight duplicates in Google Sheets using conditional formatting.

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

Open Google Sheets

Step 2: Create a new spreadsheet and enter your dataset or open an existing spreadsheet using Dataset.

Step 3: Locate Data set Which you want to mark duplicates from.

Step 4: On the ribbon, click the tab coordination.

Step 5: Click an option Conditional Formatting To operate the side panel.

Step 6: In the side panel, click the drop-down menu. "Format cells if".

Step 7: Locate The custom formula is.

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

=COUNTIF(Range,Criterion)>1

Where range refers to the set of data you want to search the spreadsheets for, and criterion is the pattern or test that will be applied to the data set. Your formula should look similar to the following:

=COUNTIF(D26:D33,D26)>1

Step 9: In the pattern field Coordination, Select your preferred format.

Step 10: Click Done to save the changes.

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

Checking for duplicates in Google Sheets using Farid

Another way you can check for duplicates in Google Sheets is to use the unique formula. The unique formula doesn't highlight duplicates in your spreadsheet, but it will identify and reproduce distinct data sets in your spreadsheet. Here's how it works:

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

Step 2: Create a new spreadsheet and enter your dataset or open an existing spreadsheet using Dataset.

Step 3: Locate empty cell In the spreadsheet, type:

=UNIQUE

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

=UNIQUE(D26:D33)

Step 5: Click on Enter The formula will return the unique data in your data set.

This method is preferred when dealing with a smaller data set.

Remove duplicates in Google Sheets using the Duplicate Remover tool

When working with a large dataset, you can use the duplicate removal tool in Google Sheets to remove duplicates. Here's how.

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

Step 2: Create a new spreadsheet and enter your dataset or open an existing spreadsheet using Dataset.

Step 3: Select the dataset you want to remove duplicates Of which.

Step 4: On the ribbon, click the tab data.

Step 5: Click an option Data cleaning.

Step 6: Locate remove duplicatesThis will launch the Remove Duplicates dialog box.

Step 7: Locate columns Which data you want to include and whether the data contains headers or not.

Step 8: Click Remove duplicates.

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

Convert Microsoft Excel spreadsheet to Google Sheets

If you need 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 Excel spreadsheet to Google Sheets And vice versa.