Highlight Duplicates in Two Columns in Google Sheets (2 Ways)

While working with a large volume of data in Google Sheets one may encounter duplication of data in their table. They might want to highlight duplicates according to their preference. But highlighting duplicates manually can be very tiring and time-consuming if you have a large volume of data. In this article, we will try to show you how you can highlight duplicates in two columns in Google Sheets very easily.

A Sample of Practice Spreadsheet

2 Ways to Highlight Duplicates in Two Columns in Google Sheets

For our approach, Conditional Formatting gives the easiest solution to highlight two columns in Google Sheets.

This feature returns the duplicates between two columns and shows them highlighted. We can access this feature from the format tab:

Format > Conditional formatting

And the Conditional formatting menu looks like this:

We can use conditional formatting in two ways (custom formulas) to achieve our objective.

1. Using MATCH Function Custom Formula in Conditional Formatting

Conditional formatting can be used with the MATCH function to create a custom formula to highlight duplicates in two columns in Google Sheets.

Here we have a dataset with two columns Colors 1 and Colors 2. We want to see if there are any duplicate names from Colors 1 column in the Colors 2 column and highlight the duplicates using Conditional formatting and the MATCH function.

Simply follow these steps:

• First, select the column where you want to highlight the duplicates and go to Menu bar > Format > Conditional formatting. We select column C5:C11 and go to Format > Conditional formatting.

• Then, you should see a sidebar appear from the right named Conditional format rules.

• The sidebar specifies where it is applying the Conditional formatting in the Apply to range box. In our example, it says C5:C11.

• Next, from the Format rules tab from Format cells if box select Custom Formula is option.

• Then, in the Value or formula box type in the MATCH formula based on which the Conditional formatting will highlight the results. We type the following formula:

• Now, you can change the highlighter color if you want. In this case, we select light yellow 2.

• Finally, press Done when you are satisfied with your conditional formatting.

• Your duplicates are now highlighted:

Read More: Conditional Formatting with Checkbox in Google Sheets

Similar Reading 

• How to Copy Conditional Formatting in Google Sheets
• How to Highlight Duplicates for Multiple Columns in Google Sheets
• Conditional Formatting Highlight Duplicates in Google Sheets
• Copy Formatting From One Sheet To Another In Google Sheets (2 Ways)

2. Utilizing the COUNTIF Function Custom Formula in Conditional Formatting

Conditional formatting can be applied along with the COUNTIF function to highlight duplicates in two columns in Google Sheets.

To show this approach, we have a dataset with two columns: Colors 1 and Colors 2. We want to see if there are any duplicate names in the two columns Colors 1 and Colors 2 and highlight the duplicates using Conditional formatting and the COUNTIF function.

Follow these steps carefully:

• First, select the column range where you want to highlight the duplicates and go to Menu bar > Format > Conditional formatting. We select range B5:C12 and go to Format > Conditional formatting.

• Then, you should see a sidebar appear from the right named Conditional format rules.

• The sidebar specifies where it is applying the Conditional formatting in the Apply to range In our example, it says B5:C12 because we already selected B5:C12.

• Next, from the Format rules tab from Format cells if box select Custom Formula is option.

• Then, in the Value or formula box type in the COUNTIF function formula based on which the Conditional format will highlight the results. We type the following formula:

• Next, you can change the highlighter color if you want. We select light yellow 2 in this case.
• Finally, press Done.

• Your duplicates are now highlighted.

Read More: Conditional Formatting with Multiple Conditions Using Custom Formulas in Google Sheets

Tips and Potential Problems with Solution

The following problems you could face while applying Conditional formatting to highlight duplicates in two columns in Google Sheets:

• Extra spaces in the cells may lead to unwanted or non-highlighted cells. For this, you can use the TRIM function to remove extra spaces.
• You must use the necessary cell references to make the highlights work properly. Use absolute cell references ($) when possible.
• Make sure you do not have any other forms of Conditional formatting applied to the cells.

Conclusion

In this article, we showed you how to highlight duplicates in two columns in Google Sheets. We hope this article was useful to you. Keep on practicing the methods that were shown here to get a grip on the concept.

Also, check out other articles on OfficeWheel to keep on improving your Google Sheets work knowledge.

Related Articles

• How to Search in All Sheets in Google Sheets (An Easy Guide)
• Google Sheets: Highlight Row If Cell Is Empty
• Highlight Row If Cell Contains Text with Conditional Formatting in Google Sheets
• Pivot Table Formatting in Google Sheets (3 Easy Ways)
• Google Sheets: Conditional Formatting Row Based on Cell
• Conditional Formatting Based on Another Cell in Google Sheets
• Change Row Color Based on Cell Value in Google Sheets (4 Ways)
• How to Use Formula to Highlight Duplicates in Google Sheets