Updated: May 13
When it comes to analyzing data on Google Sheets, sometimes is important to see where there are duplicates in two columns.
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
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).
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+\
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.
In the box under the “Custom formula is” dropdown, past this formula:
You can also choose to change the “Formatting style” box you can change the way the duplicate cells look when they are highlighted.
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.
How to Find Duplicate Cells Across Multiple Columns in Google Sheets
The same process above works for finding duplicates in multiple rows and columns as well.
All you need to do is highlight every row that you want to check and go through the same formula process.