Highlight Duplicates in Multiple Columns in Google Sheets [2023]

Video how to highlight duplicates in google sheets multiple columns

In this article, we will guide you on how to easily highlight duplicates in multiple columns in Google Sheets. By following the simple steps below, you can efficiently identify and organize duplicate data in your spreadsheet.

Highlighting Duplicates in Two Columns

1. Select the Two Columns

To begin, choose the range of the two columns that you want to analyze. If the range is small, you can easily select it using your cursor. However, if the range is large, make a note of it for the subsequent steps. For example, let’s say our range is A2:B7.

2. Click Format, then Select Conditional Formatting

Locate the Format option in the main toolbar and find the Conditional formatting option in the dropdown box that appears.

3. Click the Drop-Down Box under Format rules, then Select Custom formula is

In the Conditional format rules sidebar that appears on the right side of Google Sheets, click the drop-down box under Format rules and select Custom formula is.

4. Insert Formula “=COUNTIF(range, first_cell)” in the Text Box

Now, you need to insert a custom formula in the text box. Use the following format for the formula:

=COUNTIF(range, first_cell)

In the formula, the range represents the range of the two columns you selected earlier, and the first_cell denotes the first cell of the first column. In our example, the range is A2:B7, and the first cell is A2. Therefore, the formula becomes:

=COUNTIF($A$2:$B$7, A2)>1

Note that we convert the range A2:B7 into an absolute reference by adding dollar signs ($). Once you have assembled the formula, copy it and paste it into the text box for the custom formula.

5. Click Done

Google Sheets will automatically refresh the sheet to show you a preview of the conditional format rule in action. Once you are satisfied with the preview, click Done.

By following these steps, you will successfully highlight duplicates across multiple columns within the specified range in your Google Sheets.

FAQs

How to Highlight Duplicates in Multiple Columns (3+)?

To highlight duplicates in more than two columns, simply include the additional columns in the range of conditional formatting and in the formula. For example, if you have three columns:

Set the range to A2:C7:

Modify the formula as follows:

=COUNTIF($A$2:$C$7, A2)>1

Then, click Done.

How to Change Highlight Color?

If you prefer not to use the default color for highlighting duplicates in Google Sheets, you can easily change it. After adding the formula, click the Fill color icon within the Formatting style box.

The color palette will appear, allowing you to choose the color of your preference. For example, if you want to use a violet hue, select the color closest to your desired shade.