To find duplicate values in Excel, enter =COUNTIF($A$2:$A$100,A2)>1 in a helper column beside your list, assuming the values are in A2:A100. It returns TRUE when the value in the current row appears more than once in that range; fill it down to flag every occurrence of each duplicate.
The basic COUNTIF duplicate formula
COUNTIF checks a range for cells that meet a criterion and returns how many matches it finds. For a duplicate check, the range is the full list and the criterion is the value in the current row. The dollar signs keep the list range fixed as you copy the formula down, while the relative reference A2 changes to A3, A4, and so on.
With values in A2:A100 and the formula entered in B2, copy B2 down alongside the list. A result of TRUE means that the value appears at least twice in A2:A100; FALSE means it appears once. A value appearing three times will produce TRUE in all three corresponding rows. This formula identifies repeated values, not just the later copies.
To display words instead of TRUE and FALSE, use a helper column with a label formula. With values in A2:A100 and the first result in B2:
=IF(COUNTIF($A$2:$A$100,A2)>1,"Duplicate","Unique")
Copy it down through the rows you want to check. The result for each row will read Duplicate or Unique. If you add data below row 100, update the range or use a range that includes the new rows; otherwise, the formula will not check them.
How COUNTIF identifies a match
The formula compares each row’s value against the specified range and counts matching cells. The test >1 is what turns a count into a duplicate flag: a count of 1 means the value occurs only once, while 2 or more means it occurs repeatedly. The comparison is not case-sensitive, so COUNTIF treats “North” and “north” as matches.
It checks the value itself, not the entire row. If two rows have the same name but different order numbers, a check of the name column alone will still flag the names. Decide what “duplicate” means for your data before choosing the range and criterion. It might mean a repeated email address, a repeated invoice number, or a repeated combination of fields.
The range in the basic example is deliberately limited to the data rows, rather than the entire column. That keeps a header such as “Customer” out of the count and makes the intended comparison clear. A whole-column reference can be convenient for changing lists, but make sure the formula does not count a header or unrelated content in the same column.
Highlight duplicates without a helper column
If you want to see duplicates directly in the list, use conditional formatting. Select the cells to check, then choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. Set the formula and choose a formatting style, such as a fill color. With values in A2:A100 and that range selected, use:
=COUNTIF($A$2:$A$100,A2)>1
In a conditional-formatting formula, the reference to the first selected cell should be relative by row, as A2 is here. Excel evaluates the rule for each cell in the selection. The absolute range stays fixed, and the row reference moves as the rule is applied. If you select a range starting at A5 instead, use A5 as the relative criterion reference and adjust the counted range to match the intended list.
Conditional formatting changes how cells look; it does not change their values or delete anything. A helper column is more useful when you need to filter, sort, or audit the results. On Excel for Mac, conditional formatting is also available from the Home tab, though the dialog appearance can vary by release. In Excel for the web, the same general approach is available, but some rule-editing controls may look different.
Check duplicates using more than one column
Sometimes a duplicate is defined by a pair of values rather than one field. For example, a customer may have multiple orders, so a repeated customer name is not enough to identify a duplicated order. COUNTIFS checks several conditions together. With customer names in A2:A100, order IDs in B2:B100, and the result in C2:
=COUNTIFS($A$2:$A$100,A2,$B$2:$B$100,B2)>1
Copy the formula down. It returns TRUE when the same customer-and-order-ID combination appears more than once. Each criteria range must cover the same number of rows. You can extend this pattern to include further fields, such as date or product code, by adding another range and its matching criterion.
Choose fields that together identify the record you care about. For instance, checking a first name and last name may flag two different people with the same name. An employee ID or email address might be a better key. Conversely, including too many fields can hide a duplicate if a minor detail differs between the two records.
Choose whether to flag every copy or only later copies
The basic formula flags every occurrence of a repeated value, including the first one. That is useful when you want to review all members of each duplicate group. If your task is to mark only the second and subsequent occurrences, use a growing range that starts at the top of the list and ends at the current row.
With values in A2:A100 and the first result in B2, use:
=COUNTIF($A$2:A2,A2)>1
Fill it down. On the first appearance, the range contains just that row, so the count is 1 and the result is FALSE. On a later appearance, the range includes an earlier matching value, so the result is TRUE. This can help identify which rows could be removed while preserving the first occurrence, but review the records before deleting anything.
For a list that may contain blank cells, avoid flagging blanks as duplicates. With values in A2:A100 and a result in B2, use:
=AND(A2<>"",COUNTIF($A$2:$A$100,A2)>1)
This returns FALSE for an empty A2 and otherwise applies the duplicate test. If a cell contains spaces rather than being truly empty, it is not an empty cell for this check. Cleaning inconsistent entries may be necessary before the counts reflect the duplicates you mean to find.
Common reasons a duplicate check surprises you
- Extra spaces: “Alex” and “Alex ” can look identical on screen but may be treated as different text. Consider cleaning the source data or checking suspicious values in the formula bar.
- Different data types: A number stored as text may not behave like a number stored as a numeric value in every comparison or downstream task. Standardize the column when values that look alike are not being counted as expected.
- Wildcards in text: In COUNTIF criteria, an asterisk can match a sequence of characters and a question mark can match one character. If your actual data contains these symbols, the criterion may behave like a pattern rather than a literal value.
- Range ends too soon: A formula checking A2:A100 will ignore values below row 100. Expand the range when the list grows, or set up the workbook so that new rows are included in the checked range.
- Case differences: COUNTIF is not case-sensitive. If capitalization distinguishes records in your workflow, this formula alone will not enforce that distinction.
- Wrong definition of duplicate: A repeated value in one column does not prove that two complete records are duplicates. Confirm whether the matching key should be one field or a combination of fields.
When results seem wrong, first inspect the precise cell contents and verify the range. A visible difference in spacing, a value stored as text, or an accidental header in the range can explain a surprising result. Also check that the formula’s relative reference points to the current row after copying it down.
Finding duplicates is different from removing them
COUNTIF is a detection method: it marks or counts repeated values but leaves the source list unchanged. If you want to remove repeated records, review the data first and save a separate copy of the workbook or source sheet. In Excel for Microsoft 365 on Windows, select the data and choose Data > Remove Duplicates. Confirm whether the first row contains headers, then select the columns Excel should use to decide which records are duplicates.
That column choice matters. Selecting only an email column removes rows with a repeated email, even if other fields differ. Selecting every column looks for rows that match across all selected fields, so records with the same email but a different address may remain. Excel keeps one occurrence and removes the other matching rows from the selected data; the action changes the data rather than merely highlighting it. Undo is available immediately afterward, but do not rely on it as your only safeguard.
For a safer review, filter the helper-column results to TRUE, inspect the records, and decide what to keep before deleting rows. Conditional formatting is a visual alternative for a quick scan. Remove Duplicates is convenient when the rule is clear and deletion is intended, while COUNTIF is better when you need a transparent, repeatable check that leaves all records available for review.
Frequently Asked Questions
Does COUNTIF highlight the first occurrence too?
Yes. The test for a count greater than 1 flags every row whose value appears more than once, including the first occurrence. Use a growing range such as the one shown above when you want to flag only later occurrences.
Can COUNTIF find duplicates in two columns?
Yes. Use COUNTIFS with one range-and-criterion pair for each field that defines a duplicate. The ranges need to be the same size, and the chosen fields should represent the record identity you want to test.
Why does COUNTIF not find values that look the same?
Check for leading or trailing spaces, different stored data types, and a formula range that omits some rows. Also remember that COUNTIF ignores differences in letter case and treats wildcard characters as patterns in criteria.
Can I find duplicates without adding a formula column?
Yes. Select the list and create a conditional-formatting rule using the COUNTIF test. This colors matching cells but does not provide a TRUE/FALSE value to filter or use in another calculation.
Does COUNTIF remove duplicate values?
No. It reports whether values repeat; it does not delete cells or records. To remove repeated rows, use Data > Remove Duplicates after choosing the correct columns, and keep a copy of the original data if you may need it.
Related
- Cannot Close Excel Because a Dialog Box Is Open? Fixes
- Can You Shade Half a Cell in Excel? Options Explained
- Can You Shuffle Names in Excel? Methods and Examples
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- XLOOKUP Excel Version: Compatibility and AvailabilityXLOOKUP is available in Microsoft 365, Excel 2021, and Excel 2024, as well as Excel for the web…
- Excel Won’t Let Me Filter by Color? Fixes That WorkExcel usually lets you filter a column by cell color, font color, or icon from its filter drop-down:…
- Text Join in Excel With a Comma: Formula and ExamplesTo join the values in cells A2:A10 with a comma and a space, use =TEXTJOIN(", ",TRUE,A2:A10). The formula…
- Insert a Row in Excel With a Formula: Real OptionsA formula cannot insert a physical worksheet row or move existing cells. To show a blank row inside…
- Excel Formula for Time Clock Hours In and Out by ShiftFor clock-in time in A2 and clock-out time in B2, enter =MOD(B2-A2,1)*24 to return the shift length as…
- Excel Formula for Holiday Dates: Fixed and Moving DaysFor a holiday that falls on the same month and day each year, use =DATE($B$1,1,1), assuming the year…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.