Find Duplicates in Google Sheets: A Step-by-Step Guide

How-to basics · Updated November 20, 2026

To find duplicate values 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. This highlights every repeated value without deleting data. You can also use a formula to label duplicates or the built-in cleanup tool to remove them after reviewing the results.

What You’ll Need

  • A Google account and a spreadsheet open in Google Sheets in a web browser.
  • A clear idea of what counts as a duplicate: the same value in one column, or a whole row with matching values across several columns.
  • For removal, make a copy of the sheet or confirm you can undo the change. Removing duplicates changes the data, unlike highlighting them.

The steps below are for Google Sheets, not Microsoft Excel. Both apps can identify duplicates, but their menus and some options differ. In Sheets, you can use the same menus in a desktop browser on Windows or Mac; keyboard shortcuts can vary by operating system.

Step-by-Step

  1. Choose the data to check. Open the sheet and identify the column or columns that define a duplicate. For example, if a contact list has names in column A and email addresses in column B, matching email addresses are often a more reliable indicator than matching names. If you want to compare whole records, decide which columns must all match. Check that headers are in the first row and data starts in row 2; the formulas below assume that layout. If your data starts elsewhere, adjust the row numbers in the formulas.
  2. Highlight repeated values in one column. Select the data cells, excluding the header—for example, A2:A500. Choose Format > Conditional formatting. In the Conditional format rules panel, check that Apply to range shows the range you selected. Open the Format cells if menu and choose Custom formula is. With values in A2:A500, enter =COUNTIF($A$2:$A,A2)>1. Choose a fill color under Formatting style, then click Done. Every nonblank value that appears at least twice in column A will be highlighted, including its first occurrence. The dollar signs keep the checked list fixed while the row reference changes for each cell.
  3. Highlight only the later copies, if that is easier to review. You can instead use a rule that highlights each occurrence after the first. Select A2:A500, open Format > Conditional formatting, and choose Custom formula is. With data starting at A2, enter =COUNTIF($A$2:A2,A2)>1, choose a style, and click Done. The first appearance is left unformatted, while the second and later appearances are highlighted. This is useful when you want to keep the earliest record and inspect only the additional copies. If the selected range starts on a different row, change both row references accordingly.
  4. Ignore empty cells when highlighting. A simple count rule can treat blank cells as matching, which may color empty cells if the formatting range includes them. To exclude blanks, select A2:A500 and use this custom formula instead: =AND(A2<>"",COUNTIF($A$2:$A,A2)>1). This rule checks that the current cell is not empty before counting it. It is particularly helpful when the column has unused rows below the list. Keep the Apply to range aligned with the formula’s starting row; a rule written for A2 should not be applied starting at A1.
  5. Check for duplicates across multiple columns. To find repeated records where columns A, B, and C must all match, select the data rows—for example, A2:C500—and create a custom conditional formatting rule. With those three columns and a first data row of 2, use =AND(COUNTA($A2:$C2)>0,COUNTIFS($A$2:$A,$A2,$B$2:$B,$B2,$C$2:$C,$C2)>1). Apply it to A2:C500. COUNTIFS counts rows with the same value in each of the three columns; the outer check prevents a completely empty row from being treated as a duplicate. Add another column criterion if the record contains more fields that should be part of the match. The formula must refer to the same columns used in your comparison.
  6. Use a helper column to label duplicates. If you want a filterable result instead of colored cells, put a heading such as Duplicate? in an unused column, such as B1. With the values to check in A2:A500, enter =IF(A2="","",IF(COUNTIF($A$2:$A,A2)>1,"Duplicate","Unique")) in B2 and fill it down through B500 using the fill handle. Each nonblank value will be labelled Duplicate if it appears more than once, or Unique otherwise. You can then filter the helper column to show only Duplicate. The formula labels every copy, not just the later ones. For a whole-row check, use a COUNTIFS formula like the one in the previous step as the condition inside IF.
  7. Review the matches before removing anything. Look at the highlighted or labelled rows and compare relevant fields. Two records may share a name but have different email addresses, dates, or account numbers, so matching one field does not always mean the records are interchangeable. If the list has a header, keep it outside the selection when possible. Consider making a copy of the sheet before cleanup, especially if you are working with a large or important list.
  8. Remove duplicates only if you have confirmed the match criteria. Select the data range, including the columns that should determine whether rows are duplicates. Choose Data > Data cleanup > Remove duplicates. In the dialog, indicate whether the data has a header row and select the columns to compare. If you select only the email column, rows with the same email may be removed even if other fields differ; select all relevant columns when a duplicate means the complete row matches. Click Remove duplicates and review the confirmation message to see how many were removed. Sheets generally keeps the first matching row, so sort the data first if you need a particular copy to be retained.

Common Mistakes

Including the heading in the comparison. If the header is inside the formatting range, it can be counted or formatted along with the records. Start at the first data row, or mark the header-row option in the remove-duplicates dialog.

Checking the wrong definition of duplicate. A repeated surname is not necessarily a duplicate person, and the same product name may appear on many valid order lines. Pick a distinctive field or combination of fields that matches your task. For a full record comparison, include every column that must agree.

Removing rows before checking the result. A cleanup action can discard information you meant to keep. Highlight first, inspect the matching rows, and make a copy before using Remove duplicates. If you remove records unintentionally, use Undo promptly, before making unrelated changes.

Misaligning the formula and selected range. Custom conditional-format formulas are evaluated relative to the top-left cell of Apply to range. If the range begins at A2 but the formula is written as though it begins at A1, the check can point to the wrong row. Verify the range and row references together.

Expecting visual formatting to prove values are identical. Different-looking formatting does not necessarily mean different data, and identical-looking text may contain spaces or other invisible characters. Check the underlying cell contents when a result seems surprising.

Tips

Use a temporary, distinctive highlight color so duplicate marks stand out from existing formatting. You can edit or remove a rule later in Format > Conditional formatting by selecting it in the rules panel. Keeping the rule is useful for a list that changes regularly: new entries in the applied range will be checked automatically.

For a changing list, an open-ended range such as A2:A can cover future entries. If the sheet is very large, a bounded range such as A2:A5000 may be easier to manage. Extend the range when needed. If you add a helper formula, fill it down far enough to cover incoming records, or use a suitable expanding formula strategy for your sheet.

Think about extra spaces and inconsistent entry formats. For example, a value with a trailing space may not match the same text without that space. Standardizing the source values before comparing them can prevent false negatives. For names, case differences are usually not treated as separate by COUNTIF, but punctuation, extra spaces, and spelling variations can still affect matches.

When using formulas in a spreadsheet with a locale that expects semicolons between arguments, Sheets may require semicolons instead of the commas shown here. If a valid-looking formula produces a parse error, check the spreadsheet locale under File > Settings and use the argument separator expected by that locale. The function names and comparison logic remain the same.

Troubleshooting

Nothing is highlighted. Confirm that the conditional-format rule is enabled, Apply to range includes the cells with data, and the formula uses the right column and starting row. If you selected A2:A500, the single-column formula should refer to A2 as its relative cell. Check that the values really repeat and that the comparison is not accidentally limited to a different range.

Blank cells are highlighted. Replace the basic COUNTIF rule with the version that begins with AND(A2<>"", …). Also check that your selected range does not extend far beyond the data if you do not need it to.

Different records are marked as duplicates. The rule may be comparing too few columns. If you compare only names, two people with the same name will match. Use COUNTIFS with the fields that together identify a record, and make sure the criteria in the formula refer to the corresponding columns.

Expected matches are missed. Look for leading or trailing spaces, inconsistent punctuation, and values stored in different forms. A number stored as text, for example, may not behave as you expect in every comparison. Clean or standardize a copy of the source data, then run the check again. Avoid changing the original until you have confirmed what each transformation will do.

The formula shows a parse error. Check for a missing parenthesis, a misspelled function, or quotation marks that were copied as typographic curly quotes rather than straight quotes. In some locales, switch commas between function arguments to semicolons. Also confirm that the formula is entered in the Custom formula is box, not the ordinary cell-value condition menu.

Remove duplicates removed more rows than expected. The selected columns may not match your intended definition. Undo immediately if you have not made other changes, then reopen the tool and select the appropriate comparison columns. A backup copy is the safest way to recover if Undo is no longer available.

Frequently Asked Questions

Does conditional formatting delete duplicate values?

No. Conditional formatting only changes how matching cells look; it does not change or remove the data. Use Data > Data cleanup > Remove duplicates when you are sure you want to delete repeated records.

Can I find duplicates in just one column?

Yes. Apply a COUNTIF custom formula to the column’s data range. For values starting in A2, =COUNTIF($A$2:$A,A2)>1 highlights every value in column A that appears more than once. Use the blank-safe version if the range contains empty cells.

Can Google Sheets find duplicate rows?

Yes. Use conditional formatting on the row range with a COUNTIFS rule that checks each relevant column. Alternatively, use Remove duplicates and select the columns that define a repeated row. Comparing only some columns treats rows that match those fields as duplicates even if their other fields differ.

Does Remove duplicates keep the first copy?

Sheets generally retains the first matching row and removes later matches according to the selected columns. If you need a specific record to remain, sort the data so that record comes first before running the cleanup, and keep a backup.

Are Google Sheets and Excel instructions the same?

No. This guide uses Google Sheets menus and formulas. Excel has its own conditional-formatting and duplicate-removal commands, so follow instructions written specifically for your Excel version rather than assuming the menu paths are identical.

Related

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.