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.
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.
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.
The Conditional format rules sidebar will appear on the right side of Google Sheets.
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.
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.
The formula will now highlight duplicates across the multiple columns in your specified range .
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:
The range should be set to A2:C7.
The formula will be changed to
=COUNTIF($A$2:$C$7,A2)>1
Then press Done.
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.
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.
A preview will automatically be shown. If you are satisfied, click Done. We have now changed the highlight color to violet.
If you enjoyed this article, you might also like our article on how to highlight duplicates in Google Sheets or our article on how to set up conditional formatting if another cell contains text in Google Sheets.
If you want to learn how to set up email notifications on Google Sheets, we also suggest checking out our detailed guide.
