Google Sheets contains a huge amount of data which is managed by various formatting techniques. Sometimes the data contain duplicate data for example the employee’s data contain duplicate names of the employees.
To extract these duplicate entries, we can simply highlight them. This blog is helpful in understanding the method of highlighting duplicates in Google Sheets.
Why Duplicates are highlighted in Google Sheets?
The duplicates are highlighted in Google Sheets majorly because:
- It is easy to find the error by highlighting the data which ensures data accuracy
- It makes it easy to understand the trends of the data which helps in data analysis
- It also helps in data cleaning by deleting the unwanted duplicate entries
How to duplicate entries in Google Sheets be highlighted?
The general formula for highlighting the duplicates in the Google Sheets is:
=COUNTIF(Search Range,Cell reference)>1
In the above formula, replace the “Search Range” with the selected cells and the “Cell reference” with the first cell of the selected cells.
What is the Method to Highlight Duplicates Entries in Google Sheets?
We can highlight the duplicate entries in Google Sheets by following the below-mentioned steps.
Step 1: Open the Google Sheet
First, open the Google Sheet in which the entries should be highlighted:
Step 2: Select the Cell in Google Sheet
Select the cells in which duplicates are supposed to be highlighted. For example, we select the “Student Name” column:
Step 3: Apply “Conditional Formatting” in Google Sheets
After selecting the cells, click on “Format” in the menu bar and then choose “Conditional Formating”:
Step 4: Define the Formula in “Conditional formula rules”
Now a menu appears of the “Conditional formula rules”, choose the “Custom formula is” from the dropdown menu of the “Format cells if”:
In the box of “Value or formula”, type the formula “=countif(B:B,B1)>1”, and then click on the “Done” button:
In the above formula, replace the “B” with the range of the cells and “B1” with the first selected cell. Once the formula is implemented, the duplicates entries in the selected cell will be highlighted:
This is the way by which we can highlight the duplicates in Google Sheets. The highlight colors can also be changed from the “Conditional format rules” menu and to do so, go to the “Formatting style” and change the color:
The color has been changed to green:
The color has been changed successfully.
How can Duplicates in Several Columns in Google Sheets be Highlighted?
To highlight Duplicates in multiple columns, simply select the multiple columns and then condition applies the formula:
=COUNTIF(Search Range,Cell reference)>1
For example, if you want to select the B1:B6 and F1:F6 then you will rewrite the above-mentioned formula:
=COUNTIF($B$1:$F$6,B1)>1
In this way, it will not skip any cell of the mentioned column ranges.
How to Directly Remove the Duplicates From A Column
Here, we have the name “Maddox” two times as shown in the below image:
We simply have to:
- Select the column “A” first.
- Next, click on the “Data” option to open a dropdown menu.
- From that menu, hit on “Data cleanup“; a sub-menu will be opened.
- Tap on the “Remove duplicates” to remove the duplications.
Doing so will show us a warning box; confirm it by pressing the “Remove duplicates”.
We can verify that the duplicated name along with the cell has been removed from column A.
We have provided efficient methods for highlighting and removing the duplicates cells from the Google Sheets spreadsheet.
Conclusion
In Google Sheets, pick the cells, then select “Format” from the menu bar to highlight duplicates. The duplicate entries will be highlighted after you pick “Conditional formatting” and create the formula for the cells that you’ve chosen. Duplicate entries can also be directly removed by selecting the column and “Remove duplicates” option from menu “Data>Data cleanup“. The example in this blog post serves as a demonstration of how to highlight duplicate entries.
