How to Find Duplicates in Google Sheets
Need to identify duplicate entries in your spreadsheet? This guide shows you how to find duplicates in Google Sheets using conditional formatting, COUNTIF formulas, and built-in tools to highlight, count, or remove duplicate values.
Quick Answer
To find duplicates in Google Sheets: Select your data, go to Format > Conditional formatting, choose Custom formula is, and enter =COUNTIF(A:A,A1)>1. Choose a highlight color and click Done.
To remove duplicates, use Data > Data cleanup > Remove duplicates.
Method 1: Conditional Formatting (Easiest)
Conditional formatting is the quickest way to visually highlight duplicates in your spreadsheet. This method uses a formula that checks each cell and highlights it if the value appears more than once.
Steps to Highlight Duplicates
- Select your data range (e.g., A2:A100 or the entire column A:A).
- Go to Format > Conditional formatting in the menu.
- In the sidebar, click the dropdown under "Format rules" and select Custom formula is.
- Enter the formula:
=COUNTIF(A:A,A1)>1 - Click the fill color icon and choose a highlight color (e.g., yellow or red).
- Click Done to apply.
=COUNTIF(A:A,A1)>1 counts how many times each value appears in column A. If the count is greater than 1, the cell gets highlighted. Adjust the column letter (A) to match your data.Highlight Only Second Occurrence (Skip First)
If you want to keep the first occurrence unmarked and only highlight the duplicates (second, third, etc.), use this formula instead:
=COUNTIF($A$1:A1,A1)>1This counts occurrences only up to the current row, so the first appearance returns FALSE and is not highlighted.
Method 2: COUNTIF Formula (Manual Check)
If you want to add a helper column that shows whether each row is a duplicate, use the COUNTIF function directly in a cell.
Basic Duplicate Check
Add a helper column (e.g., column B) with this formula in B2:
Formula
=COUNTIF(A:A,A2)Returns
Number of times the value appearsDrag the formula down to apply it to all rows. Any cell showing a number greater than 1 indicates a duplicate.
TRUE/FALSE Duplicate Check
For a cleaner TRUE/FALSE result:
Formula
=COUNTIF(A:A,A2)>1Returns
TRUE if duplicate, FALSE if unique| Column A (Data) | Column B (Count) | Column C (Duplicate?) |
|---|---|---|
| Apple | 2 | TRUE |
| Banana | 1 | FALSE |
| Apple | 2 | TRUE |
| Orange | 1 | FALSE |
Method 3: UNIQUE Function (Extract Unique Values)
The UNIQUE function extracts all unique values from a range, effectively removing duplicates. This is useful when you want a clean list without duplicate entries.
Formula
=UNIQUE(A2:A100)Example with multiple columns
=UNIQUE(A2:B100)Steps
- Click on an empty cell where you want the unique list to appear.
- Enter the formula
=UNIQUE(A2:A100)(adjust range as needed). - Press Enter. The unique values will automatically populate downward.
Note: The UNIQUE function creates a dynamic array. Do not put data directly below it, as the list will expand automatically when you add new data.
Remove Duplicates (Built-in Tool)
Google Sheets has a built-in feature to remove duplicate rows entirely. This permanently deletes duplicate entries, keeping only the first occurrence.
Steps to Remove Duplicates
- Select the data range containing duplicates (include headers if applicable).
- Go to Data > Data cleanup > Remove duplicates.
- In the dialog box:
- Check "Data has header row" if your selection includes headers.
- Select which columns to analyze for duplicates.
- Click Remove duplicates.
- A message will confirm how many duplicate rows were removed.
Warning: This action is permanent and modifies your original data. Make a copy of your sheet first if you want to preserve the original data with duplicates.
Find Duplicates Across Multiple Columns
Sometimes you need to find rows where the combination of multiple columns is duplicated. For example, finding rows where both the name AND email are the same.
Using COUNTIFS for Multiple Columns
Use the COUNTIFS function to check duplicates across multiple columns:
Formula (Two Columns)
=COUNTIFS($A:$A,$A1,$B:$B,$B1)>1Formula (Three Columns)
=COUNTIFS($A:$A,$A1,$B:$B,$B1,$C:$C,$C1)>1This formula returns TRUE only when the combination of values in all specified columns appears more than once.
Using Concatenation Method
Another approach is to concatenate columns and then check for duplicates:
Step 1: Create helper column with concatenated values
=A2&"|"&B2Step 2: Use COUNTIF on the helper column
=COUNTIF(D:D,D2)>1Count Duplicates
Need to know how many duplicate values exist in your data? Here are formulas to count duplicates in different ways.
Count How Many Times Each Value Appears
Formula
=COUNTIF(A:A,A2)Count Total Duplicate Cells
To count how many cells contain duplicate values (not unique):
Formula
=SUMPRODUCT((COUNTIF(A2:A100,A2:A100)>1)*1)Count Unique Duplicate Values
To count how many different values appear more than once:
Formula
=SUMPRODUCT((COUNTIF(A2:A100,A2:A100)>1)/COUNTIF(A2:A100,A2:A100))Tips & Best Practices
Exclude header rows
Start your range from row 2 (e.g., A2:A100) to avoid highlighting headers as duplicates of themselves.
Use absolute references for ranges
Use $A$2:$A$100 instead of A2:A100 when you want the range to stay fixed as you copy formulas.
Handle case sensitivity
COUNTIF is case-insensitive by default. "Apple" and "apple" are treated as duplicates. Use EXACT() for case-sensitive matching.
Watch for leading/trailing spaces
Invisible spaces can cause duplicates to be missed. Use TRIM() to clean your data: =COUNTIF(A:A,TRIM(A1))>1
Back up before removing
Always duplicate your sheet (right-click tab > Duplicate) before using the Remove duplicates feature.
Combine with filters
After highlighting duplicates, use Data > Create a filter to show only duplicate rows for review.
Troubleshooting
Conditional Formatting Not Highlighting Duplicates?
- Wrong column reference: Make sure the column letter in your formula matches your selected range (e.g., A:A for column A).
- Wrong cell reference: The cell reference (e.g., A1) should match the first cell in your selected range.
- Formula syntax: Make sure you use
>1not1at the end of the formula.
Duplicates Not Being Detected?
- Hidden spaces: Use
=TRIM()to remove leading/trailing spaces from your data. - Different formats: Numbers stored as text may not match actual numbers. Use
=VALUE()to convert. - Case differences: If case matters, COUNTIF will not distinguish. Use a helper column with
=EXACT().
Remove Duplicates Removed Wrong Rows?
- Wrong columns selected: Make sure you selected the correct columns to check for duplicates.
- Header not excluded: Check the "Data has header row" option if your data includes headers.
- Restore from history: Go to File > Version history > See version history to restore a previous version.
Frequently Asked Questions
How do I find duplicates in Google Sheets?
Use conditional formatting with the formula =COUNTIF(A:A,A1)>1 to highlight duplicates. Select your data range, go to Format > Conditional formatting, choose "Custom formula is", enter the formula, and select a highlight color.
What is the formula for finding duplicates in Google Sheets?
The formula =COUNTIF(A:A,A1)>1 finds duplicates in column A. It counts how many times each value appears and returns TRUE if it appears more than once. You can also use =COUNTIF($A$2:$A$100,A2)>1 for a specific range.
How do I remove duplicates in Google Sheets?
Select your data range, go to Data > Data cleanup > Remove duplicates. Choose which columns to check for duplicates, decide whether to include headers, and click Remove duplicates.
How do I check for duplicates across multiple columns?
Use the COUNTIFS formula: =COUNTIFS($A:$A,$A1,$B:$B,$B1)>1. This checks if the combination of values in columns A and B appears more than once.
How do I count duplicates in Google Sheets?
Use =COUNTIF(A:A,A1) to count how many times each value appears. To count total duplicate entries, use =SUMPRODUCT((COUNTIF(A2:A100,A2:A100)>1)*1).
How do I highlight only the second occurrence of duplicates?
Use =COUNTIF($A$1:A1,A1)>1 in conditional formatting. This counts occurrences up to the current row, so the first occurrence returns FALSE and is not highlighted.
Is COUNTIF case-sensitive?
No, COUNTIF is case-insensitive. "Apple" and "apple" are treated as duplicates. For case-sensitive matching, you need to use a combination of SUMPRODUCT and EXACT functions.
Summary
Finding duplicates in Google Sheets is straightforward with the right approach:
- Conditional formatting with
=COUNTIF(A:A,A1)>1for visual highlighting. - COUNTIF formula in a helper column for manual checking.
- UNIQUE function to extract a clean list without duplicates.
- Data cleanup > Remove duplicates to permanently delete duplicate rows.
For multiple columns, use COUNTIFS or concatenate columns before checking. Always back up your data before removing duplicates, and remember that COUNTIF is case-insensitive by default.
Create Spreadsheet Tutorial Videos
Need to train your team on Google Sheets techniques? Deckoholic makes it easy to record screen tutorials with AI voiceover, zoom effects, and professional polish.
Try Deckoholic Free