With values in A2:A100, enter =COUNTIF($A$2:$A$100,A2)>1 in B2 and fill it down. TRUE marks a value that appears more than once in the list, and FALSE marks a value that appears only once.
Set up a basic duplicate check
COUNTIF counts cells in a range that meet a criterion. In this example, the range is the whole list in column A, while the criterion is the value on the current row. The dollar signs keep the list range fixed when you copy the formula down; the reference A2 changes to A3, A4, and so on.
The formula marks every copy of a repeated value, including its first occurrence. For example, if A2:A7 contains Cedar, Birch, Cedar, Maple, Birch, Cedar, the results beside those values are TRUE, TRUE, TRUE, FALSE, TRUE, TRUE. Cedar appears three times and Birch twice, so all five of those cells are flagged.
To use it:
- Keep a header in row 1, if you have one, and put the values to check in A2:A100.
- Enter the formula in B2, or use another empty column for the result.
- Fill the formula down through the rows you want to check. Double-clicking the fill handle can copy it alongside a continuous neighboring list.
- Filter the result column for TRUE to review the repeated values.
What COUNTIF is counting
In the formula, $A$2:$A$100 is the range Excel searches, and A2 is the criterion it searches for. The comparison is recalculated on each row as you fill down. The test >1 converts the count into a TRUE-or-FALSE result: one match is not a duplicate, but two or more matches are.
If you want the number of occurrences rather than a flag, use this version. With values in A2:A100 and the formula entered beside the first value in B2, it returns the count for that row:
=COUNTIF($A$2:$A$100,A2)
For example, if Cedar occurs three times in the range, the result beside each Cedar is 3. This is useful when the total matters, such as deciding whether a name appears twice or dozens of times. The count includes the current row, so any result greater than 1 indicates a repeated value.
Flag only the later occurrences
Sometimes the first instance should remain unmarked and only subsequent copies should be flagged. This is a different definition of “duplicate”: it identifies repeats encountered after the first appearance, rather than marking every member of a repeated group.
With values in A2:A100, enter this in B2 and fill down:
=COUNTIF($A$2:A2,A2)>1
The first range reference starts at A2 and ends at the current row. As the formula moves downward, that range grows. The first Cedar produces a count of 1 and FALSE; the next Cedar produces 2 and TRUE; a third produces 3 and TRUE. Keep the starting cell fixed with a dollar sign, but leave the ending row relative so it can expand.
Use this approach for a review list where you want to preserve the original record and investigate only later entries. Use the full-range version when the goal is to identify every value belonging to a duplicated group.
Highlight duplicates without a helper column
Excel can color duplicates directly. Select the cells to check, then choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. Choose a format and select OK. This built-in rule is a quick choice when you only need a visual highlight, not a TRUE/FALSE result you can filter or use in another calculation.
For custom logic, use a formula-based conditional-formatting rule. With the values in A2:A100 and A2 as the active top-left cell of the selection, select A2:A100, then choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. Enter:
=COUNTIF($A$2:$A$100,A2)>1
Choose a format, confirm the rule, and check that the Applies to range covers the intended cells. The reference to A2 should be relative so Excel checks each cell in turn; the searched range stays fixed. If the selection begins at a different row, adjust the criterion reference to its top-left cell. In Excel for Mac, the Conditional Formatting commands are on the Home tab as well, though the dialog layout can differ slightly.
Check complete rows, not just one column
A repeated value in one column does not necessarily mean an entire record is duplicated. Two customers can share a surname, for example, while having different email addresses. If a record is defined by several fields, compare all of those fields together.
With names in A2:A100, dates in B2:B100, and departments in C2:C100, put this formula in D2 and fill down to count rows matching all three values:
=COUNTIFS($A$2:$A$100,A2,$B$2:$B$100,B2,$C$2:$C$100,C2)>1
COUNTIFS applies each range-and-criterion pair at the same row position, so this flags records where the name, date, and department all match. The example compares exact combinations across these three columns. Add another range-and-criterion pair for each additional field that defines a duplicate record. Decide carefully which fields belong in that definition: including a unique ID may make every row distinct, while leaving out an important field may flag records that are not truly interchangeable.
Handle blanks, spaces, and text differences
Blank cells can be counted as matches by a duplicate check, which can make an incomplete list look as if it contains duplicates. If blanks should be ignored, use this version. With values in A2:A100 and the formula in B2, it returns FALSE for a blank current cell and otherwise checks for repeats:
=AND(A2<>"",COUNTIF($A$2:$A$100,A2)>1)
Extra spaces are another frequent source of confusing results. “Northwind” and “Northwind ” look almost identical but are different text values for an exact comparison. Clean or standardize the source values before relying on a duplicate report. TRIM can remove ordinary leading and trailing spaces, while CLEAN can remove certain nonprinting characters; neither should be assumed to fix every imported-data issue. Nonbreaking spaces and inconsistent punctuation may need separate attention.
COUNTIF is not case-sensitive, so values such as “ABC” and “abc” are treated as the same for this purpose. If capitalization is meaningful in your data, COUNTIF alone does not provide a case-sensitive duplicate test. Also check whether values that look alike have different underlying types or formats, such as a number and text that resembles that number. Consistent data entry and normalization make duplicate results easier to interpret.
Choose a range that includes the right data
A fixed range such as A2:A100 is easy to understand, but it will not check new rows added below row 100 unless you extend the formula and any conditional-formatting range. Choose an upper row that reasonably covers expected additions, or convert the dataset to an Excel Table. Tables expand as you add rows and are generally easier to maintain for growing lists.
Do not include a header in the data range unless it is genuinely one of the values you want to compare. Also make sure the count range and the rows receiving the formulas cover the same list. A mismatch can make a formula report repeats based on data outside the section you meant to audit. After adding or removing records, review both the formula range and the conditional-formatting Applies to range.
Common problems and what to check
- Every row says TRUE: Confirm that the searched range is correct and that the formula is not accidentally comparing against a single fixed criterion such as $A$2 on every row.
- New entries are not detected: Extend the fixed range or use a Table-based workflow that grows with the data.
- A value looks duplicated but is not flagged: Check spaces, punctuation, hidden characters, and whether both cells contain the same underlying value.
- Blank rows are flagged: Exclude blank current cells with the AND version above, or remove unused rows from the range.
- Conditional formatting colors the wrong cells: Confirm that the rule’s top-left cell reference matches the first cell selected and inspect the Applies to range.
One related misconception is that a duplicate flag tells you which record to delete. It only reports that values meet the comparison rule you supplied. Before removing anything, verify that the fields used for comparison really define an unwanted duplicate and that both records do not represent legitimate separate events.
When to use another approach
Use COUNTIF when you want a transparent formula that can flag, count, filter, or feed other logic. Use the built-in Duplicate Values conditional-formatting rule when a quick visual check is enough. For a one-time cleanup, Excel’s Remove Duplicates command can delete repeated rows, but it changes the data rather than simply identifying it. Make a copy or ensure you can restore the original before removing records, and choose the columns that define a duplicate carefully.
For ongoing checks, a helper column with COUNTIF is often easier to audit than a color alone because each row has a visible result. For large or more complex cleaning tasks, Power Query can help transform and deduplicate data through a repeatable refresh process. The right choice depends on whether you want to inspect, highlight, count, or actually remove repeated records.
Frequently Asked Questions
Does COUNTIF mark the first copy as a duplicate?
Yes, the full-range test returns TRUE for every occurrence of a value that appears at least twice, including its first occurrence. Use the expanding-range version in the section above to flag only later occurrences.
How do I count how many times each value appears?
Use COUNTIF with the full list as the range and the current row’s value as the criterion. The result is a number, such as 3 for a value appearing three times, rather than a TRUE/FALSE flag.
Can COUNTIF find duplicates across two columns?
For a duplicate defined by a combination of fields, use COUNTIFS and provide a range-and-criterion pair for each column. This checks whether the complete combination occurs more than once.
Is COUNTIF case-sensitive?
No. COUNTIF treats text that differs only by capitalization as matching. If case distinguishes records in your workflow, use a case-sensitive method instead of relying on COUNTIF alone.
Can I highlight duplicates instead of showing TRUE or FALSE?
Yes. Select the data and use Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values for a quick highlight, or create a formula-based rule for more control.
Related
- Cannot Find Data Validation in Excel? Here’s Where
- Cannot Close Excel Because a Dialog Box Is Open? Fixes
- Can You Shade Half a Cell in Excel? Options Explained
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Julian Date Formula: Day of Year and MoreFor a date in A2, use =INT(A2)-DATE(YEAR(A2),1,0) to return its day number within the year: January 1 is…
- How to Create Rules in Outlook to Sort Email AutomaticallyTo create a rule in Outlook, open Settings > Mail > Rules in new Outlook or Outlook on…
- VLOOKUP Examples for Beginners: A Practical Excel GuideIn Excel, VLOOKUP finds a value in the first column of a range and returns related information from…
- Excel Named Range in a Formula: How to Use It CorrectlyIn Excel, use a named range in a formula by typing its name where you would normally put…
- Excel Rank Formula Without Duplicate Ranks: ExamplesTo give tied values different, consecutive ranks in Excel, enter =RANK.EQ(B2,$B$2:$B$10,0)+COUNTIF($B$2:B2,B2)-1 beside the first value and fill down.…
- Excel Function Keys: What F1–F12 DoIn Excel for Microsoft 365 on Windows, the function keys F1–F12 open Help, edit cells, repeat actions, move…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.