How To Highlight Duplicates in Google Sheets
In Google Sheets, you can emphasize duplicate entries by employing conditional formatting alongside the COUNTIF function. In the detailed guide provided below, I'll demonstrate the steps involved. Specifically, I'll explain how to utilize conditional formatting to accentuate duplicates, focus on a particular column, and extend the highlighting across multiple columns.
Table of Contents
How To Highlight Duplicate Values in Google Sheets: Finding Duplicate Rows
Potential Problems When Highlighting Duplicate Cells in Google Sheets
How To Highlight Duplicates in Google Sheets
The primary method for identifying duplicates in Google Sheets is through conditional formatting, which is quite straightforward. Initially, I'll explain the process for a single column. Essentially, you utilize a COUNTIF function to ascertain if a cell is duplicated (Alternatively, the UNIQUE function can be employed for comparable outcomes). Subsequently, you implement conditional formatting according to the findings. An animation illustrating this process is provided
In my animation demonstration, I begin by selecting the "format" dropdown menu. Afterward, I employ a COUNTIF formula along with conditional formatting to emphasize duplicates. Additionally, I provide a detailed guide on eliminating duplicates, eliminating the necessity for a remove duplicates add-on. This thorough guide elucidates the formulas for both highlighting and removing cells with duplicate information from your spreadsheet. Such guidance proves invaluable for data refinement, enabling the display of solely unique values, a practice I frequently employ.
How To Highlight Duplicates in Google Sheets Using a Single Column
To begin, we'll focus on identifying duplicates within a single column. Following that, I'll introduce more advanced techniques, such as detecting duplicates across multiple columns. Here are the steps to highlight duplicate entries within a column:
Choose the dataset (as demonstrated in the above example).
Navigate to "Format" > "Conditional formatting."
Select "Format cells if" and then choose "Custom formula is" from the drop-down menu.
Enter the formula:
=COUNTIF(Search Range,Cell reference)>1
Define the formatting style under “Formatting style,” specify the formatting and click “Done“
To exclude the header row, simply avoid selecting it when choosing the data range. Highlight everything below it to ensure only relevant data is highlighted.
Visual Walk-Through
If you prefer to see step-by-step instructions, I also took screenshots. Here’s how to highlight duplicates with my method to highlight all the names that repeat in Column A.
To highlight duplicates in Google Sheets using a single column:
Select the names dataset (excluding the headers)
Click on “Format” > “Conditional formatting”
Select the “Add another rule” option
4. Ensure the range (where you’ll highlight the duplicates) is correct. If it isn’t, change it from the “Apply to range” section.
Click on “Format cells if” > “Custom formula is”
In the field below, enter the following formula:
=COUNTIF($A$2:$A$10,A2)>1
7. From the “Formatting style” options, specify the formatting option to highlight the duplicate cells.By default, it will use the green color, but you can specify other colors and styles (e.g., bold, italics)
Click “Done”
And here’s how it looks when you’re finished. Note that all the duplicates are now highlighted.
Remember: Conditional Formatting is Dynamic
One great thing about conditional formatting is its dynamic nature. If you happen to change any cell data, the formatting will update automatically. For example, if you remove one of the names that have a duplicate, the highlight from that name (in another cell) will also disappear.
How Does The Highlight Duplicate Formula Work?
Using a custom formula in conditional formatting involves examining each cell with a specified formula to detect duplicates in Google Sheets. To tidy up your data, you might opt to eliminate these duplicates entirely. When the formula evaluates to TRUE for a cell, it applies the designated formatting; if FALSE, the cell remains unaltered. In the given scenario, if any name in the data range is duplicated, the COUNTIF formula returns TRUE and highlights the cell; otherwise, it remains unaffected.
employing COUNTIF and conditional formatting, and offers a step-by-step breakdown.
Note the use of the range: $A$2:$A$10, where the dollar signs ensure the formula examines subsequent cells below for duplicate data. Removing highlighted cells necessitates removing the conditional formatting feature.
To do this, follow the steps below:
Select the cells that have conditional formatting applied
Click on the “Format” option
Click on “Conditional Formatting“
Delete the conditional formatting rule from the pane that opens on the right
How To Highlight Duplicates in Multiple Columns
You can also use conditional formatting to highlight duplicates (in this case, it’s a name that occurs more than once in all three columns).
To highlight duplicate data in multiple columns, follow the steps below:
Select the names dataset (excluding the headers)
Click the “Format” > “Conditional formatting“
Choose the “Add another rule” option
Ensure the range (where you’ll highlight duplicate cells) is correct. If it isn’t, you can change it from the “Apply to range” section.
Click on the “Format cells if” drop-down and then click on the “Custom formula is” option
In the field below, enter the following formula:
=COUNTIF($A$2:$C$10,A2)>1From the “Formatting style” options, specify the formatting to highlight duplicate cells.
By default, it will use the green color, but it’s easy to specify other colors and styles, such as bold or italics
Click on “Done“
Note: The above steps will highlight the cell if a name appears more than once (in all three selected columns). Here’s how it looks once you’ve set up the formula.
How To Highlight Duplicate Values in Google Sheets: Finding Duplicate Rows
So we’ve talked about how to highlight duplicate data in Google Sheets, but we’ve really only covered how to find duplicate cells. What if you want to find whole rows of duplicated data? There’s a solution for that too. Here’s what to do when you want to highlight rows with duplicated data. The key here is array formulas.
In this case, a record will be duplicated providing it has the exact same value in each cell and row (such as rows 2, 4, 7, and 8 in the above example).
You don’t have to check individual cells at this point: You have to check the entire row (and only highlight the rows where all the cells repeat).
Select the dataset (excluding the headers)
Click the “Format” > “Conditional formatting” > “Add another rule”
Choose the “Format cells if” drop-down > “Custom formula is” option
In the field below, enter the following formula:
=COUNTIF(ARRAYFORMULA($A$2:$A$10&$B$2:$B$10&$C$2:$C$10),$A2&$B2&$C2)>1
5. From the “Formatting style” options, specify the formatting for highlighting the duplicate cells.
6. Click on “Done”
How Does ARRAYFORMULA Work with the COUNTIF Formula?
You know I like to break down the syntax of my formulas. That’s how I figured out how to make everything work. Since an entire row is being compared with all other rows, we’ve combined the content of all rows and created a single string for each row. Let me explain in more detail.
The following part of the formula creates an array of strings where all the cell content in a row is combined, using the and sign (&).
=ARRAYFORMULA($A$2:$A$10&$B$2:$B$10&$C$2:$C$10)
This array is used in the COUNTIF formula, and the condition used is again a concatenated string with all the values in a row. This is done using the following formula:
$A2&$B2&$C2
This now has been converted into a simple column-type construct where the COUNTIF function checks how many times this combined string is repeated and will end up highlighting all the records that are duplicated.
How To Show Duplicates in Google Sheets With Added Criteria
Google Sheets can also use most added criteria you could think of to highlight duplicate data. For example, you can set the system to only highlight duplicates for specific values.
The syntax will use the asterisk symbol (*) and the and operator. This will enable the COUNTIF function to use both criteria, using the syntax below:
=(COUNTIF(Range,Criteria)>1) * (New Condition) )
Using our data from previous sections. Let’s pretend we discovered that there were two people named Henry in the marketing department. We updated the ID number of one of them.
We still want to highlight the duplicate employees, so we can add a second condition to be met:
Navigate the conditional formatting rules
Enter the first part of the formula with the range and format:
=(COUNTIF($A$2:$C$10,$A2)>1)
This will show if there are duplicates in the A row but won’t exclude the now mismatched ID of the second Henry, so you’ll need these additional steps:
Use the asterisk symbol (*) and the and operator after the first formula
Add the second condition to the syntax of (COUNTIF(Range,Criteria)>1) but make sure you’re addressing the other row – row C in this case. The whole formula should look like this:
=(COUNTIF($A$2:$C$10,$A2)>1)*(COUNTIF($A$2:$C$10,$C2)>1)
In this case, we will use: =(COUNTIF($A$2:$C$10,$A2)>1) to show whether there are duplicates in the A row
This won’t exclude the now-mismatched ID of the second Henry, so you’ll need these additional steps
Use the asterisk symbol (*) and the and operator after the first formula
Add the second condition to the syntax of (COUNTIF(Range,Criteria)>1), but ensure you’re addressing the other row
In this case, it will be Row C
The whole formula should look like this:
=(COUNTIF($A$2:$C$10,$A2)>1)*(COUNTIF($A$2:$C$10,$C2)>1)
Depending on your needs, you could also:
Use a different after the criteria i.e., >0 or <5
Add a third criteria
Multiple “*” conditions
There’s also a use for concatenation in Google Sheets. I’ve added this to my repertoire for building formulas where I want to highlight duplicates of data based on multiple cells.
How To Edit Conditional Formatting Rules
You may want to keep some duplicates but remove highlighting. The solution is simple: Edit or delete the conditional formatting rule:
Highlight the cells you applied the conditional formatting rule to
Navigate to “Format” > “Conditional formatting”
Click the trash can symbol next to the existing rule
Potential Problems When Highlighting Duplicate Cells in Google Sheets
Occasionally, you might follow all the above steps and use the same formulas — but Google Sheets still doesn’t highlight the duplicates. There are a few issues to check for:
Extra Spaces in the Cells
Are there any extra spaces (e.g., leading, trailing characters) in one cell and not the other?
Since we’re looking for an exact match for two or more cells, any extra spaces will lead to a mismatch. Even if you can see that there is a duplicate, it may not get highlighted.
Note: You can use the TRIM function to remove all the extra space characters.
Incorrect Reference
There are three different kinds of references in Google Sheets:
Absolute references (e.g., $A$1)
Relative references (e.g., A1)
Mixed references (e.g., $A1, A$1)
If a formula calls for one type of reference and you end up using the others, you will likely have an issue. Check the references to ensure Google Sheets highlights the duplicates correctly
Some Tips When Highlighting Duplicates in Google Sheets
Remove conditional formatting rules for the desired cells that can return false results
Remove missing spaces from your searches
Don’t select headers when using an ARRAYFORMULA for highlighting duplicates
Frequently Asked Questions
In order to keep all of the most common questions in one place, I’m listing some below. As always, you can leave a comment if you have anything that’s not covered here. I curated some of these from the comments here and from my YouTube channel.
What Is the Formula for Highlight Duplicates in Google Sheets?
Use the COUNTIF formula inside the conditional formatting menu with the following syntax.
=COUNTIF(range, criterion)
Enter the range you wish to highlight (and >1 as the criterion). As in, if more than one instance exists, highlight the cell. Follow the guide above for a more in-depth look.
How Do I Group Duplicates in Google Sheets?
There’s an easy way to group duplicates in Google Sheets. It’s actually a function all its own. To group duplicates, you can use the SORT function or the “sort shortcut” in the toolbar:
Select the column you wish you group duplicates
Navigate to “Data” > “Sort sheet”
You can use our above method to conditionally format duplicates. You can also sort by color to avoid sorting non-duplicate cells.
How Do I See Duplicates in Google Sheets?
You can view duplicates through conditional formatting in the format menu, as well as a COUNTIF formula. Choose the color you prefer.
How Do I Highlight the Same Cell With Duplicates in Google Sheets?
You can’t search for duplicates in a single cell, but you can quickly find duplicated data across an entire sheet. That’s what I’ve covered with the COUNTIF, UNIQUE, and Conditional Formatting tools.
How Do I Compare Two Columns In Google Sheets to Find Duplicates?
To find duplicates and compare two columns, you can use the COUNTIF formula as a custom formula inside the conditional formatting menu. Simply include the cell references in the formula and use >1 as the criterion.
How Do I Compare Different Google Spreadsheets by Finding Duplicates?
Complicated formulas or scripts are possible, but we recommend combining both Sheets into a single spreadsheet, and then comparing them:
Right-click the sheet name at the bottom of the page.
Hover over “Copy to”
Click “Existing spreadsheet” and select the desired spreadsheet
Use the sheet reference in the COUNTIF formula. For example, if you wanted to use a cell range from Sheet1, you could type =Sheet1!
How Do I Compare Different Google Spreadsheets for Duplicates?
You could do this with complicated formulas or scripts, but the simplest way would be to add the required sheet into a single total spreadsheet and then compare the two sheets. To do this:
Right-click the sheet name at the bottom of the page
Hover over “Copy to“
Click “Existing spreadsheet” and select the desired spreadsheet
Then you can use the sheet reference in the COUNTIF formula. For example, if you wanted to use a cell range from Sheet1, you could type =Sheet1!
Wrapping Up
I hope that I’ve helped you learn how to highlight duplicates in Google Sheets. After all, Spreadsheet Point provides plenty of other Google Sheets tutorials. My goal is to help you quickly find the solution you’re looking for.