Highlight Duplicates in Google Sheets Between Two Columns

Here you can clearly see John Smith appears twice in Column A. However the important cross column match is that John Smith also appears in Column B. Similarly Sarah Wilson occurs once in each column so she is also a match.

So your actual requirement may therefore be:

• Highlight values in Column A that also exist in Column B.
• Highlight values in Column B that also exist in Column A.
• Ignore values repeated only inside Column A.
• Ignore values repeated only inside Column B.
• Keep the original data unchanged.
• And review the matches visually before deleting anything.

This is very different from simply asking Google Sheets to find duplicate values. This difference is the most important part of highlight duplicates in Google Sheets between two columns because the formula must reflect the comparison you actually want.

Example: Same Value, Different Duplicate Situation

Consider this smaller example you need to review Column A, Column B and other details as shown in table:

This value (ABC101) occurs twice in Column A as you can clearly see in the above table but it should not be highlighted simply because it repeats in Column A. It should only be highlighted when the value is also found in Column B.

This is the key difference between – Duplicates within a column and Matches between two columns.

Google Sheets already provides users with conditional formatting and custom formulas that they can use for applying formatting based on another cell or range.

How to Highlight Duplicates in Google Sheets Between Two Columns

The simplest approach you can use is Conditional Formatting with COUNTIF. Let’s assume:

• Column A contains the first list.
• Column B contains the second list.
• Row 1 contains the headings.
• Your data starts from row 2.

To highlight duplicates in Google Sheets between two columns you need here to create a rule for Column A that checks whether each value exists anywhere in Column B. If you want Quick Methods to Remove Duplicate Rows From CSV File then check this detailed guide how you can easily do this.

Formula for Column A

=AND(A2<>””,COUNTIF($B$2:$B,A2)>0)

This formula does two things:

1. A2<>”” prevents blank cells from being highlighted.
2. COUNTIF($B$2:$B,A2)>0 checks whether the value in A2 exists in Column B.

If the value exists in column B then the result is TRUE and Google Sheets applies the formatting you selected.

Google’s documentation clearly describes the same general process users need to select the range, open conditional formatting, choose “Custom formula is” enter the formula and lastly define the formatting. Now Column A will show the values that also exist in Column B.

How to Highlight Matching Values in Column B Too

If you want both lists to show the matches then you can simply create a second conditional formatting rule and for this you need to.

Select: B2:B1000 and then use this formula: =AND(B2<>””,COUNTIF($A$2:$A,B2)>0)

This will check every value in Column B against Column A. After applying the rule here both columns will show the values that occur in the other column. This gives you a much clearer result when you highlight duplicates in Google Sheets between two columns as you can immediately see the matching values ​​from both datasets.

Why You Should Not Use the Normal Duplicate Rule

This is where many Google Sheets users get confused. Google Sheets can simply highlight repeated values inside a range with a formula such as:

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

Google itself offers COUNTIF as a conditional counting function and they demonstrate its use with conditional formatting  for identifying repeated values.

But that formula answers a different question “Does this value occur more than once in Column A?” and your question is “Does this value from Column A also exist in Column B?”

Therefore when you highlight duplicates in Google Sheets between two columns there formula needs to use the other column as the comparison range check compare logic table for details.

Compare the logic

This small difference can simply change the entire result.

Highlight Duplicates Between Two Columns but Not Duplicates Within a Column

This is one of the most useful variations of the problem just imagine Column A contains the following values:

Now the repeated 1002 values in Column A are not cross column duplicates because 1002 does not appear in Column B. Only 1003 should be highlighted.

The formula: =AND(A2<>””,COUNTIF($B$2:$B,A2)>0) does exactly that. It does not ask whether A2 occurs more than once in Column A. It asks whether A2 exists in Column B.

This makes this approach quite suitable when you need to highlight duplicates between two columns but not duplicates within a column.

How to Compare Two Columns for Matches in Google Sheets

If you only need a formula result rather than colored cells then you can also use COUNTIF. For example you can place this in C2: =IF(A2=””,””,IF(COUNTIF($B$2:$B,A2)>0,”Match”,”No Match”)) You can then fill the formula downward once it done the result will look like:

This approach is useful if you want an explicit status column rather than only relying on color formatting. If your objective is to google sheets compare two columns for matches then this method makes the result easier for you to filter, count and audit.

How to Count Duplicates in Google Sheets Between Two Columns

Sometimes highlighting is only the first step and some users may also want to know “How many values from Column A are present in Column B?” so if this your case then you can use this given formula:

=SUMPRODUCT(–(A2:A1000<>””),–(COUNTIF(B2:B1000,A2:A1000)>0))

This will count all non empty cells in Column A whose values occur in Column B. However there is an important difference which you need to know if Column A contains:

In this case the formula counts the matching cells in column A. Therefore now the value 1001 can contribute more than once since it appears twice in column A. If you need the count of unique matching values well the logic needs to be different.

For example: =COUNTUNIQUE(FILTER(A2:A1000,COUNTIF(B2:B1000,A2:A1000)>0))

Google Sheets provides COUNTUNIQUE for counting unique values in a range or collection of values. So before you decide how to count duplicates in Google Sheets you must first decide whether you want to count Matching cells, Matching unique values, Total occurrences, Duplicate records or simply entire matching rows.

At that point a spreadsheet formula may tell you which values match but you still have to manually review, remove, export and organize the duplicate records. Google also notes that as the data range increases then conditional formatting calculations may take longer than expected because the rules are repeatedly checked across the entire range.

So the question changes from “Can Google Sheets find the matches? To “What is the safest and most efficient way to clean this dataset?”

When a CSV Duplicate Cleaner Makes More Sense

If your original data comes from CRM exports, databases, marketing systems, accounting applications or other CSV sources then it might be best to clean your CSV file before you importing it into Google Sheets.

This is particularly useful when your objective has moved beyond simply trying to highlight duplicates in Google Sheets between two columns.

For example instead of manually comparing you can follow this process:

CSV File 1 → Google Sheets → Formula → Review → Delete → Export

you can work directly with the source CSV datasets and easily identify duplicate records based on the fields that matter.

Frequently Asked Questions

Q1. How do I highlight duplicates in Google Sheets between two columns?

You need to select the first column then open Format > Conditional formatting and choose Custom formula is and now use a formula =AND(A2<>””,COUNTIF($B$2:$B,A2)>0) and then create a similar rule for Column B if you want both columns highlighted.

Q2. How do I count duplicates in Google Sheets?

It completely depends on what you exactly mean by duplicates. COUNTIF can count occurrences matching a criterion by the time COUNTUNIQUE can count distinct values. For cross column analysis you first need to decide whether you want the number of matching cells or the number of unique matching values.

Q3. Can I highlight duplicates between two columns but ignore duplicates within each column?

Yes. This is one of the main reasons to use a cross column COUNTIF formula. Here the formula checks whether the current value exists in the other column or not rather than counting how many times it appears in its own column.

Final Takeaway

What you actually want to identify well this should be your first step to understand first your need. If you simply want to find repeated values within one column then a normal duplicate rule can work. But when you need to highlight duplicates in Google Sheets between two columns here the comparison should be made between the two lists not within the same column.

If you have a small dataset you can use the formula based method explained above to quickly identify matching values between the columns. This is usually enough when you only have a limited number of records to review.

However if you regularly work with large datasets, multiple CSV files or you need to clean duplicate records then a dedicated tool can make the process much easier. SysTools provides a dedicated solution with multiple advanced features so you will be able to clean in just a few clicks. It also comes at an affordable price making it a very practical option when manual spreadsheet work starts taking too much time.