Find Duplicates in Google Sheets: Rows & Cells

This guide shows you how to find and highlight duplicate values in Google Sheets, both within a single column and across entire rows. You’ll learn several methods—from simple conditional formatting to formulas—so you can choose the one that fits your data. No prior experience with spreadsheets is needed, just a Google account and your data ready in a sheet.

What You’ll Need

A Google Sheets document with your data.
Basic familiarity with selecting cells and typing in the formula bar (covered below).
For the formula methods, a basic understanding of how to copy formulas down a column.

Step-by-Step

Method 1: Highlight Duplicates in a Single Column (Conditional Formatting)

Open your Google Sheets file and select the column you want to check for duplicates. For example, click on the letter A to select the entire column, or click and drag to select a specific range like A2:A100.
Click the menu item Format and then Conditional formatting.
In the panel that opens on the right, under Apply to range, confirm the range you selected is shown (e.g., A2:A100).
Under Format rules, click the dropdown that says Format cells if… and choose Custom formula is.
In the formula box, enter: =COUNTIF(A:A, A2) > 1 (assuming your data starts in row 2; adjust the cell reference to match your first data row).
Set the formatting style you want (e.g., a yellow fill color) by clicking the paint roller icon and selecting a color.
Click Done. All cells in the selected range that have a duplicate elsewhere in column A will now be highlighted.

Method 2: Find Duplicate Rows (Entire Row Matches)

This method highlights entire rows that are exact duplicates of another row.

Select the range of cells that includes all the columns you want to check. For example, if your data spans columns A to C, select A2:C100 (adjust to your actual data range).
Go to Format > Conditional formatting.
In the Apply to range box, make sure your selected range is shown.
Choose Custom formula is from the dropdown.
Enter the formula: =COUNTIF(ARRAYFORMULA($A$2:$A$100 & "|" & $B$2:$B$100 & "|" & $C$2:$C$100), $A2 & "|" & $B2 & "|" & $C2) > 1
Replace the ranges (A2:A100, B2:B100, C2:C100) with your actual data range, and adjust the column letters to match your data. The “|” is just a separator to avoid false matches (e.g., “12” and “3” vs “1” and “23”).
Set a fill color and click Done. Any row that appears more than once will be highlighted entirely.

Method 3: List Duplicate Values with a Formula (No Formatting)

If you want a separate list of the duplicate values, use the UNIQUE and FILTER functions.

In a new column (e.g., column E), type the header Duplicates.
In cell E2, enter the formula: =UNIQUE(FILTER(A2:A100, COUNTIF(A2:A100, A2:A100) > 1))
This will list each value from column A that appears more than once. Adjust the range A2:A100 to match your data.
Press Enter. The list of duplicates will appear, spilling down as needed.

Method 4: Use the Built-in Remove Duplicates Tool (Quick Check)

Select the range you want to check for duplicates.
Go to Data > Data cleanup > Remove duplicates.
A dialog will appear asking if your data has a header row. Check or uncheck the box accordingly.
Click Remove duplicates. Google Sheets will tell you how many duplicate rows were removed. This method permanently deletes duplicates, so use it only if you don’t need to keep them.

Common Mistakes

Incorrect range references in formulas: Using absolute references (with $) incorrectly can cause the formula to check the wrong cells. In conditional formatting, the formula should use relative references for the first cell (like A2) and absolute references for the whole column (like $A$2:$A$100) when needed.
Not adjusting the formula for the first row: If your data starts in row 2, the formula must reference A2, not A1. Using A1 will offset the check and may highlight the wrong cells.
Forgetting about blank cells: COUNTIF will treat blank cells as duplicates. If you have blank cells in your range, they will be highlighted. To avoid this, use a formula like =AND(A2<>"", COUNTIF(A:A, A2) > 1).
Mixing up “duplicates” with “unique” in Remove Duplicates: The Remove Duplicates tool deletes entire rows that are identical, not just individual cells. If you only want to find duplicates in one column, use conditional formatting instead.

Tips

Use conditional formatting for a visual overview: It’s the fastest way to see duplicates in context. You can even add multiple rules to highlight duplicates in different columns with different colors.
Check for duplicates across multiple columns: The row-duplicate method (Method 2) works well, but you can also use the formula =COUNTIFS($A$2:$A$100, $A2, $B$2:$B$100, $B2) > 1 to highlight rows where both column A and column B match.
Use the UNIQUE function to see all unique values: If you want to see what values are NOT duplicates, use =UNIQUE(A:A) in a new column. It will list all distinct values.
Freeze your header row: If you have headers, go to View > Freeze > 1 row so you can scroll without losing track of your columns.

Troubleshooting

Conditional formatting isn’t highlighting anything: Double-check your formula. Make sure the cell reference in the formula matches the first cell of your selected range. Also, ensure you have selected the correct range in “Apply to range”.
It highlights blank cells: Add AND(A2<>"", ...) to your formula as mentioned above.
Formula returns an error like “#VALUE!”: This often happens if your range includes text and numbers, and the formula is trying to concatenate them incorrectly. For the row-duplicate method, ensure all columns are text or convert them to text using TEXT(A2, "0") if needed.
The UNIQUE/FILTER formula only shows the first duplicate: That’s correct—UNIQUE removes duplicates, so it lists each value only once. If you want to see every occurrence, use conditional formatting instead.
Remove Duplicates says “No duplicates found” but you see them: This can happen if there are extra spaces or different data types (e.g., one cell is text “123” and another is number 123). Use the TRIM function to clean data first: =TRIM(A2) in a new column, then copy and paste as values.

Frequently Asked Questions

How do I find duplicates in two columns simultaneously?

Use the COUNTIFS function. For example, to highlight rows where both column A and column B have the same value, use: =COUNTIFS($A$2:$A$100, $A2, $B$2:$B$100, $B2) > 1 in conditional formatting.

Can I remove duplicates but keep the first occurrence?

Yes. The built-in Remove Duplicates tool keeps the first occurrence of each row and deletes the rest. Just make sure your data is sorted the way you want before using it.

Why is my conditional formatting highlighting the header row?

Your formula likely references a cell that includes the header. If your data starts in row 2, your formula should use A2, not A1. Also, check that your “Apply to range” does not include the header row.

How do I highlight duplicates based on a partial match (e.g., same name in a full name)?

Use wildcards in COUNTIF. For example, to find cells in column A that contain the word “John”, use: =COUNTIF(A:A, "*John*") > 1. This will highlight all cells with “John” anywhere in the text.

Can I find duplicates in a specific column but ignore case?

Yes. Use the LOWER function to make the comparison case-insensitive. For example: =COUNTIF(ARRAYFORMULA(LOWER(A:A)), LOWER(A2)) > 1.

What does the “|” symbol do in the row-duplicate formula?

It’s a separator that combines the values from multiple columns into one text string. This prevents false matches like “1” and “23” being considered the same as “12” and “3”. Any character that doesn’t appear in your data works.

General steps for current mainstream versions; menus can differ by device and version. See the disclaimer or report an error.

More in this bed

The whole Tech How-To bed →