How to Find Duplicates in Google Sheets: 5 Easy Methods 2026

You have exported 2000 rows of CRM data, then your work mate has added in another 800 from another system and now you have 2 big excel spreadsheets with hidden duplicates smack in the middle ruining your dashboards.

A.1. How to locate Remove copies in Google Sheets? In Google Sheets, your search queries will be displayed in a second itself. Here we have listed 5 proven effective methods, which include simple to use formula and inbuilt cleanup tool to locate duplicates.

$A:$A, A1

Method 1: COUNTIF Formula Find Duplicates in One Column

This is the fastest way to find  duplicates in the same column. COUNTIF counts the number of times a value can be counted exactly. If it is more than 1 it’s a duplicate.

Step 1: Pick an empty cell to the right of where the data is (if the data is in column A, select cell B1).

Step 2: Type this formula: =COUNTIF()

Step 3 : Hit Enter. The number tells you how many times the A1 value occurs. 1=Unique 2 or more=Duplicate

Step 4: Drag the formula down from column B for every row.

Sort on column B now, so that you can “sort on drag to filter here!”, then “sort A-Z” so that all duplicates will immediately be brought up to the top.

Cleaner method: look for duplicate record so you know what to name each cluster with the IF statement: =IF(COUNTIF($A:$A, A1)>1,“Duplicate”,“Unique”) A different output with a label instead of a number.

Method 2: Conditional Formatting  See Duplicates in Color

Conditional Formatting is a bit more refined but no more human. Conditional Formatting is the solution if you want the duplicate numbers to be visually noticeable.

Step 1: Choose the Column/Range which you would like to check (e.g. A1:A1000).

Step 2 : Click Format->Conditional formatting on the top menu.

Step3: From the drop down list of “Format cell if”,select“Custom formula is”.

Step 4:  Enter: =COUNTIF($A:$A, A1)>1

Step 5: Select the spot color (red or orange work in most instances) and press Done.

Likewise, all copies of a cell will change to your colour ‘you’. You do not need any reenabling rule as the highlighting will be updating on the fly as you add and delete data.

Method 3: COUNTIFS  Find Duplicates Across Multiple Columns

One column can‘t really tell you if you have a true duplicate. For example, if you want to know if John appears twice in column A. Well, he does but that is not a duplicate, that‘s the same name. So, same name and same email? Now we‘re talking!

COUNTIFS is used to test several conditions.

Step 1: Select an empty cell (e.g., C1)

Step 2: Type: =COUNTIFS($A:$A, A1, $B:$B, B1)

Step 3: Press Enter, then copy Formula down column C.

Duplication! Any column that has 2 or more is a duplicate. i.e. the same value in Column A AND column B occurs more than once down your sheet..

To check three columns, simply extend the formula: =COUNTIFS($A:$A, A1, $B:$B, B1, $C:$C, C1)

Method 4: UNIQUE Function Extract a Clean List Without Duplicates

The UNIQUE function doesn‘t display the duplicate values in your sheet, it assigns those duplicates to another clean list with all the different values.

Step 1: Select a blank cell in a blank area of your sheet.

Step 2: Type: =UNIQUE(A1:A500)

Step 3: Enter. Below the right hand side is generated by every value in Column A that has not occurred before, each in their own row. The Google Sheets: Any amendments to the source data will be automatically updated in the Google Sheets Graph:

Want to list only the values that have duplicates? Use this formula: =UNIQUE(FILTER(A2:A, COUNTIF(A2:A, A2:A)>1))

This will cause the output to duplicate values, so will delete duplicate objects in the output which will give you a list of anything with a duplicate.

Method 5: Built-in Remove Duplicates Tool No Formula Needed

And if all you want to do is find and delete duplicates without writing a formula then Google sheets has a built-in tool for this as well.

Step 1: click anywhere in a range of your data.

Step 2: Choose Data -> Data cleanup -> Remove duplicates.

Step 3:  Should have opened up a dialog box. Please check the box called Read the first row as a label if your first row is labeled “Name” or “Email”.

Step 4: Decide columns to check. When you select any one Column, then it will delete the duplicate values on that column. When you select 2 columns, then it will delete the rows where the selected columns have the same values.

Step 5: Under Remove duplicates, select Remove duplicates. The pop-up window will tell you how many duplicate rows have been found and removed.

Very important warning: it deletes whole rows not cells. So be very careful before you want to use this. Use File->Make a copy to make a copy of your file before tinkering on the copy. Undo does not always work for big deletions.

Common Mistakes When Finding Duplicates

Extra room within the missing cells. John smith and John smith the space at the end doesn‘t bother you to google sheets. To them, they are different. Always clean your data with =TRIM() when you are doing any duplicate check.

Wrong start row. If you have your labels in the first row then you want to start your formula in A2 and not A1, otherwise those labels will be included as well.

Utilising formulas over excessive rows. If you use a COUNTIF to relate to 50,000 rows of data and the data is only in row 300 this will create a very slow response time. keep your range to your data.

Removal without preview. The Remove Duplicates command is on, no warning. First, use Conditional Formatting to temporarily highlight. Then, remove duplicates knowing it‘s safe.

Which Method Should You Use?

Situation	Best Method
Quick check, single column	COUNTIF helper column
Want visual color highlights	Conditional Formatting
Duplicates span multiple columns	COUNTIFS
Need a clean list of unique values	UNIQUE function
Delete duplicates without any formula	Remove Duplicates tool

FAQs

Q: looking for duplicates in a google spreadsheet without formulas. 

Use the coming feature of google spreadsheets: Data -> Data cleanup -> Remove duplicates. It‘s no formula but ‘deletes’ the duplicate line.

Q. Can google sheets find a dupe across 2 columns? 

A. Helper column=COUNTIFS($A:$A, A1, $B:$B, B1) count only if they are the same value in both columns!

Q: How do I know if both 2 sheets have the same value? 

=COUNTIF(Sheet2!$A:$A,A1) gives me the number of hours that value in A1 is found in sheet2. If the number is higher than 0 then the value is also in sheet2.

Q: How to quickly remove all duplicates in Google sheets?

 select Data-> Data cleanup-> Remove duplicates. The fastest takes about 10 seconds but always a copy of your file.

Final Thoughts

Identifying duplicates in Google Sheets requires a high level of skill. COUNTIF compares just one column in seconds. COUNTIFS compares multiple columns in seconds. The UNIQUE formula shows a way to view data as is. Remove Duplicates capabilities handles removal without a formula.

Golden rule: Never delete without checking! Unlike deletion rows, highlighting can be undone. Protect your sheet where it is an emergency by choosing the best preposition in your case you may have a clean and uniform data all the time.