Google Sheets Duplicates: How to Find Repeated Values

To look up duplicates in Google Sheets, add a helper column and use =IF(A2="","",IF(COUNTIF($A$2:$A$1000,A2)>1,"Duplicate","Unique")); fill it down to label repeated values. You can also highlight duplicates with conditional formatting or check whether values from one list appear more than once in another. Start with a copy of important data, and make sure you know whether a duplicate means the same value in one column or an identical whole row.

What You’ll Need

A Google Sheets spreadsheet open in a web browser, with the values you want to check.
A free column beside your data for results, if you want to label duplicates with a formula.
A clear rule for what counts as a duplicate: a repeated entry in one column, a repeated record across several columns, or a match between two lists.
For a large or important sheet, a copy of the original data before sorting, cleaning, or deleting rows.

Step-by-Step

Identify the data range and duplicate rule. Open the sheet in Google Sheets and find the column or columns to check. For example, checking customer email addresses in column A finds repeated addresses; it does not tell you whether the customers’ other details match. If a duplicate should mean that the name, email, and region are all the same, check all three columns instead. Note the header row and begin with the first data row below it.
Add a helper column for one-column duplicates. If your data has a header in A1 and values in A2:A1000, type a heading such as “Status” in B1. With those values in A2:A1000, enter this in B2: =IF(A2="","",IF(COUNTIF($A$2:$A$1000,A2)>1,"Duplicate","Unique")). For example, if Alpha occurs twice in column A, the result beside either Alpha is “Duplicate”; if Gamma occurs once, its result is “Unique.” The formula deliberately leaves empty cells blank. Adjust the 1000 limit if your list is longer.
Fill the formula down the helper column. Select B2 and drag the small square at the bottom-right corner of the selected cell down beside your data. You can also copy B2, select the cells below it in column B, and paste. Check a few results near the bottom: the row reference should change as the formula is filled, while the range to search should remain fixed. If the sheet is regularly updated, leave room in the search range or raise its ending row so new values are included.
Choose whether the first occurrence should count as a duplicate. The previous formula labels every instance of a repeated value, including its first appearance. If you want to keep the first one marked separately and label only later occurrences, use a different helper formula. With the same values in A2:A1000 and the first result in B2, enter =IF(A2="","",IF(COUNTIF($A$2:A2,A2)>1,"Duplicate","First occurrence")). If Alpha appears in A2 and A5, B2 says “First occurrence” and B5 says “Duplicate.” Fill this version down; its search range grows one row at a time.
Look up values from a second list. This is useful when you have a list of entries to check against a master list. Keep the master values in A2:A1000, put the values to investigate in D2:D1000, and add a heading such as “Lookup result” in E1. With a search value in D2, enter this in E2: =IF(D2="","",IF(COUNTIF($A$2:$A$1000,D2)>1,"Duplicate in source",IF(COUNTIF($A$2:$A$1000,D2)=1,"Found once","Not found"))). If D2 contains Alpha and Alpha occurs twice in the master list, E2 returns “Duplicate in source.” A value appearing once returns “Found once,” while a value absent from the master list returns “Not found.” Fill the formula down beside the lookup list.
Check for identical records across multiple columns. If a duplicate means the entire record matches, compare the relevant fields rather than checking just one column. For records in A:C, with headers in row 1, place a result heading in D1. With the first record in row 2, enter in D2: =IF(COUNTA(A2:C2)=0,"",IF(COUNTIFS($A$2:A2,A2,$B$2:B2,B2,$C$2:C2,C2)>1,"Duplicate row","First occurrence")). If the same three values appear in rows 2 and 5, row 2 is “First occurrence” and row 5 is “Duplicate row.” Fill down. To compare more fields, add a matching range and current-row value pair inside COUNTIFS for each additional column.
Highlight repeated values without adding labels. Select the data cells to format, such as A2:A1000, and choose Format > Conditional formatting. In the side panel, under “Format rules,” select “Custom formula is.” With values in A2:A1000 and A2 as the first selected cell, enter =COUNTIF($A$2:$A$1000,$A2)>1. For example, if Alpha appears twice, both Alpha cells receive the chosen formatting. Pick a fill color or text style and select “Done.” The dollar signs keep the search range fixed while the row reference adjusts for each cell.
Review the matches before taking action. A label or highlight helps find repeated entries; it does not confirm that they are safe to remove. Check the matching rows and any related columns. If you want to examine only labelled records, use Data > Create a filter, then use the filter control in the helper-column header to show “Duplicate” or “Duplicate row.” When finished, turn off the filter or use the filter control to show all records again.

Common Mistakes

Including the header in the range. If A1 contains “Email,” start the data range at A2. Otherwise the header is treated as a value and can affect results.
Checking the wrong definition of a duplicate. A repeated email address is not necessarily an identical customer record. Choose the fields that define a match before applying a formula.
Using the all-instances formula when only later repeats should be flagged. COUNTIF over the complete list marks the first occurrence too. Use the expanding-range version if the first instance should be distinguished.
Ignoring spaces or inconsistent typing. “Sam Lee” and “Sam Lee ” can look identical while containing different spaces. Extra spaces can affect matching; check and clean the data if results seem incomplete.
Deleting rows based only on color or a label. A duplicate indicator identifies a match according to the chosen rule, but two records with one shared field may still contain different useful information. Review the full record first.

Tips

Use a helper column when you need an auditable result. It makes it easier to sort or filter matches and to see why a row was identified. Keep the original data unchanged until you have reviewed the results.
Make the range large enough, but not misleading. A formula ending at row 1000 will not check new data entered in row 1001. Update the range when the list grows, or choose a sensible limit with space for expected additions.
Sort carefully. Sorting can make repeated entries easier to inspect, but select the entire table before sorting so names, emails, and other fields stay together. A helper formula that marks later occurrences depends on row order, so sorting may change which row is called the first occurrence.
Use formatting for a quick visual scan. Conditional formatting updates as values change and avoids a separate label column. A helper column is usually better when you need to filter, export, or document the results.
Remember that Google Sheets and Excel have different menus. These instructions use Google Sheets in a browser. Excel also has duplicate-highlighting tools, but its ribbon paths differ; do not look for Google Sheets’ Format menu in Excel.

Troubleshooting

The formula reports a value as unique even though it looks repeated. Check for leading or trailing spaces, different punctuation, or a range that stops before the other occurrence. You can test a cleaned version in a spare column. With the original text in A2, enter =TRIM(A2) in B2; if A2 contains “ Alpha ”, B2 returns “Alpha.” Fill down, then check the cleaned column for duplicates. Keep the original values until you have verified the cleanup.

The results show every entry as unique. Confirm that the formula points to the actual data column and that the search range includes all relevant rows. If the formula was copied to another column, check that the reference to the values changed as intended while the fixed range still points to the full list.

Blank rows show a duplicate label. Use the blank-checking IF at the start of the formulas above. Also check whether cells that look empty contain spaces or formulas returning an empty string; those may not behave like truly empty cells. Remove unwanted spaces or adjust the source data before relying on the result.

Conditional formatting highlights the wrong cells. Verify that “Apply to range” starts on the same row referenced in the custom formula. For a range beginning at A2, use the row reference $A2, not $A1. Confirm that the range to count is the intended column and that the rule uses “Custom formula is.”

Sheets rejects the formula. Check that quotation marks and parentheses are balanced and that the formula begins with an equals sign. Some spreadsheet locales use semicolons instead of commas as separators; if Sheets indicates a parse error, use the separator required by your locale. The formulas here are written with comma separators.

Frequently Asked Questions

Does COUNTIF find every occurrence or just the second one?
The formula with the complete search range labels every instance of a repeated value, including the first. The expanding-range formula labels the first occurrence separately and marks only later repeats.

Can I find duplicate rows instead of duplicate cells?
Yes. Use COUNTIFS with one pair of criteria for each column that defines a full-record match. The example above compares A, B, and C; add another pair for every additional field that must match.

Can Google Sheets remove duplicates automatically?
Yes. Select the relevant data and choose Data > Data cleanup > Remove duplicates. Follow the dialog to select columns and whether the data has a header. This changes the data, so make a copy first and review which columns define a duplicate before confirming.

Are duplicate checks case-sensitive?
COUNTIF matching in Google Sheets is generally not case-sensitive, so entries such as “alex@example.com” and “Alex@example.com” are treated as matches. If letter case is meaningful for your data, use a case-sensitive method rather than relying on the COUNTIF examples.

Why do some values with asterisks or question marks match unexpectedly?
COUNTIF treats an asterisk and a question mark as wildcard characters in criteria. If your actual data includes those symbols, a value containing one may match more broadly than intended. Clean or escape wildcard characters before using the result to make decisions.

Can I remove the helper column after checking?
Yes, once you have reviewed the results. If you need a permanent record, copy the helper-column results and use Edit > Paste special > Values only in a separate location before removing the formula column.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.