Have you ever had the experience of copying and pasting information into a spreadsheet and finding that it duplicates itself? It happens when you are working with large amounts of data and can’t keep up with every piece of information. You might even find yourself in a situation where several different people have been working on the same spreadsheet at different times, but not necessarily updating each other.
Due to this communication gap, Google Sheets become messy, But you can solve that problem in just a few minutes. (No Technicality)
One of the most common errors that people new to Google Sheets make is to import data twice, duplicating all the entries in their spreadsheet. Just Download this Template and learn How to find duplicates while reading and executing.
There are multiple ways and steps of highlighting duplicate cells, text and entries.
By exploring every option and method, You will never lose time again or be stuck anywhere.
How to Highlight Duplicate Entries in Google Sheets? | Single Column
If you have lots of data set in your column and want to get rid of duplicate cells just highlight them using this method. (Quickest way to highlight duplicates)
Step 1: Select the column of dataset range
SheetTips: If the column contains a header, avoid selecting it.
Step 2: Click on the format displayed on menu items to start formatting.
Step 3: Click on the Conditional formatting option. (That will launch the conditional format rule sidebar menu)
In the conditional format rule sidebar, you can see two options Apply to range and Format rules.
Step 4: Apply to range select range for your dataset.
Step 5: In format rules click on the drop-down list of Format rules > Format cells if…, And select the Custom formula is (Now Value or formula box will appear)
Step 6: Enter the formula in the box to apply conditions for highlighting duplicates.
We need to use the COUNTIF function for that.
Elaboration of COUNTIF Function > =COUNTIF(Range,Criteria)>1 ,Use formula like this in our case e.g. =COUNTIF($A$2:$A$20,A2)>1
Step 7: In Formatting style Click on Fill Color and select any color you want to make duplicate entries look different.
Step 8: Click on the button that says “Done” to finish the process of highlighting duplicate data.
SheetTips: To select All duplicate cells use the dollar sign ( $ ) and lock cells, And only select duplicate cells one time do not use the dollar sign ( $ ).
See the difference:
How to Find Duplicates In Google Sheets | Multiple Columns
So now if you are in a situation where you have multiple data entries in various columns. And you think there are a few duplicate cells. This step will help you to get rid of that problem.
Note: Cell range will depend on how much data you want to format for any purpose.
Follow the same pattern till the 6th step.
In the 6th step, We will use the same COUNTIF formula to find duplicates in google sheets.
And custom formula will be like e.g. =COUNTIF($A$2:$F$20,A2)>1
SheetTips: In formatting style you can specify the formatting of duplicate cells with other colors and styles such as bold, italic or strikethrough also.
How to Highlight Duplicate Rows In Google Sheets?
Now this is very useful if you have whole multiple duplicate Rows, But sure this one can spin your head for a while, But Don’t worry. It’s easy with SheetTips guidance.
The reason why this one can spin your head is we are not going to check individual cells, But we will check the entire row and highlight those ones where all the cells repeat.
What will happen?
Suppose you have an enormous amount of data and there are multiple Rows with duplications, This will highlight whole rows that are duplicated.
Follow the same steps till you get enter custom formula is..
=COUNTIF(ARRAYFORMULA($A$3:$A$21&$B$3:$B$21&$C$3:$C$21&$D$3:$D$21),$A3&$B3&$C3&$D3)>1
Formula Understanding
In this example, we need to find out which row is duplicated.
In the previous method, we verified if cells are unique or not. The string in cells is unique if not count it as duplicated.
So we will take multiple cells and combine them to consider them as single strings and verify them.
To do that we used ARRAYFORMULA to combine other cells and consider it as a single string e.g. ARRAYFORMULA($A$3:$A$21&$B$3:$B$21&$C$3:$C$21&$D$3:$D$21)
And after that, we used a combined string for criteria e.g. $A3&$B3&$C3&$D3
That will convert row’s cells into single cells and it highlights duplicates.
Editing or Deleting Conditional Formatting Rules
As always our needs change and with that rules needs to be edit or delete to keep all the data up to date.
How to edit Conditional Formatting Rules
To edit the existing rules follow the steps till 4th.
Then Click on the rule you want to edit.
Change your formatting rules and hit on Done.
For to delete Conditional Formatting Rules
To delete the existing rules follow the same steps till 4th.
Then click on the Remove Rule icon to delete that rule.
Using the UNIQUE Function to remove duplicate data
When working with smaller data sets this might be helpful because it helps create new data sets with only unique entries. (This doesn’t highlight duplicates)
We will use UNIQUE Function for that.
The syntax for that is =UNIQUE(Range)
Open the spreadsheet or copy the learning sheet from here.
Step 1: Select the cell in the sheet. e.g. B2
Step 2: Enter the syntax, e.g. =UNIQUE(A2:A20)
Step 3: You will see only unique entries in the new row.
SheetTips Conclusion
Duplicates can be a huge pain in the butt, especially if you’re trying to keep your data clean and organized.Looking for a way to clean up your data sheet, or just want to make it easier to identify duplicates, then try our templates, Copy our Template and learn.