How to Locate Duplicates in Google Sheets: A Walkthrough

To locate duplicates in Google Sheets, select the cells you want to check, choose Format > Conditional formatting, and use a custom formula such as =COUNTIF($A$2:$A,A2)>1 for values in column A. You can also use a helper formula to label matches or check whether entire rows repeat. Before starting, decide what counts as a duplicate: the same value in one column, or the same combination of values across several columns.

What You’ll Need

A Google account and a spreadsheet open in Google Sheets in a web browser.
A clear idea of which cells or columns you want to compare. For example, you might check email addresses in column A or compare names and dates across columns A and B.
Optional: permission to edit the spreadsheet if you plan to add formulas, apply formatting, or remove duplicate rows. If you only need to inspect existing data, view access may be enough.

Step-by-Step

Identify the data range and what should match. Look at the headings and decide whether the first row is a header. If names are in A1 and the records begin in A2, exclude the heading by starting your range at A2. Decide whether you want to find every repeated occurrence, including the first one, or highlight only later occurrences. Also decide whether duplicates should be judged by one column or by a complete row.
Highlight duplicates in a single column. Select the values you want to examine, such as A2:A1000. To cover future entries too, you can select A2:A rather than a fixed endpoint. Choose Format > Conditional formatting. In the Conditional format rules panel, check that Apply to range shows the intended cells. Open the Format cells if menu, select Custom formula is, and enter this formula, assuming the values begin in A2: =COUNTIF($A$2:$A,A2)>1. Choose a fill color or text style, then select Done. Each non-unique value in the selected range should be highlighted. The first occurrence is highlighted as well as subsequent occurrences, which makes it easy to see every member of a repeated group.
Highlight only the later occurrences, if that is easier to review. Select the same range and add or edit a conditional formatting rule. Use Custom formula is and enter =COUNTIF($A$2:A2,A2)>1, assuming the first data cell is A2 and the rule applies from A2 downward. The first instance is not highlighted; the second and later instances are. This can be useful when you want to preserve the first record and review only the extra copies. Check that the row number in the formula corresponds to the first row in Apply to range.
Use a helper column to label matches. If you want a visible result you can filter or sort, add a heading such as Duplicate check in an empty column, for example B1. With the values to check in A2:A, enter this formula in B2: =IF(A2="","",IF(COUNTIF($A$2:$A,A2)>1,"Duplicate","")). The formula leaves blank source cells blank and labels a value Duplicate if it appears more than once. Fill it down by dragging the small square at the bottom-right corner of B2, or copy B2 and paste it into the cells below. You can then use the label to filter the rows. This method identifies all copies of a repeated value, including the first one.
Check for repeated rows using more than one column. If a record is defined by both a name and a date, checking only the name can produce false matches. Suppose names are in A and dates are in B, with records starting on row 2. Put a heading in C1 and enter this in C2: =IF(A2="","",IF(COUNTIFS($A$2:$A,A2,$B$2:$B,B2)>1,"Duplicate row","")). Fill the formula down. It labels rows where the combination of the A and B values appears more than once. Add another matching range and criterion inside COUNTIFS if the record depends on more columns. Make sure each compared range covers the same rows.
Review the matches before changing the data. Check a few highlighted cells or labels against the original records. Confirm that the matching values really represent the same thing, and that the check did not include a heading or unrelated column. If you are working with important data, make a copy of the sheet first using the sheet tab menu or File > Make a copy. Finding duplicates does not remove them; it only helps you identify them.
Optionally remove duplicate rows after checking. If you have confirmed that repeated rows should be deleted, select the relevant table, including all columns that belong to each record. Choose Data > Data cleanup > Remove duplicates. In the dialog, indicate whether the data has a header row and choose the columns to compare. If you select only one column, Sheets may remove rows that share that value even when their other details differ. Review the confirmation message and the resulting table. This action changes the data, so keep a copy if you may need to restore removed records.

For Google Sheets in a browser, the paths above use the Sheets menus. Excel has similar ideas but different menu locations: conditional formatting is under Home > Conditional Formatting, and duplicate removal is under Data > Remove Duplicates. Do not look for the Google Sheets Data cleanup menu in Excel.

Common Mistakes

Including a heading in the check. If your range begins at A1, the header is treated like a value. Start at the first data row, or mark the header correctly when using Remove duplicates.
Checking too few columns. Two people may share a name but have different email addresses. If the record is identified by a combination of fields, count duplicates using all those fields rather than one alone.
Removing instead of locating. Conditional formatting and helper formulas are ways to find matches. Remove duplicates deletes rows, so use it only after reviewing the comparison columns and keeping a backup when appropriate.
Applying a formula to a different starting row. A custom formula written for A2 may be wrong if the selected range starts at A3. Align the relative cell reference with the first row of the apply-to range.
Assuming differences in capitalization matter. COUNTIF and COUNTIFS in Google Sheets treat text matches without regard to capitalization. For example, Alex and ALEX count as matches. If capitalization should distinguish records, the standard duplicate check may not fit your rules.
Overlooking extra spaces or inconsistent formatting. A value with a leading or trailing space may not appear identical to the same value without that space. Dates and numbers can also be stored or displayed inconsistently. Clean or standardize the source before deciding that two records differ.

Tips

Use a temporary copy for experiments. Duplicate checks are usually reversible, but deletion is not as easy to undo later. Test a removal on a copied sheet when the data matters.
Choose contrasting formatting. A light fill color is easier to scan than a very dark one, especially in a large table. Avoid relying on color alone if the sheet will be printed or viewed by someone with color-vision differences; add a helper-column label too.
Filter the helper column. After filling down the Duplicate check formula, use Data > Create a filter, then open the filter control in that column and show only Duplicate. This makes matching rows easier to inspect without deleting anything.
Use exact record rules. Write down what makes two entries the same before choosing columns. For a contact list, email might be the key; for orders, it could be order number plus item code. Clear rules prevent valid records from being flagged just because one field matches.
Keep the comparison range consistent. If you add new data beyond a fixed range such as A2:A100, those new rows will not be checked by that rule. Use a range that includes likely additions, and verify the Apply to range setting when the sheet grows.

Troubleshooting

Nothing is highlighted: Open Format > Conditional formatting and inspect Apply to range and the custom formula. If the range begins at A2, the formula should refer to A2; if it begins at A3, update the reference. Confirm that the values you expect to match are actually in the selected column.

Too many cells are highlighted: Check whether blank cells are included or whether a header is being counted. A rule using COUNTIF can treat repeated blanks as matches. If blanks should be ignored, use a custom formula such as =AND(A2<>"",COUNTIF($A$2:$A,A2)>1), assuming the applied range starts at A2. Also verify that you did not select entire rows or extra columns by mistake.

Some apparent duplicates are not detected: Look for leading or trailing spaces, different punctuation, or values stored in different formats. For text with accidental spaces, create a cleaned helper column; assuming the original value is in A2, =TRIM(A2) removes extra spaces at the beginning and end and reduces repeated spaces between words. Compare the cleaned values rather than editing the original data immediately. For dates, confirm that both cells contain dates, not one date and one text string that looks like a date.

The formula shows an error or does not fill down: Confirm that you entered it in the first data row and copied it into the rows below. In the formula bar, check for missing parentheses or quotation marks. Google Sheets usually uses commas between formula arguments in common English-language settings, but spreadsheet locale settings can affect separators and number formats. If your locale expects semicolons, adjust the separators accordingly.

Remove duplicates deleted more than expected: Use Undo immediately if the removal was just performed, or restore the previous version through File > Version history > See version history if available to you. For another attempt, copy the sheet first and select all columns that define a full record in the Remove duplicates dialog. Selecting just a name or category can make distinct records look identical to the cleanup tool.

Frequently Asked Questions

Does Google Sheets have a built-in way to find duplicates?
Yes. Conditional formatting can highlight repeated values, and Data > Data cleanup > Remove duplicates can remove repeated rows. A helper formula is useful when you want a filterable label rather than color alone.

Can I find duplicates without deleting them?
Yes. Use conditional formatting or a helper column with COUNTIF or COUNTIFS. Neither method removes records. Review the matches before using the separate Remove duplicates command.

How do I find duplicates across two columns?
Use COUNTIFS to compare both fields together. For example, with names in A and dates in B beginning on row 2, the formula in the guide labels a row only when that name-and-date combination appears more than once.

Does the check distinguish uppercase and lowercase?
No. COUNTIF and COUNTIFS are case-insensitive for text, so capitalization alone does not make two entries different. If case is meaningful, use a case-sensitive comparison approach rather than relying on these standard checks.

Why are blank cells marked as duplicates?
A duplicate-count rule can count repeated blank cells in its range. Exclude blanks with an AND condition in the custom formula, or use a helper formula that returns an empty result when the source cell is blank.

Can I check duplicates in a whole table?
Yes. Define which columns together identify a record, then compare that combination with COUNTIFS or select the relevant columns in Remove duplicates. Avoid comparing only one field if different records can legitimately share its value.

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.