COUNTIF Formula for Duplicates in Excel: Find and Flag

Formulas & functions · Updated November 21, 2026

To flag every value that appears more than once in Excel, enter =COUNTIF($A$2:$A$100,A2)>1 in a helper column beside your data, then fill it down. This checks the value in A2 against the full range A2:A100 and returns TRUE when it occurs at least twice.

What COUNTIF checks

COUNTIF counts cells in a range that meet one condition. Its first argument is the range to search; its second is the criterion to match. When the criterion is a cell reference, Excel checks for cells that match that cell’s value. The dollar signs in $A$2:$A$100 keep the search range fixed as you copy the formula down, while the relative reference A2 changes to A3, A4, and so on.

The formula returns a logical result: TRUE means the value is repeated in the selected range, and FALSE means it appears only once. To see the actual number of occurrences instead, use the same COUNTIF without the comparison to 1. For example, a result of 3 means the value is found three times in the search range. Choosing between a count and a TRUE/FALSE flag depends on whether you need to investigate the number of repeats or simply identify them.

Flag duplicates with a helper column

Suppose column A contains names in A2:A7, and you want a clear result beside each name. Put a heading such as Duplicate? in B1, enter the formula below in B2, and fill it through B7. The range is fixed so that every row is checked against the complete list.

With names in A2:A7 and the formula entered in B2, use =COUNTIF($A$2:$A$7,A2)>1. If A2:A7 contains Ana, Luis, Ana, Mei, Luis, and Omar, respectively, the results in B2:B7 are TRUE, TRUE, TRUE, FALSE, TRUE, and FALSE. Both appearances of Ana and both appearances of Luis are marked, not just the later occurrence.

To fill the formula down, select B2 and drag the small square at the lower-right corner of the cell to B7. You can also copy B2, select B3:B7, and paste. In Excel for Microsoft 365 on Windows, a double-click on the fill handle can fill adjacent rows when the neighboring data column is continuous. Check the last row afterward, particularly if there are blank cells in the adjacent column.

Count repeats or mark only later occurrences

There are two useful meanings of “duplicate.” The formula above flags every member of a repeated group, including its first appearance. That is usually best for reviewing a list because both the original and the repeated entries are visible. If you instead want to leave the first occurrence unmarked and identify only later repeats, make the COUNTIF search range grow as the formula is copied downward.

With values in A2:A7 and the formula entered in B2, use =COUNTIF($A$2:A2,A2)>1, then fill down. The first reference stays anchored at A2, but the ending reference advances with each row. For the sample list, Ana in A4 and Luis in A6 return TRUE; their first appearances return FALSE. This pattern is useful for reviewing import files or deciding which later rows need attention, but it does not label the original row as part of a duplicate group.

To show the occurrence number rather than a TRUE/FALSE result, use the same expanding-range pattern without comparing the count to 1. With values in A2:A7 and the formula in B2, enter =COUNTIF($A$2:A2,A2) and fill down. The sample produces 1, 1, 2, 1, 2, 1. This makes it easier to distinguish a second occurrence from a third or fourth one.

Highlight duplicates with Conditional Formatting

You do not need a helper column if the goal is simply to highlight repeated cells. Select A2:A100, then choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. With A2:A100 selected and A2 as the top-left cell, enter =COUNTIF($A$2:$A$100,A2)>1 in the formula box, choose a format, and confirm the rule. Excel applies the format to cells for which the formula returns TRUE.

The formula’s relative A2 reference matters: it lets Excel test each selected cell in turn. The absolute search range keeps the comparison set unchanged. If your selection begins at A5, use A5 as the relative reference instead. A common mistake is to select A2:A100 but write a formula referring to A1; the rule then tests the wrong row and can produce confusing highlights.

Conditional Formatting changes how cells look; it does not delete or change their contents. You can edit or remove the rule through Home > Conditional Formatting > Manage Rules. On Mac, the Conditional Formatting controls are also on the Home tab, though dialog details can vary with the Excel version. The COUNTIF formula itself works the same way.

Exclude blank cells from the duplicate check

Blank cells can complicate duplicate checks, especially when a column contains formulas that return an empty string or many unused rows are included in the selected range. To avoid flagging empty-looking cells, add a test that requires the current cell to be nonblank before checking its count.

With data in A2:A100 and the formula entered in B2, use =AND(A2<>"",COUNTIF($A$2:$A$100,A2)>1) and fill down. AND returns TRUE only when both conditions are true: A2 is not empty, and its value occurs more than once. This prevents blank rows from being labeled as duplicates. If the cells contain formulas returning an empty string, the comparison to an empty string also treats those results as blank for this purpose.

When a blank-looking cell is still flagged, inspect the cell rather than relying only on its display. It may contain a space, a nonbreaking space copied from a webpage, or a formula result that is not actually empty. COUNTIF compares underlying cell values, not just what appears on screen. Cleaning inconsistent source data may be necessary before a duplicate check can give the result you expect.

Common reasons the result looks wrong

  • The range moved while copying. If the search range is written as A2:A100 without dollar signs, it can shift on each row. Use $A$2:$A$100 for a fixed range, or use an expanding range intentionally when marking later occurrences.
  • Spaces or hidden characters differ. “North” and “North ” may look almost identical but are not the same text. Leading spaces, trailing spaces, and copied nonprinting characters can prevent a match. Data > Text to Columns or a cleaning step can help, but check the data before changing it.
  • Numbers and text are not consistently stored. A number stored as text can behave differently from a numeric value, even if both display as 123. Standardize the column’s data before counting if imported values appear identical but do not match.
  • Formatting can disguise the real value. Two dates may display with different formats while representing the same underlying date, or two values may look alike after rounding while having different stored decimals. COUNTIF evaluates the stored values, not merely their displayed appearance.
  • COUNTIF does not distinguish letter case. In ordinary use, Ana and ANA are treated as a match. If capitalization should make values distinct, COUNTIF alone is not a case-sensitive duplicate test.
  • Wildcard characters have special meaning. In criteria, an asterisk can match a sequence of characters and a question mark can match one character. If your data includes literal wildcard characters, COUNTIF may match more than you intend; a tilde is used to escape wildcard characters in criteria.

COUNTIF versus removing duplicates

COUNTIF identifies repeated values while keeping the source rows intact. That makes it suitable for audits, review lists, and reports where you need to see which records repeat. Conditional Formatting is another non-destructive option when visual highlighting is enough. Both approaches respond to changes in the data after recalculation, unlike a one-time manual inspection.

Excel’s Remove Duplicates command has a different purpose: it removes repeated records from the selected data. On Windows, select the relevant data and choose Data > Remove Duplicates. Review the selected columns carefully before confirming, because the command changes the dataset and keeps only one occurrence according to the columns you choose. Make a copy of the data first if you need to preserve every original row. Use COUNTIF when you need to find or review repeats; use Remove Duplicates only when you intend to delete them.

For a separate list of distinct values, Excel for Microsoft 365 also has the UNIQUE function. With source values in A2:A100 and the formula entered in an empty cell such as C2, use =UNIQUE(A2:A100). It returns a spilled list of distinct values without deleting the source data. UNIQUE is available in Microsoft 365 and Excel 2021 or later; it is not a replacement for a duplicate flag when you need to identify the original rows that repeat.

Frequently Asked Questions

Does COUNTIF count the first occurrence as a duplicate?
When the formula checks the full list with a condition greater than 1, it flags every occurrence of a repeated value, including the first. Use an expanding range such as $A$2:A2 if you want only the second and later occurrences flagged.

How do I count how many times a value appears?
With the list in A2:A100 and the value to check in C2, enter =COUNTIF($A$2:$A$100,C2) in D2. D2 returns the number of matches for C2 in the list. For example, if Ana appears twice, the result is 2.

Can COUNTIF find duplicates across two columns?
Yes. Put both columns into one search range if they form a single list, or use a suitable combined range when the cells are contiguous. For noncontiguous ranges, COUNTIF accepts one range per function, so add separate counts if you need to check each area. Decide whether the two columns represent the same kind of value before combining them.

Is COUNTIF case-sensitive?
No. COUNTIF treats uppercase and lowercase text as equivalent. If differences in capitalization matter, use a case-sensitive approach built around EXACT rather than relying on COUNTIF alone.

Why does my duplicate formula return FALSE for values that look identical?
Check for extra spaces, hidden characters, inconsistent number-versus-text storage, or different underlying decimal or date values. Also confirm that the formula’s search range includes all relevant rows and that its cell reference points to the correct row.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

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

Spotted an error in this guide? Let us know.