To find repeated values in Excel, enter a COUNTIF formula beside your list and fill it down: with the values in A2:A20, use =COUNTIF($A$2:$A$20,A2)>1. TRUE identifies a value that appears more than once; FALSE means it appears once. You can also use the formula in conditional formatting to highlight matches. The steps below use Microsoft Excel for Microsoft 365 on Windows.
What You’ll Need
- An Excel workbook containing the list you want to check. This guide assumes the values are in column A, with a header in A1 and data in A2:A20; adjust the example range to fit your sheet.
- A blank column beside the list if you want a visible TRUE/FALSE result for each row. Alternatively, use conditional formatting to mark duplicates directly in the list.
- A saved copy of your workbook if the data is important. The formula does not change your original values, but a copy gives you a safe place to review results and make any cleanup decisions.
Step-by-Step
- Identify the values to check. Decide what counts as a duplicate. For a list of email addresses, you might compare the email column alone. For a list of transactions, a duplicate may mean the same customer and date together, rather than a repeated value in just one field. This distinction affects which formula you need. Keep the header out of the range so Excel does not include it in the count.
- Add a results column. If your values are in A2:A20, enter a heading such as Duplicate? in B1. Click B2, the first cell beside your data. The formula will assess the value in A2 against every value in the selected list. A fixed range with dollar signs is important: it keeps the list being searched in the same place as you copy the formula down.
- Enter the COUNTIF test. With the list in A2:A20 and the first result cell in B2, enter
=COUNTIF($A$2:$A$20,A2)>1. Press Enter. COUNTIF counts how many times the value in A2 occurs in A2:A20; the comparison >1 turns that count into a TRUE or FALSE result. For instance, if A2 contains North and North occurs three times in the range, B2 returns TRUE. If it occurs once, B2 returns FALSE. - Fill the formula down the list. Select B2, then point to the small square at the bottom-right corner of the cell and drag it down to B20. You can also copy B2, select B3:B20, and press Ctrl+V on Windows. Excel adjusts the reference to the value being checked on each row, while the dollar signs keep the search range fixed. On a Mac, use Command+V to paste. Check the final row to confirm the formula was copied through all of the data.
- Review the flagged entries. Filter the results column to show TRUE if you want to inspect only repeated values. Select a cell in your data, then choose Data > Filter if filters are not already on. Use the filter arrow in B1 and select TRUE. The TRUE rows include every occurrence of a repeated value, including its first appearance. This is useful when you want to see the full group before deciding what to keep.
- Highlight duplicates without a helper column, if preferred. Select A2:A20. Choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. With the selected range beginning at A2 and the values in A2:A20, enter
=COUNTIF($A$2:$A$20,A2)>1in the formula box. Choose Format, select a fill or font style, and choose OK. Confirm the rule applies to the intended range, then choose OK again. Excel highlights every occurrence of repeated values. On Excel for Mac, the Conditional Formatting options are also on the Home tab; labels or dialog layout can vary slightly by release. - Choose whether blank cells should count. If your list contains empty cells and you do not want them highlighted, use a condition that requires the current cell to be nonblank. With values in A2:A20 and the conditional formatting rule applied starting at A2, enter
=AND(A2<>"",COUNTIF($A$2:$A$20,A2)>1). The same test can go in B2 as a helper-column formula, then be filled down. AND checks both conditions: the cell is not empty, and its value appears more than once. This avoids treating repeated blanks as duplicates. - Check a combination of columns when needed. If a record is only a duplicate when both its name and date match, count the pair rather than checking one column alone. With names in A2:A20, dates in B2:B20, and the result formula in C2, enter
=COUNTIFS($A$2:$A$20,A2,$B$2:$B$20,B2)>1. Fill it down. For example, a name appearing on two different dates is not flagged as a duplicate pair, but a repeated name-and-date combination is. COUNTIFS is the plural counterpart that applies multiple criteria.
Common Mistakes
Leaving the search range relative. A formula such as =COUNTIF(A2:A20,A2)>1 may work in the first row, but copying it down shifts the searched range. Later rows can then miss values near the top of the list. Use dollar signs around both ends of the range, as in $A$2:$A$20, while leaving the cell being checked relative.
Including the heading or missing the last row. A range that starts at A1 includes the column label, while a range that ends at A19 misses data in A20. Make the formula range cover all and only the records you want to compare. If you add more rows later, extend the range or update the formula and conditional formatting rule.
Assuming TRUE means the later copy only. The test greater than one flags every instance of a repeated value, including the first one. That behavior is intentional for reviewing all members of a duplicate group. If you want to flag only the second and later occurrences, use a running count instead. With values in A2:A20 and the result formula in B2, enter =COUNTIF($A$2:A2,A2)>1 and fill down. The expanding range counts from the first data row through the current row, so the first occurrence returns FALSE and later repeats return TRUE.
Expecting COUNTIF to distinguish capitalization. COUNTIF is not case-sensitive, so Apple and APPLE are treated as the same text for this check. Also, leading or trailing spaces can make values that look alike behave differently. Inspect the original data before removing spaces or changing capitalization; those differences may be meaningful.
Tips
Use a helper column when you need to audit the result, filter the list, or explain why a row was flagged. A TRUE/FALSE result is easy to review and does not alter the original cells. Conditional formatting is quicker for visual scanning, but keep the underlying data intact until you have confirmed what should be removed.
For lists that change often, consider converting the range to an Excel table with Insert > Table. Tables expand when you add rows, and formulas entered in a table column typically fill through that column. After creating the table, check that the formula and conditional formatting still cover new rows as expected. This is especially useful for recurring lists, but a quick spot-check after adding data is still sensible.
COUNTIF also interprets the asterisk and question mark as wildcard characters in criteria. If the values themselves contain these characters and you need them treated literally, add a tilde before the character in the criterion. For ordinary names, IDs, or addresses without wildcard symbols, the basic formula is usually enough. Keep IDs in a consistent format, too: values such as 00123 and 123 may not represent the same thing in your workflow even if Excel treats their underlying values similarly.
Before deleting anything, sort or filter the flagged records and compare the full row details. A repeated name may belong to two different people, and two transactions with the same amount may be legitimate. COUNTIF finds repeated values; it cannot decide whether the records are truly redundant. If you do remove rows, retain an untouched copy or use the workbook’s version history so you can recover data if necessary.
Troubleshooting
No duplicates appear, although the list looks repeated. Verify that the formula’s range includes both copies and that it is pointed at the right column. Check for extra spaces, different punctuation, or values stored in different formats. Dates can display identically while containing different underlying times. Compare the actual cell contents and formats, and test a few individual rows before changing the source data.
Everything is marked as a duplicate. Check whether the formula range accidentally includes many blank cells, or whether the tested values are genuinely repeated. If blanks should be ignored, use the AND version shown above. Also inspect the formula in the last few rows: the lookup range should stay fixed, while the single cell reference should move from A2 to A3, A4, and so on.
The result is a formula error or does not update. Confirm the formula starts with an equals sign and that parentheses are balanced. In Excel for Microsoft 365 on Windows, the formulas here use commas between arguments. If your regional settings require another list separator, Excel may expect semicolons instead; use the separator recognized by your installation. If results seem stale, check that calculation is set to automatic under Formulas > Calculation Options > Automatic, or recalculate with F9 on Windows. On Mac, use Fn+F9 if the function keys are configured for hardware controls.
Conditional formatting highlights the wrong cells. Select the affected range and open Home > Conditional Formatting > Manage Rules. Check both the formula and the Applies to range. If the selected range begins at A2, the formula should refer to A2 as its relative cell reference; using A1 can shift the test by one row. Remove or correct an old overlapping rule if it is taking precedence.
Frequently Asked Questions
Does COUNTIF show which occurrence is the original?
The greater-than-one test flags every occurrence, including the first. To mark only later repeats, use the running-range formula in Common Mistakes. It counts from the top of the list through the current row.
Can I highlight duplicates directly with COUNTIF?
Yes. Select the cells, create a conditional formatting rule using a formula, and use the COUNTIF test with the first selected cell as its relative reference. Apply the rule to the full data range so Excel evaluates each row.
Does COUNTIF treat uppercase and lowercase as different?
No. COUNTIF is case-insensitive for text criteria. If capitalization must distinguish records, a different, case-sensitive approach is needed; the basic COUNTIF check will group those values together.
Can I find duplicates across two columns?
Yes, if a duplicate means that both column values match, use COUNTIFS with one range-and-criterion pair for each column. Decide which fields define a duplicate before applying the formula, and check the resulting rows before deleting anything.
Will the formula update when I add more data?
Only if the new rows are included in the searched range. Extend the formula and conditional formatting range, or use an Excel table and verify that new rows are included. A fixed range such as A2:A20 does not automatically include data entered in A21.
Related
- How to Enable Undo in Excel and Restore the Undo Button
- Track Changes in Excel Online: View and Review Edits
- How to Enable Transpose in Excel
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Day Countdown: Build a Live Days-Left FormulaWith a deadline date in B2, enter =B2-TODAY() in C2 to show how many calendar days remain. For…
- Lowercase Names in Excel: Formula, Fill Down, and ReplaceTo lowercase names in Excel, use the LOWER function in a helper column, then fill the formula down.…
- Excel Dependent Drop-Down List with VLOOKUP ExplainedYes—you can use a dependent drop-down list with VLOOKUP, but they do different jobs. Data Validation creates the…
- Find Greater Than in Excel: Formulas, Filters, and RulesTo find values greater than a number in Excel, use the greater-than operator > in a formula, or…
- How to Edit a Filter in Excel: Change Criteria and RangesTo edit a filter in Excel, open the drop-down arrow in a filtered column and change its selected…
- Excel Vertical Lookup: VLOOKUP, XLOOKUP, and TipsAn Excel vertical lookup searches down the first column of a table for a value, then returns related…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.