.png)
Highlight Duplicates in Multiple Columns in Google Sheets [2023]
In this article we will show you how to highlight duplicates in two or more columns in google sheets so you can highlight duplicates in multiple columns with ease. Simply follow the steps below.
How to Highlight Duplicates in Two Columns
1. Select the Two Columns
You can either select the range or take note of the range if it is a large range. For our example, the range is A2:B7. This is a small range so we can simply select it using our cursor. However, take note of the range since we will use it in the next steps.
google sheets highlight duplicates in two columns, select range
2. Click Format, then Select Conditional formatting
The Format option is located in the main toolbar and the Conditional formatting option is near the end of the drop-down box that will appear.
google sheets highlight duplicates in two columns, click format, select conditional formatting
The Conditional format rules sidebar will appear on the right side of Google Sheets.
google sheets highlight duplicates in two columns, conditional format rules sidebar visible
3. Click the Drop-Down Box under Format rules, then Select Custom formula is
highlight duplicates in google sheets two columns, select custom format as format rule
4. Insert Formula =COUNTIF(range, first_cell) in the Text Box
We will now insert the custom formula. It should have the following format:
=COUNTIF(range, first_cell)
Where the range covers the range of the two columns while the first_cell is the first cell of the first column. For our example, the range is A2:B7 while the first cell is A2. The formula becomes
=COUNTIF($A$2:$B$7,A2)>1
Where A2:B7 is converted to an absolute reference by inserting dollar signs $: $A$2:$B$7. Once you assemble the formula, copy it and then paste it to the text box for the custom formula.
highlight duplicates in google sheets two columns, formula inserted
5. Click Done
Google Sheets will automatically refresh the sheet to show a preview of the conditional format rule in action. Once you are happy with it, click Done.
highlight duplicates in google sheets two columns, done button at the end of the sidebar
The formula will now highlight duplicates across the multiple columns in your specified range .
highlight duplicates in two columns google sheets result
FAQs
How to Highlight Duplicates in Multiple Columns (3+)?
To highlight duplicates in more than two columns, just include the other columns in the range of conditional formatting and in the formula to add. For example, we have three columns:
google sheets highlight duplicates in multiple columns, multiple columns shown
The range should be set to A2:C7.
google sheets highlight duplicates in multiple columns, range of multiple columns selected
The formula will be changed to
=COUNTIF($A$2:$C$7,A2)>1
google sheets highlight duplicates in multiple columns, modified formula added
Then press Done.
google sheets highlight duplicates in multiple columns result
How to Change Highlight Color?
If you don’t want to use the default color set by Google Sheets to highlight duplicates, you have the option of changing it alongside other formatting options. After adding the formula, click the Fill color icon inside the Formatting style box.
how to change highlight duplicates color in google sheets multiple columns
The color palette will appear where you can choose the color you want. For our example, we want to change the highlight color to violet. Select the color closest to the hue of violet that you want.
how to change highlight duplicates color in google sheets multiple columns, select from color palette
A preview will automatically be shown. If you are satisfied, click Done. We have now changed the highlight color to violet.
how to change highlight duplicates color in google sheets multiple columns, results shown
