To flag every value that appears more than once in Excel, enter =COUNTIF($A$2:$A$100,A2)>1 beside the first value and fill down; it returns TRUE for each duplicate, including the first occurrence. To flag only repeat appearances after the first, use =COUNTIF($A$2:A2,A2)>1 instead.
The Basic COUNTIF Duplicate Formula
COUNTIF checks how many cells in a range meet a criterion. For each row, the criterion in these formulas is the value in that row. If the count is greater than 1, the value occurs more than once in the selected list.
Assuming the list is in A2:A100 and the formula goes in B2, enter =COUNTIF($A$2:$A$100,A2)>1 and copy it down through B100. The dollar signs keep the search range fixed as the formula is copied, while A2 changes to A3, A4, and so on. If “Maple” appears in A2 and A9, both corresponding results are TRUE. A value appearing once returns FALSE.
To display a count rather than TRUE or FALSE, use this version. With the same list in A2:A100 and the formula in B2, enter =COUNTIF($A$2:$A$100,A2). If “Maple” occurs three times, each of those rows displays 3. This is useful when you want to see the frequency, not just identify whether a repeat exists.
Change the range to match your data. For example, if values are in D5:D250, the formula beside the first entry should use that range and refer to D5. Keep the first and last rows consistent with the actual list so unrelated cells do not affect the result.
How COUNTIF Evaluates Each Row
The function has two arguments: the cells to examine and the criterion to look for. In COUNTIF($A$2:$A$100,A2), the first argument is the full list and the second is the current row’s value. The comparison is repeated for every formula you fill down.
The fixed range is important. Without dollar signs, copying a formula down shifts the range as well as the current-cell reference. For example, a formula that starts by searching A2:A100 could shift to A3:A101 on the next row. Anchoring the range with dollar signs prevents this drift. You can also select the range reference while editing and press F4 in Excel for Windows to cycle through reference types; on Mac, the equivalent shortcut is typically Command+T, though keyboard mappings can vary by settings and keyboard.
COUNTIF is not case-sensitive: “Maple” and “MAPLE” count as the same text for this purpose. It compares cell contents rather than just what appears on screen. Two dates that display differently because of number formats may still represent the same underlying date value, while identical-looking text can differ because one cell contains extra spaces.
Flag Only the Second and Later Occurrences
Sometimes the first instance is the original record and only subsequent instances should be marked. Use a growing range so each row is compared with the entries from the top of the list through that row. With values in A2:A100 and the formula in B2, enter =COUNTIF($A$2:A2,A2)>1 and fill down.
For the first occurrence, the running count is 1, so the formula returns FALSE. On a second occurrence it becomes 2 and returns TRUE; later repeats also return TRUE. For example, if the list reads Cedar, Birch, Cedar, Cedar, the results are FALSE, FALSE, TRUE, TRUE. This method identifies repeats relative to the list’s current order. Sorting the list can change which row is considered the first occurrence, but it does not change whether the value is duplicated overall.
To see the running count instead of a TRUE/FALSE result, use the same cell layout and enter =COUNTIF($A$2:A2,A2). The four example values would return 1, 1, 2, and 3. This can help distinguish a second occurrence from a fifth one.
Highlight Duplicates Without a Helper Column
Conditional formatting can color duplicates directly in the list. Select A2:A100, then choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. Enter the formula below, select a format, and confirm the rule. With the selected range starting at A2, use =COUNTIF($A$2:$A$100,A2)>1. The formula’s A2 reference is relative so Excel evaluates each selected cell in turn.
This rule highlights every occurrence of a repeated value, including its first appearance. To highlight only later appearances, keep the same selected range and use =COUNTIF($A$2:A2,A2)>1. The resulting colors update when values change, unlike a one-time fill color. On Excel for Mac, Conditional Formatting is also available from the Home tab; labels and dialog layout can differ slightly. Excel for the web supports conditional formatting, although some rule-editing options may be arranged differently.
For a built-in duplicate highlighting option, select the list and use Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. This is quick when the goal is simply to highlight all duplicates. A formula rule is more flexible: it can highlight only later occurrences, apply to a chosen range, or include additional conditions.
Exclude Blank Cells and Handle Messy Data
Blank cells can be counted as matching blank cells. If a list contains several empty rows, the basic duplicate formula may therefore mark those rows as duplicates. To ignore blanks, use an AND check. With entries in A2:A100 and the formula in B2, enter =AND(A2<>"",COUNTIF($A$2:$A$100,A2)>1) and fill down. The first test requires the current cell to be nonblank; the second checks whether its value occurs more than once.
Leading or trailing spaces are a common reason that values which look alike do not match. For example, “North” and “North ” are different text strings. If the source data is inconsistent, clean it in a helper column before counting. With raw text in A2:A100, enter =TRIM(A2) in B2 and fill down, then run the duplicate formula against B2:B100. TRIM removes extra ordinary spaces at the start and end and reduces repeated spaces between words to one. It does not remove every possible nonprinting character, so data imported from websites or other systems may need additional cleaning.
COUNTIF treats the characters * and ? as wildcards in criteria: * can match any sequence of characters and ? can match one character. This can matter when the value being checked literally contains one of those symbols. The tilde character (~) is used to escape a wildcard in a COUNTIF criterion. For ordinary names, codes, and numbers without wildcard characters, the standard formula is generally sufficient.
Find Duplicates Based on More Than One Column
A duplicate record may mean that a combination of fields repeats, even though neither field is unique on its own. For example, the same customer might have multiple orders, so a repeated customer name alone is not enough to identify a duplicate transaction. COUNTIFS can test several criteria at once.
Assume customer names are in A2:A100, order dates are in B2:B100, and the formula is entered in C2. To flag rows where the combination of customer and date appears more than once, use =COUNTIFS($A$2:$A$100,A2,$B$2:$B$100,B2)>1 and fill down. If “Lee” has two rows with the same date, both are TRUE; a row with that customer on another date is not flagged by this test.
Each criteria range in COUNTIFS must cover the same number of rows. To add another field, add another range and its matching criterion, such as a product code. Decide carefully which columns define a duplicate: including a field that varies between repeated records can prevent a genuine duplicate combination from being found.
Common Problems and Formula Checks
- The formula is copied but results look inconsistent: Check that the full data range has dollar signs and that the current-row reference points to the correct column and first row.
- Some apparent duplicates are missed: Compare spaces, number-versus-text storage, and date values. Formatting can make different underlying values look alike, and extra spaces can make similar text unequal.
- Every row appears to be a duplicate: Confirm that the formula is not counting a much larger range containing unrelated repeated values, and check that the criterion reference is the cell on the same row.
- Blank rows are highlighted: Add the nonblank test shown above or limit the selected range to populated rows.
- Conditional formatting colors the wrong cells: Check that the rule formula refers to the first cell in the Applies to range. If the range begins at A2, the relative reference should normally begin with A2.
Also check whether your Excel installation expects commas or semicolons between formula arguments. The examples here use commas, as requested for standard English-language Excel syntax; regional settings can affect the separator Excel accepts. If a formula returns an error, verify the separator and that every parenthesis is paired.
COUNTIF Versus Remove Duplicates
COUNTIF identifies repeated values while leaving the source data intact. It is a good choice when you need a reviewable flag, want to count occurrences, or expect the list to change. Conditional formatting is suitable for a visual scan, while a helper column makes it easy to filter TRUE results and inspect the relevant rows.
Excel’s Remove Duplicates command changes the data by deleting repeated rows, keeping one occurrence according to the selected columns and the order of the list. It is not a substitute for checking records when repeated entries may be legitimate. Make a copy before removing records, and decide which columns define a duplicate. For a reversible review, calculate or highlight first, inspect the matches, and remove rows only when you have confirmed they should go.
Frequently Asked Questions
Does COUNTIF count the first occurrence as a duplicate?
With the full-range formula =COUNTIF($A$2:$A$100,A2)>1, yes: every occurrence of a repeated value returns TRUE, including the first. Use the growing-range formula to flag only the second and later occurrences.
How do I count how many times each value appears?
With values in A2:A100 and a formula beside the first value in B2, use =COUNTIF($A$2:$A$100,A2) and fill down. Each row returns the total number of times its value appears in the full list.
Can COUNTIF find duplicates across two columns?
Yes. With names in A2:A100, dates in B2:B100, and a formula in C2, use =COUNTIFS($A$2:$A$100,A2,$B$2:$B$100,B2)>1 to flag repeated name-and-date combinations.
Is COUNTIF case-sensitive?
No. COUNTIF considers text values that differ only by capitalization to be matches. If capitalization must distinguish records, COUNTIF alone is not sufficient; a case-sensitive formula requires a different approach.
Will the duplicate formula update when I add rows?
Only if the formula’s search range includes the new rows. Extend the range when adding data, or use an Excel Table so the data range can expand as records are added. Check that any helper formulas and conditional-formatting Applies to range also include the new rows.
For most lists, start with the full-range COUNTIF formula to flag all repeats, or the growing-range version to preserve the first occurrence as the original. Add a blank check if needed, and use COUNTIFS when a duplicate is defined by a combination of columns.
Related
- Clean-Looking Excel Sheets: Practical Formatting Tips
- Clean Up an Excel Spreadsheet: A Practical Guide
- Clean an Excel Spreadsheet: Steps for Cleaner Data
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Rank in Excel Ignoring Blanks: Formula and ExamplesYes. In Microsoft Excel, RANK.EQ ignores blank cells in its reference range automatically. To also leave the rank…
- Excel Formulas: Function List, Categories & ExamplesThere is no single finite list of every Excel formula: a formula is any expression you build, while…
- String Functions in JavaScript: The Essential GuideString functions in JavaScript are built-in methods that let you manipulate and inspect text values, from finding and…
- Excel OFFSET Function: How It Works and When to Use ItExcel’s OFFSET function returns a reference to a cell or range a chosen number of rows and columns…
- Excel SUBSTITUTE Multiple Values: Formulas and TipsTo substitute multiple values in Excel, nest the SUBSTITUTE function for each old-to-new text pair, or use a…
- SUMIF Criteria Examples: Text, Dates, Numbers, and MoreIn Excel, SUMIF adds values that match one condition; for example, with categories in A2:A7 and amounts in…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.