How Do I Find Duplicates in Two Columns in Google Sheets?

When it comes to analyzing data on Google Sheets, sometimes is important to see where there are duplicates in two columns.

This is especially helpful if you've synced two Google Sheets into one master sheet or perhaps you have set up colored dropdown lists in your sheet and want to see duplicates.

Another great use for finding duplicates in two columns is if you are using Google Sheets to pull in historical stock data. You can easily analyze the data using this method.

So how do I find duplicates in two columns in Google Sheets? Thankfully Google has made it very simple with conditional formatting.

Highlight the columns or rows that you want to locate duplicates in.
Right click on the area highlighted and click on “Conditional Formatting”.
In the dropdown under “Format cells if…” choose “Custom formula is”.
Paste this formula into the box: =COUNTIF($A$2:G,Indirect(Address(Row(),Column(),)))>1
Click green “Done” button.

Visual Guide to Finding Duplicates in Two Columns in Google Sheets

Step 1:

Highlight the columns or rows that you want to find duplicates in. You do this by clicking and dragging over the column letters or row numbers (i.e. I highlighted columns A and B).

Step 2:

Right-click anywhere in the blue highlighted area and a box will pop up. Scroll down to the bottom of that box and click “Conditional formatting“.

You can also click on “Format” in the top menu and then click on “Conditional formatting” at the bottom of the drop down.

Another way is to simply use the keyboard shortcut: CTRL+\

Step 3:

Since you already highlighted the cells you can leave the “Apply to range” section the same.

In the “Format cells if…” section, choose “Custom formula is” in the dropdown box.

Step 4:

In the box under the “Custom formula is” dropdown, past this formula:

=COUNTIF($A$2:G,Indirect(Address(Row(),Column(),)))>1

You can also choose to change the “Formatting style” box you can change the way the duplicate cells look when they are highlighted.

Step 5:

You should now see your duplicate items highlighted on your spreadsheet.

Click the green “Done” button to finish.

You’ll still need to sort through the cells and find the exact duplicates.

If you’re looking for an exact item on your list, you can use the dropdown under “Format cells if…” to find specific duplicates.

For example, I wanted to see where “blue” was duplicated in my two columns for I chose “Text is exactly” in the dropdown and then typed in the word “blue” in the box.