COUNTIF Duplicate Formula in Google Sheets

To flag every nonblank value that appears more than once in Google Sheets, put this formula beside the first data row and fill it down: =AND(A2<>"",COUNTIF($A$2:$A$100,A2)>1). It returns TRUE for each occurrence of a repeated value, including the first; adjust the range and cell references to fit your sheet.

What COUNTIF checks

COUNTIF counts cells in a range that meet one condition. Its structure is COUNTIF(range, criterion): the range is the set of cells to inspect, and the criterion is the value or condition to look for. In a duplicate check, the range usually covers the list, while the criterion points to the current row’s value.

For example, if a list of email addresses is in A2:A100, COUNTIF can ask how many times the value in A2 occurs in that list. A result greater than 1 means the value is repeated. The dollar signs in $A$2:$A$100 lock the search range when you copy the formula down, while the relative reference A2 changes to A3, A4, and so on.

COUNTIF does not delete or change data. It reports a count, and a formula that compares that count with 1 can turn the result into a TRUE/FALSE flag. You can use that flag to review records, filter a helper column, or build a conditional formatting rule.

Decide what you mean by a duplicate

There are two common interpretations. You might want to identify every member of a repeated group, or you might want to mark only the later occurrences after the first one. Pick the definition before writing the formula; both are useful for different tasks.

All occurrences: Every copy of a value is flagged, including the first. Comparing its total count with 1 does this.
Later occurrences only: The first appearance is left unflagged, and only copies below it are marked. This is helpful when you want to preserve the original and inspect extra entries.
Duplicate records: A record is repeated only when a combination of fields, such as name and date, repeats. A single-column check is not enough for this definition.

These definitions can produce different counts. If “North” appears three times, an all-occurrences check flags three cells; a later-occurrences check flags two. That distinction matters when deciding how many rows to remove or how many extra entries need review.

Flag all repeated values in a helper column

Suppose the values to check are in A2:A100, with a header in A1. In B2, enter the formula below and fill it down through B100. The formula assumes that layout, ignores empty cells, and checks each value against the full list:

=AND(A2<>"",COUNTIF($A$2:$A$100,A2)>1)

TRUE means the value appears at least twice in the specified range; FALSE means it appears once or the cell is blank. For example, if A2:A6 contains blue, red, blue, green, red, the results beside those five rows are TRUE, TRUE, TRUE, FALSE, TRUE. Both copies of blue and both copies of red are identified.

To flag only later copies, use a growing range that starts at the first data row and ends at the current row. With the same values in A2:A100 and the formula beginning in B2, use:

=AND(A2<>"",COUNTIF($A$2:A2,A2)>1)

As you fill down, the search range expands one row at a time. On the first occurrence, COUNTIF returns 1; on a later occurrence, it returns 2 or more. With the five-value example above, the third row (the second blue) and fifth row (the second red) return TRUE; the first appearances return FALSE.

In Google Sheets, you can fill a formula down by dragging the small square at the lower-right corner of the selected cell, or by copying and pasting it into the destination cells. If your spreadsheet’s locale expects semicolons between function arguments, replace the commas with semicolons.

Highlight duplicates with conditional formatting

For a visual scan, you can apply a custom formula directly to the range instead of adding a helper column. Select A2:A100, choose Format > Conditional formatting, and set the rule type to Custom formula is. Assuming the selected range begins at A2 and the data is in A2:A100, enter:

=AND(A2<>"",COUNTIF($A$2:$A$100,A2)>1)

Choose a formatting style and select Done. The formula’s A2 reference is relative, so Sheets evaluates the value on each row of the selected range; the dollar signs keep the checked list fixed. To highlight only later copies instead, use the growing-range version =AND(A2<>"",COUNTIF($A$2:A2,A2)>1). The formatting rule marks cells but does not remove duplicates.

Check the “Apply to range” field if the colors seem shifted. The cell reference in a custom formula should match the top-left cell of that range. For example, if the range starts at C5, the formula should refer to C5 rather than A2, while the COUNTIF range should cover the values you actually intend to compare.

Check duplicates across multiple columns

Sometimes a repeated value in one column is legitimate, but a repeated combination of fields is not. To flag rows where both the name and the date match, assume names are in A2:A100, dates are in B2:B100, and the first formula is entered in C2:

=COUNTIFS($A$2:$A$100,A2,$B$2:$B$100,B2)>1

COUNTIFS checks multiple conditions on corresponding rows. In this example, a TRUE result means that the same name-and-date pair occurs more than once. Each criteria range must cover the same number of rows. To ignore rows where either field is blank, assuming the same layout, use:

=AND(A2<>"",B2<>"",COUNTIFS($A$2:$A$100,A2,$B$2:$B$100,B2)>1)

You can extend the idea to more columns by adding another range-and-criterion pair to COUNTIFS. This is useful for records defined by an order number and product, or a person, event, and date. Be deliberate about which fields identify a record: adding a field that varies between otherwise identical rows will make those rows appear distinct.

Common reasons a duplicate check misses a match

If values that look identical are not flagged, or unrelated values are flagged together, check the data and the comparison range before changing the formula.

Spaces or invisible characters: “Avery” and “Avery ” may not behave as the same cleaned value. Imported data can also contain nonbreaking spaces or other characters that are hard to see. A helper column using TRIM can remove ordinary leading, trailing, and repeated spaces; review results before replacing original data.
Case: COUNTIF is not case-sensitive. It treats lowercase and uppercase versions of otherwise identical text as a match. If capitalization is meaningful in your task, COUNTIF alone is not a case-sensitive duplicate test.
Blank cells: A plain COUNTIF test can report empty cells as repeated. Adding a nonblank condition, as in the examples, prevents blanks from being highlighted.
Wrong range: A fixed range that stops too early will miss later entries. A range that includes a title or unrelated notes can create misleading results. Make the list range match the records you intend to compare.
Numbers and text: A value imported as text may not behave as expected beside a numeric value, even if both look alike. Check the cell format and the underlying value, especially with identifiers that may contain leading zeros.
Wildcard characters: In COUNTIF criteria, an asterisk and question mark have wildcard meaning. If your data contains those characters literally, the criterion may match more than the exact text you intended.

For reliable comparisons, consider cleaning a copy of the imported data first, standardizing formats, and confirming the range includes every intended row. Avoid deleting records based only on a visual match when values such as account numbers, dates, or leading zeros have operational meaning.

Remove duplicates only after reviewing them

If the goal is to remove repeated rows rather than identify them, use the review results first. Google Sheets provides Data > Data cleanup > Remove duplicates. Select the relevant range, indicate whether it has a header row, and choose which columns should define a duplicate. Removing duplicates changes the sheet, so make a copy or confirm you can restore the data before proceeding.

The selected columns determine what Sheets considers a match. Selecting only an email column removes rows with repeated email values, even if other columns differ. Selecting several columns checks the combination instead. This feature is not a replacement for the COUNTIF formula when you need a visible flag, want to preserve all rows, or need to inspect which occurrence came first.

Excel has COUNTIF and COUNTIFS too, so the core formulas are similar there. The instructions above use Google Sheets’ browser menus; Excel’s conditional-formatting interface and data-cleanup commands are arranged differently.

Frequently Asked Questions

How do I highlight duplicates in Google Sheets?
Select the cells, choose Format > Conditional formatting, then choose Custom formula is. Use a COUNTIF rule that refers to the selection’s top-left cell and excludes blanks if needed. Set a color and click Done.

Does COUNTIF flag the first occurrence?
It depends on the range in the formula. Comparing each value with a fixed range containing the full list flags every copy when the count is greater than 1. A range that expands from the first data row to the current row flags later copies only.

Can COUNTIF check duplicates in two columns?
Yes. Use COUNTIFS with one range-and-criterion pair for each field. It flags a row when that combination appears more than once, provided the criteria ranges are the same size.

Why are blank rows highlighted as duplicates?
A formula that counts empty cells can treat multiple blanks as repeated. Add a condition requiring the current cell to be nonblank, such as the AND check shown above, or leave blank rows outside the applied range.

Is COUNTIF case-sensitive in Google Sheets?
No. COUNTIF does not distinguish between uppercase and lowercase letters. If capitalization must distinguish values, use a different, case-sensitive comparison approach rather than assuming COUNTIF will separate them.

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.