To find duplicate values in Excel, use COUNTIF to count each value within the list: with values in A2:A100, enter =COUNTIF($A$2:$A$100,A2)>1 in B2 and fill down. TRUE marks every occurrence of a value that appears more than once; FALSE means it appears only once. You can also use COUNTIF to show the number of occurrences or highlight duplicates without adding a helper column.
How COUNTIF finds duplicates
COUNTIF has two arguments: a range to examine and a criterion to look for. It counts how many cells in the range meet that criterion. In the example above, the range is locked as $A$2:$A$100, while A2 points to the value on the current row. When the formula is filled down, the lookup reference changes to A3, A4, and so on, but the list being searched stays the same.
The greater-than comparison turns the count into a simple test. A result of 1 is not a duplicate; a result of 2 or more is. For instance, if “Maple” appears in A2, A9, and A18, COUNTIF returns 3 for each of those rows, so the test returns TRUE in all three. The dollar signs are important: they keep the search range from shifting as you copy the formula.
To see the actual number of occurrences instead of TRUE or FALSE, with the same values in A2:A100 and the formula entered in B2, use =COUNTIF($A$2:$A$100,A2). A 1 means unique in that range; a number above 1 means repeated. This count can be useful for sorting or filtering, while a TRUE/FALSE result is often easier to scan.
Flag every occurrence of a duplicate
Use the greater-than-one test when you want every copy identified, including the first one. This is useful when reviewing a list for repeated customer IDs, product codes, email addresses, or other values that should occur only once. Put the formula beside the first data row, then copy it through the records.
For example, suppose A2:A6 contains Cedar, Birch, Cedar, Ash, and Cedar. With those entries in A2:A6 and the formula in B2, =COUNTIF($A$2:$A$6,A2)>1 returns TRUE for Cedar in rows 2, 4, and 6, and FALSE for Birch and Ash. The result is based on the complete list, not just the rows above the current one.
If the list grows, update the end of the range or use a larger range that includes likely additions. A fixed range that stops at row 100 will not detect a matching value added in row 101. For frequently expanding lists, converting the data to an Excel table can make ranges easier to maintain; table references are also more readable, though the basic COUNTIF logic remains the same.
Mark only the second and later copies
Sometimes the first occurrence is the valid record and only later repetitions need attention. In that case, count from the top of the list to the current row. With values in A2:A100 and the formula entered in B2, use =COUNTIF($A$2:A2,A2)>1 and fill down.
The first range endpoint, $A$2, stays fixed, while the second endpoint expands as the formula moves down. The first time Excel encounters a value, its running count is 1 and the result is FALSE. The next time it appears, the count reaches 2 and the result is TRUE. In the Cedar example, the first Cedar is not flagged, but the second and third are. This approach is sometimes called marking duplicates after the first occurrence.
To display a label instead of TRUE or FALSE, with the same list in A2:A100 and the formula in B2, use =IF(COUNTIF($A$2:$A$100,A2)>1,"Duplicate",""). It displays Duplicate for repeated values and leaves other rows visually blank. If you need to distinguish the original from later copies, combine IF with the running-count test: with the same layout, use =IF(COUNTIF($A$2:A2,A2)>1,"Later copy","").
Highlight duplicates with Conditional Formatting
For a visual check, select A2:A100 and choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. Choose a formatting style in the dialog and confirm. Excel colors values it recognizes as repeated within the selected range. This is quick for review because it does not require a helper column, and the formatting updates when values in the selected range change.
The built-in Duplicate Values rule is a good choice when the goal is simply to see repeats. A formula-based rule gives more control, such as highlighting only later copies or excluding blank cells. To highlight all repeated nonblank values, select A2:A100, choose Home > Conditional Formatting > New Rule, then choose “Use a formula to determine which cells to format.” With values in A2:A100 and the selected range beginning at A2, enter =AND(A2<>"",COUNTIF($A$2:$A$100,A2)>1), choose a format, and confirm. The relative A2 reference adjusts for each cell while the counted range remains fixed.
Conditional formatting changes appearance, not the underlying data. It does not remove or merge records, and colored cells may need a separate filter or review step before you decide what to do with them. On Excel for Mac, the Conditional Formatting commands are also available from the Home tab, though the dialog layout can differ slightly from Windows. Excel for the web offers conditional formatting as well, with some differences in dialog options.
Count duplicates while ignoring blanks
A blank cell can produce a confusing result because COUNTIF can count blank entries when the criterion is blank. If empty rows should not be treated as repeated values, add a test that checks the current cell first. With values in A2:A100 and the formula in B2, enter =AND(A2<>"",COUNTIF($A$2:$A$100,A2)>1). The first condition is FALSE for an empty A2, so that row is not marked, even if the range contains other empty cells.
This check also excludes a formula result that displays as an empty string, such as a cell whose formula returns "". That can matter in reports where formulas fill a column but intentionally show nothing until data is entered. If blanks have business meaning in your dataset, decide whether empty values should count as duplicates before applying a rule; excluding them is not always the right choice.
Find duplicate combinations across columns
A value may be allowed to repeat on its own while a particular combination of fields must be unique. For example, the same customer can place several orders, but the combination of customer ID and order date might be expected only once. COUNTIF checks one criterion; COUNTIFS checks multiple criteria together.
With customer IDs in A2:A100, order dates in B2:B100, and the formula in C2, use =COUNTIFS($A$2:$A$100,A2,$B$2:$B$100,B2)>1. TRUE means that the ID and date on that row occur together more than once. Each criteria range should cover the same rows, and each row’s criteria should refer to cells on that row. You can extend the same pattern with more range-and-criterion pairs when a record is defined by three or more fields.
Be precise about what makes a record a duplicate. If a customer ID and date are the key, including an amount in the test would treat two otherwise identical transactions with different amounts as distinct. Conversely, leaving out a field that matters can flag legitimate separate records. Write down the fields that define uniqueness before building the formula.
Common causes of unexpected results
COUNTIF is not case-sensitive, so it treats text such as Maple and MAPLE as matching. If capitalization distinguishes records in your situation, COUNTIF alone will not enforce that distinction. Also check for inconsistent spaces, punctuation, or imported characters. A name with a trailing space can look identical on screen but behave differently from the clean version.
COUNTIF criteria support wildcards: an asterisk matches a string of characters, and a question mark matches one character. That is useful for intentionally searching patterns, but it can surprise you if a value contains those characters and you expect a literal match. A tilde escapes wildcard characters in criteria. For ordinary IDs and names, the simplest safeguard is to inspect suspicious values and standardize the source data rather than assume every visible match is an exact match.
Imported numbers and text can also be inconsistent. A code that looks like 00125 may be stored as text in one row and represented differently in another, or a date may be text rather than a true Excel date. Check the cell format and underlying values, and normalize the column before relying on duplicate results. Formatting alone does not always convert stored text into a number or date.
Finally, verify that the formula refers to the full intended list, starts on the correct row, and uses the right columns. A common error is copying a formula with a relative search range, causing the range to move and omit earlier records. Another is checking a filtered subset mentally while the formula still examines hidden rows in its specified range; filtering does not automatically limit COUNTIF to visible cells.
COUNTIF versus duplicate-removal tools
COUNTIF identifies and counts; it does not delete records. That is an advantage when you need an audit trail or want to decide manually which record to keep. After flagging duplicates, you can filter the helper column for TRUE and review the full rows before taking action.
Excel’s Data > Remove Duplicates command is different: it removes repeated rows from the selected data according to the columns you choose, keeping one occurrence. Use it only when you are ready to change the data, and make a copy first if the original records need to be preserved. COUNTIF is safer for investigation because it leaves the source untouched.
For a separate list containing one copy of each value, Excel for Microsoft 365 and Excel 2021 or later include UNIQUE. With source values in A2:A100 and the formula entered in C2, =UNIQUE(A2:A100) returns a spill range of distinct values. It is useful for creating a clean list, but it does not by itself label which source rows are duplicates. COUNTIF remains the direct option when you need to mark or count repeats in the original data.
Frequently Asked Questions
Does COUNTIF find duplicates in Excel?
Yes. Count each value against the full list and test whether the result is greater than 1. COUNTIF can flag all copies or, with a growing range that starts at the first data row, only later copies.
How do I highlight duplicates without a formula column?
Select the cells to check, then use Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. Choose a style and confirm. Use a formula-based conditional-formatting rule if you need to exclude blanks or apply a more specific test.
How do I ignore blank cells when checking duplicates?
Add a nonblank test with AND before the COUNTIF test. For example, for values in A2:A100, the condition =AND(A2<>"",COUNTIF($A$2:$A$100,A2)>1) will not flag an empty current cell.
Can COUNTIF find duplicates across two columns?
Use COUNTIFS to test a combination of fields, such as an ID and a date. Give each condition its own range and matching row reference. The result identifies rows where that combination appears more than once.
Does COUNTIF distinguish uppercase and lowercase?
No. COUNTIF comparisons are not case-sensitive. If capitalization is meaningful, use a case-sensitive approach instead of relying on COUNTIF alone, and confirm the records are otherwise standardized.
Related
- Can Git Track Excel Files? Version Control Explained
- Can You Use Excel to Randomize a List? Yes, Here’s How
- Calendar Template in Microsoft Excel: Choose and Use
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- PMT Function Syntax in Excel: Arguments and ExamplesIn Excel, the PMT function syntax is =PMT(rate, nper, pv, fv, type). The first three arguments are required;…
- HLOOKUP Examples for Practice: Formulas and Fixes in ExcelFor an exact-match HLOOKUP practice example, put product codes in A1:D1, prices in A2:D2, and stock counts in…
- Excel Formula to Count Cells With Text in Any RangeTo count cells containing text in Excel, use =COUNTIF(A2:A100,"*"), replacing A2:A100 with your range. The asterisk is a…
- Excel Column Subtraction Formula: Examples and TipsTo subtract values in one Excel column from the matching values in another, enter =A2-B2 in the first…
- XLOOKUP Excel Cell References: A Practical GuideIn Excel, an XLOOKUP cell reference is the cell or range you tell the function to search or…
- Excel Won’t Calculate Formulas? Causes, Checks, and FixesIf Excel does not calculate a formula, first check Formulas > Calculation Options and select Automatic. If that…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.