Google Sheets, a cost-free and web-based spreadsheet application, provides users with the ability to generate, modify, and distribute spreadsheets via the internet.
Highlighting duplicates in Google Sheets is a useful feature that can help you quickly identify and remove duplicate data from your spreadsheets.
Why Highlighting Duplicates in Google Sheets is Useful
Duplicate data can cause errors in your calculations and analysis and make it difficult to work with your data effectively. By highlighting duplicates, you can easily spot and remove them, ensuring that your data is accurate and easy to work with. This can save you time and effort and help you make better decisions based on your data.
Step-by-Step Guide: How to Highlight Duplicates in Google Sheets
Highlighting Duplicates in Single Column
All the Duplicates in a Single Column
Here, you’ll learn how to highlight all duplicate values in a column.
Step 1: Select the Cell Range
First select the cell range where you suspect duplicate values exist. You can do this by selecting the cell range as shown in screenshot from A2 to A11.
Step 2: Apply Conditional Formatting
- Next, navigate to the “Format” menu and select “Conditional formatting”.
- In the conditional formatting dialog box, choose the option “Format cells if”.
- Then select “Custom formula is.” option.
Step 3: Enter the Custom Formula
In the input box, enter the below formula:
=countif($A$2:$A,A2)>1
This formula checks if the value in the selected cells appears more than once.
Step 4: Choose Formatting Style
Specify the formatting style you prefer for highlighting duplicate values. It could be a different text color, background color, etc. For now we’re changing cell background color to Light Yellow.
Step 5: Apply Formatting to See the Result
Click “Done” to apply the formatting. Google Sheets will now highlight any duplicate values within the selected cell range based on the chosen formatting style.
All the Duplicates in a Single Column (Except for the First Instance)
Here you’ll learn how to highlight all duplicate values in a column except for the first instance.
In most cases you don’t want to highlight the first duplicate value because you consider it as a Unique Value.
So to acheive this, follow all the same steps above, and only modify the custom formula in Step 03:
=countif($A$2:$A2,A2)>1
This method is very useful when you want to delete only duplicate values, you only need to select rows that contains highlighed cells and delete them.
Highlighting Duplicates in Multiple Columns
All the Duplicates in Multiple Column
Here you’ll learn how to highlight duplicates in multiple columns or entire row, but this only highlights when values in both columns should match.
Step 1: Select the Cell Range
First select the cell range where you suspect duplicate values exist. You can do this by selecting the cell range as shown in screenshot from A2 to B11.
Step 2: Apply Conditional Formatting
- Next, navigate to the “Format” menu and select “Conditional formatting”.
- In the conditional formatting dialog box, choose the option “Format cells if”.
- Then select “Custom formula is.” option.
Step 3: Enter the Custom Formula
In the input box, enter the below formula:
=COUNTIFS($A$2:$A, $A2:$A, $B$2:$B, $B2:$B)>1
Here we are using “COUNTIFS” function, because we are dealing with multiple columns.
This formula will highlight the entire row or both cells if the values in both columns should match.
Step 4: Choose Formatting Style
Specify the formatting style you prefer for highlighting duplicate values. It could be a different text color, background color, etc. For now we’re changing cell background color to Light Yellow.
Step 5: Apply Formatting to See the Result
Click “Done” to apply the formatting. Google Sheets will now highlight the entire row where the values in both columns match.
All the Duplicates in Multiple Column (Except for the First Instance)
Here you’ll learn how to highlight duplicates in multiple columns or entire row except for the first instance.
In most cases you don’t want to highlight the first instance of duplicates because you consider it as a Unique value or row.
So to acheive this, follow all the same steps above, and only modify the custom formula in Step 03:
=COUNTIFS($A$2:$A2, $A2, $B$2:$B2, $B2)>1
This method is very useful when you want to delete only multiple columns duplicates, you only need to select rows that contains highlighed cells and delete them.
Highlighting Duplicates Across All the Cells
Here you’ll learn how to highlight duplicates across all the cells.
Step 1: Select All The Cells
First select all the cells where you suspect duplicate values exist. You can do this by selecting the cell range as shown in screenshot from A1 to C10.
Step 2: Apply Conditional Formatting
- Next, navigate to the “Format” menu and select “Conditional formatting”.
- In the conditional formatting dialog box, choose the option “Format cells if”.
- Then select “Custom formula is.” option.
Step 3: Enter the Custom Formula
In the input box, enter the below formula:
=COUNTIF($A$1:G,Indirect(Address(Row(),Column(),)))>1
Step 4: Choose Formatting Style
Specify the formatting style you prefer for highlighting duplicate values. It could be a different text color, background color, etc. For now we’re changing cell background color to Light Yellow.
Step 5: Apply Formatting to See the Result
Click “Done” to apply the formatting. Google Sheets will now highlight all the cells where the duplicate values exists.
Conclusion
Detecting and dealing with duplicates in Google Sheets is a fundamental aspect of data management. By leveraging the built-in tools and techniques discussed in this article, you can efficiently identify and highlight duplicates.
Also Read: How to Merge Cells in Google Sheets
