Google Sheets Duplicate Formula: Find or Flag Repeats

To find duplicate values in Google Sheets, use =COUNTIF($A$2:$A$100,A2)>1 in a helper column, assuming the values are in A2:A100; it returns TRUE for every value that appears more than once. To flag only the second and later occurrences, use =COUNTIF($A$2:A2,A2)>1 and fill it down.

These formulas identify repeats without deleting anything, so you can review the results before changing your data. The right approach depends on whether you want to count repeats, highlight them, find duplicate records across several columns, or remove extra rows altogether.

Count matching values with COUNTIF

COUNTIF checks a range for cells that meet one condition. The first formula above counts how often the value in A2 occurs anywhere in A2:A100, then compares that count with 1. If it appears at least twice, the comparison returns TRUE; otherwise, it returns FALSE. Replace the range and cell reference to match your sheet, and use dollar signs to keep the search range fixed when copying the formula.

For a count rather than a TRUE or FALSE result, enter this version. With product names in A2:A7, put the formula beside A2 and copy it down; if Maple appears twice, each Maple row returns 2, while a name appearing once returns 1.

With names in A2:A7 and the formula in B2, enter =COUNTIF($A$2:$A$7,A2) and fill down; for the example values Maple, Cedar, Maple, Birch, Cedar, the results are 2, 2, 2, 1, and 2 in the nonblank rows.

The dollar signs make the start and end of the lookup range stay put as the formula is copied. The A2 reference has no dollar sign, so it changes to A3, A4, and so on. For a growing list, you can use a whole-column range, but a bounded range can be easier to inspect and may avoid unnecessary calculation on very large sheets.

Flag only later occurrences

Sometimes the first copy is legitimate and only the repeats after it need attention. A running COUNTIF range answers that question: its starting cell stays fixed, but its ending cell moves as you fill down. The count is therefore based on the current row and the rows above it, not on later entries.

With values in A2:A7, put this formula in B2 and fill down. For Maple, Cedar, Maple, Birch, Cedar, the results are FALSE, FALSE, TRUE, FALSE, and TRUE; the first appearance of each name is not flagged.

With values in A2:A7 and the formula in B2, enter =COUNTIF($A$2:A2,A2)>1 and fill down; a value is TRUE only when it has already appeared earlier in the list.

To keep empty rows from being treated as repeats, wrap the check in IF. With values in A2:A100 and the formula in B2, enter =IF(A2="","",COUNTIF($A$2:A2,A2)>1) and fill down; blank input cells produce blank results, and later repeated values produce TRUE. This is useful in a working list where new records may be added below the current data.

Highlight duplicates without a helper column

Conditional formatting can color duplicates directly in the data instead of displaying a separate TRUE/FALSE result. In Google Sheets in a browser, select the cells to check, choose Format > Conditional formatting, and set the rule to Custom formula is. Pick a fill color, then select Done. The formula should be written for the first cell in the selected range, and the range in the rule should be adjusted to fit your data.

For A2:A100, set the apply-to range to A2:A100 and use this custom formula; every value that appears more than once will be highlighted, including its first occurrence.

With values in A2:A100 and A2 as the first selected cell, enter =COUNTIF($A$2:$A$100,A2)>1 as the custom formula; the rule highlights all matching duplicates.

To highlight only later occurrences instead, use a running range. With A2:A100 selected and A2 as the first cell, use =COUNTIF($A$2:A2,A2)>1; the first instance remains uncolored and subsequent instances are highlighted. If the rule appears to mark the wrong rows, check that the apply-to range begins on the same row referenced by the formula.

Check duplicate records across multiple columns

A repeated item is not always a duplicate record. For example, a customer may place several orders, so a repeated customer name alone should not identify an entire row as a duplicate. Instead, compare the fields that together define a record, such as customer ID, order date, and order number.

With IDs in column A, dates in B, and order numbers in C, compare all three fields. Put the formula in D2 and copy it down through the records; it returns TRUE when that combination appears on more than one row. Change the columns to match the fields that should identify a unique record.

With record fields in A2:C100 and the formula in D2, enter =COUNTIFS($A$2:$A$100,A2,$B$2:$B$100,B2,$C$2:$C$100,C2)>1 and fill down; rows with the same ID, date, and order number are marked TRUE.

COUNTIFS requires all listed conditions to match on the same row. Decide carefully which fields define a duplicate: adding a timestamp or a note column may cause otherwise identical records to count as different, while comparing too few fields can flag valid separate records. You can also use conditional formatting with a COUNTIFS custom formula to color matching records.

List the repeated values once

If you want a compact list of which values repeat, rather than a flag beside every source row, combine UNIQUE, FILTER, and COUNTIF. This is useful for reviewing a long column or building a short list for follow-up. In Sheets, the result expands into cells below the formula, so keep that output area clear.

With names in A2:A100, enter this formula in an empty cell; it returns each nonblank name that occurs more than once, once in the output list. If Maple and Cedar repeat, the output contains Maple and Cedar rather than one entry per matching row.

With names in A2:A100 and an empty output area, enter =UNIQUE(FILTER(A2:A100,A2:A100<>"",COUNTIF(A2:A100,A2:A100)>1)); the returned list contains distinct nonblank values that occur more than once.

If the formula reports an array expansion error, check whether the cells below or beside its result contain existing data. Also confirm that the source range actually contains values to return. For a simpler workflow, use a helper column with COUNTIF and filter that column for TRUE; this is easier to audit when you need to see the original row for each match.

Remove duplicates using the Sheets menu

A formula identifies duplicates but does not remove rows. If you have reviewed the data and want to delete extra copies, make a copy of the sheet first or use Undo immediately if the result is not what you intended. Then select the data range and choose Data > Data cleanup > Remove duplicates. In the dialog, indicate whether the range has a header row and choose which columns Sheets should use to decide whether rows are duplicates. Select Remove duplicates to proceed.

The choice of columns matters. Selecting only an email column removes rows that repeat that email, even if other cells differ; selecting every column removes only rows whose selected field values match as a complete record. A formula is safer for investigation because it leaves the original rows in place, while the cleanup command is convenient after you have confirmed the criteria.

Keep a backup when the source data is important. Removing rows can affect references, summaries, or downstream processes, and a formula-based review gives you a chance to resolve differences such as conflicting addresses or dates before deleting anything.

Common problems and what to check

A duplicate formula can be correct and still produce results that surprise you. COUNTIF is not case-sensitive, so Maple and MAPLE are treated as matching values. Extra spaces can have the opposite effect: Maple and Maple followed by a space may not match as expected. If the source comes from pasted or imported data, inspect the cells and consider cleaning inconsistent spacing before deciding that two entries are genuinely different.

Unexpected TRUE values: Check whether the same value occurs elsewhere in the lookup range, including rows outside the visible section.
Blank rows marked as duplicates: Use an IF check to return a blank when the current input cell is empty.
Only some parts of a record should match: Use COUNTIFS with the identifying columns rather than counting one field alone.
Formula copied incorrectly: Confirm the lookup range is absolute, such as $A$2:$A$100, while the current-row reference changes as intended.
Formula parse error: Google Sheets locales may use semicolons instead of commas between function arguments. If commas are rejected, check the spreadsheet’s locale settings and use the separator expected by that locale.

For exact, case-sensitive comparisons, COUNTIF is not the right test by itself. You can use a more specialized formula, but first decide whether capitalization truly distinguishes records; for names and email addresses, a case-insensitive check is often the useful behavior. Avoid changing data automatically until you have confirmed the rules that define a duplicate in your particular sheet.

Google Sheets and Excel differences

The COUNTIF and COUNTIFS examples here also work in current Excel, but the menu instructions are specifically for Google Sheets in a web browser. Excel has its own Remove Duplicates command and conditional-formatting menus, so do not expect the Google Sheets menu path to match. Sheets also handles formula results in its own interface, and a formula that relies on array expansion needs empty cells available for its output.

For many everyday checks, COUNTIF is the most portable and understandable choice. It is a good fit for a single column or a small number of fields, and the formula can be inspected row by row. Regardless of the application, use a helper column or conditional formatting when you need to review records before deletion, and reserve the removal command for a clearly defined cleanup task.

Frequently Asked Questions

What is the formula to find duplicates in Google Sheets?

For values in A2:A100, use =COUNTIF($A$2:$A$100,A2)>1 beside the first value and fill down. TRUE means the value occurs more than once in the checked range.

How do I highlight duplicates in Google Sheets?

Select the range, open Format > Conditional formatting, choose Custom formula is, and enter a COUNTIF rule based on the first cell of the selected range. Set a color and choose Done.

How do I find duplicates in two columns?

Use COUNTIFS to compare both fields on the same row. For example, compare the ID in column A and date in column B if that pair, rather than either field alone, defines a duplicate.

Does COUNTIF distinguish uppercase and lowercase?

No. COUNTIF is case-insensitive, so entries that differ only in capitalization are counted as matches. Check case-sensitive requirements separately before using a formula to remove records.

Can a formula delete duplicate rows?

No. Formulas can count, flag, or display duplicates, but they do not delete the source rows. To remove rows in Sheets, use Data > Data cleanup > Remove duplicates after reviewing the selected columns and keeping a backup if needed.

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.