How to highlight duplicates in Google Sheet
First let’s understand the process of finding and highlighting the duplicates in Google sheet with a simple example.
We have the below table in Google Sheet containing different types of dresses along with their price and colour.
Now we need to find if there is any duplicate in the Dress column. If yes then we need to delete the same so that there is only one price for each type of dress irrespective of it’s colour variant.
To find the duplicates we will use the conditional formatting feature of the google Sheet.
- First select the range of the cells where we need to identify the duplicates. Here we have selected B2:B10.
- Now in ‘Format’ menu click ‘Conditional Formatting’
- The ‘Conditional format rules’ pane will appear. In the ‘Single Colour’ tab, the cell range will be auto populated as per your initial selection. You can update the same if required in ‘Apply to range’.
Select the option ‘Custom Formula is’ for the ‘Format Rules’.
- Now we have added the below formula under the custom formula in the ‘Single Colour’ tab of the Conditional Format rules pane.
Here since we are trying to find the duplicates in column B and the first cell of the specified range B2:B10 is B2 hence we mentioned B:B,B2 as the arguments of COUNTIF function.
Now press Done.
- As you can see the duplicate cells have been highlighted. If you click on any cell in the cell range on which your conditional format rule has been applied,the respective conditional formatting rule will also be displayed.
- To change the highlight colour click on your conditional formatting rule and under ‘Formatting Style’ select the desired colour from ‘Fill Colour’.
- Now let’s say we need to find the duplicates based on 2 columns. Column A & B. Means we need to highlight the duplicate values for both Colour and Dress. To do that first select the cell range as A2:B10 and in custom formula add the below formula.
- As you can see below now the duplicate cells in first 2 columns have been highlighted in our table.
How to Delete Duplicates in Google sheet
Now we will see how to remove the duplicates in google sheet we have just identified and highlighted in our example.
Removing Duplicates are simple in Google sheet. You just need to select the cell ranges from where you would like to remove the duplicates. Here we have selected the columns A & B.
And then click ‘Remove Duplicates’ Option available in ‘Data’ menu of the Google Sheet.
Now the below screen will appear where you can select which columns you want to analyse to find and remove duplicates. Since we have already selected the Column A & B, they are pre-selected here. We have selected the checkbox for ‘Data has header row’ as our table has header. This way the duplicates will be removed except from the header row.
Now click Remove duplicates. The below message will appear.
As you can see below the duplicate values in column A & B have been removed. But there is a problem. As we have only selected the first 2 columns and hence after removing duplicates from the first 2 columns now the data in 3rd column are no more correctly mapped, that is the price for each different colour and dress combination has been messed up.
So the learning here is, if there are multiple columns in our table but to remove the duplicate data we need to consider few columns of them not all then we have to select the entire table and then click ‘Remove Duplicates’ Option available in ‘Data’ menu of the Google Sheet.
In other way you can also do it by clicking the Expand option provided in the ‘Remove Duplicates’ pane.
After that you will select only the columns you need to consider for duplicates as we have selected columns A & B to analyse as follows and then clicked Remove Duplicates.
As you can see below everything is properly placed now after removing the duplicate values from first 2 columns.