Find Duplicates Between Two Columns in Google Sheets

[nerdslife_team]

Hey everyone, when you’re knee-deep in data using Google Sheets, dealing with duplicates can be a real headache. Some duplicates are intentional, sure, but others are just errors, especially when you’re collaborating with others on the same sheet. Being able to identify and handle these duplicates is super important for data analysis.

While Google Sheets doesn’t have a dedicated ‘find duplicates’ button, there are still some easy methods to compare data between columns and highlight those duplicate entries. I’ll walk you through how to compare two columns and spot duplicates using conditional formatting, which is the most effective way to identify duplicates across columns. Then we’ll explore how to highlight duplicate rows, and highlight duplicates in multiple columns. Finally, we will also look at how you can count the duplicates.

Method 1: Using Conditional Formatting to Highlight Duplicates Between Two Columns

Conditional formatting is a handy feature that lets you change a cell’s appearance (like font color or background) based on specific rules. We can use this to highlight duplicate entries between columns, and here’s how you can do that:

1. Open up the Google Sheet you’re working with. Start by selecting the first column you want to compare (let’s say it’s column A). You can select the entire column by clicking on the column letter at the top.
   

2. Next, click on ‘Format’ in the menu, and then select ‘Conditional formatting’.
   

3. A ‘Conditional formatting’ menu will appear on the right side of the sheet. Check that the cell range shown under ‘Apply to range’ is correct. If you want to adjust the range, click the ‘range icon’ and specify the correct one.
   

4. Under ‘Format rules’, click on the dropdown menu and choose ‘Custom formula is’.
   

5. Now, enter a formula in the ‘Value or formula’ field.

   If you have selected the entire column (like column A, represented as A:A), use this formula:

    =COUNTIF($B:$B,$A2)>0
   

   If you’ve selected a specific range in column A (like A2:A30), use this formula instead:

   =COUNTIF($B$2:$B$30, $A2)>0
   

   Remember to replace ‘B’ with the column you’re comparing against. The ‘$’ signs make the range absolute, so the formula works correctly when applied to other cells.
   

   

6. In the ‘Formatting style’ section, you can pick how you want the duplicates to appear. You can use a preset format or choose from options like bold, italic, text color, or fill color.
   

7. Choose a preset style.
   
   or customize the style yourself.
   

8. For this example, we will choose yellow as the fill color for duplicate cells.
   

9. Click ‘Done’ to apply the formatting.
   

This highlights items in column A if they appear in column B. Remember that only the items in column A which have duplicates in column B will be highlighted.

10. Now, repeat this for Column B. Select your range in column B (e.g., B2:B30), go to ‘Format’ and then ‘Conditional formatting’ or just click ‘Add another rule’ in the Conditional format rules pane.
    

11. Confirm the range under the ‘Apply to range’ option
    

12. Set ‘Format cells if…’ to ‘Custom formula is’ and enter the formula:

=COUNTIF($A$2:$A$30, $B2)>0

This formula checks the values in column B against column A to find duplicates.

13. Pick the formatting style (e.g. orange fill).
    

14. Click ‘Done’, and you’ll now see the duplicate items between columns A and B highlighted.
    

Notice, that even though ‘Arcelia’ is repeated in column A, since there’s only one instance in Column B, it’s not highlighted. This only highlights values that have matches between columns.

Method 2: Highlight Duplicate Values in the Same Row

Sometimes you need to highlight rows where the values in two columns are identical. Here’s how to do it with conditional formatting:

1. Select both columns that you want to compare. Then, go to ‘Format’ and select ‘Conditional formatting’.
   

2. Confirm the cell range in ‘Apply to range’ and select ‘Custom formula is’ from the drop-down.
   

3. Enter the following formula:

   =$A2=$B2
   

   This formula will compare each row and highlight the rows with the same value in both columns. Though you only enter the formula for the first row, conditional formatting will apply it to all rows in your selected range.
   

4. Select the formatting style and click ‘Done’.
   

5. Now you’ll see only the rows with matching values in both columns highlighted.
   

Method 3: Highlight Duplicate Cells Across Multiple Columns

If you’re working with a larger sheet, you may need to highlight duplicates across many columns. Conditional formatting can handle that too.

1. Select all the columns and rows you need to check for duplicates. You can select entire columns by holding Ctrl and clicking the column letters at the top. You can also click on the first cell and the last cell while holding Shift key.
   

   

2. Go to ‘Format’ and select ‘Conditional formatting’.
   

   

3. Set the ‘Format rules’ to ‘Custom formula is’, and enter this formula:

 =countif($A$2:$C$30,A2)>1

The $ signs make the column references absolute, so the formula works correctly across columns.

4. Choose how you want the duplicates to be highlighted in the ‘Formatting style’ options and then click ‘Done’.
   

   

5. Now, all the duplicates across the selected columns will be highlighted.
   

   

You can edit or remove the conditional formatting rules anytime. Just select a cell with conditional formatting, go to ‘Format’ and ‘Conditional formatting’. From there you can modify an existing rule or add another one.

Method 4: Count Duplicates Between Two Columns

Sometimes, you need to know how many times a value in one column appears in another. Here’s how you can get those counts:

1. To count how often a value from Column A appears in Column B, enter this formula in cell C2 (or another cell in a new column):

   =COUNTIF($B$2:$B$30,$A2)
   

   This counts the occurrences of the value in A2 within the range B2:B30.
   

2. After typing the formula and pressing Enter, you might see an Auto-fill option. Click the checkmark to apply the formula to the rest of the cells.
   

3. If the autofill prompt doesn’t appear, drag the blue square at the bottom right corner of cell C2 to apply the formula to the rest of the rows in column C.
   

4. Column C will now show how many times each value in Column A appears in Column B.
   

5. To do the reverse (count how many times items in column B appear in column A), enter this formula in cell D2:

    =COUNTIF($A$2:$A$30,$B2)
   

6. Autofill or drag the formula down the column to apply it to other rows.
   

Now column D will show the number of times each value in column B appears in column A.

Note: To count duplicates across multiple columns, adjust the range in the COUNTIF formula. For example, change the range from A2:A30 to A2:B30. This will count the duplicates across the columns A and B.