Yes. In Excel, COUNTIF can find duplicates by counting how often each value appears: with values in A2:A7, enter =COUNTIF($A$2:$A$7,A2)>1 beside the first value and fill down. The formula returns TRUE for every value appearing more than once and FALSE for a one-off value.
What COUNTIF Finds
COUNTIF checks a range for cells matching one criterion and returns the number of matches. To find duplicates, the range is the list you want to inspect, and the criterion is the value in the current row. Comparing the count with 1 turns that count into a simple duplicate flag: a value is repeated if its count is greater than 1.
For example, suppose A2:A7 contains Maple, Cedar, Maple, Birch, Cedar, and Elm. The formula returns TRUE for both Maple cells and both Cedar cells, because each appears twice. It returns FALSE for Birch and Elm, which each appear once. This flags all members of a repeated group, including the first occurrence; it does not mean that every flagged row is a later copy.
The dollar signs in $A$2:$A$7 keep the list range fixed when you copy the formula downward. The reference A2 has no dollar signs, so it changes to A3, A4, and so on. This combination of a fixed range and a moving criterion is the usual pattern for checking a list row by row.
Flag Every Value That Appears More Than Once
Use a fixed range that covers the data, then fill the formula down alongside the list. For the example above, with the six values in A2:A7 and the check formula entered in B2, use =COUNTIF($A$2:$A$7,A2)>1. B2 returns TRUE because Maple occurs twice; copied down, the results are TRUE, TRUE, TRUE, TRUE, FALSE, FALSE.
It is often useful to keep the flag in a separate column rather than overwriting the original values. You can then filter the flag column for TRUE to review the repeated entries, or use it to guide a cleanup. If the list may grow, extend the range far enough to include new rows or use an Excel Table so the data range can expand as rows are added. A range that stops too early will not count duplicates outside its boundaries.
To return the number of occurrences rather than TRUE or FALSE, remove the comparison with 1. With Maple, Cedar, Maple, Birch, Cedar, and Elm in A2:A7, enter =COUNTIF($A$2:$A$7,A2) beside the list. For the first Maple, it returns 2; for Birch, it returns 1. Each duplicate receives the same count, which can be more informative when a value appears three or more times.
Mark Only the Later Occurrences
Sometimes the first instance should remain unmarked and only subsequent repeats should be identified. For this, make the COUNTIF range start at the first data row but end at the current row. With the same example in A2:A7, enter =COUNTIF($A$2:A2,A2)>1 in the check column and fill down. The formula returns FALSE for the first Maple and first Cedar, TRUE for the second Maple and second Cedar, and FALSE for Birch and Elm.
This works because each row counts matches only from the beginning of the list through that row. The first time a value appears, its running count is 1. When it appears again, the count becomes 2, so the comparison is TRUE. This pattern is handy for identifying which rows are extra copies while preserving the earliest entry.
Be aware that the result depends on row order. If you sort the list, a different occurrence may become the first one. If the earliest record matters—for example, because it has the original date or notes—decide whether to sort before using this method.
Highlight Duplicates with Conditional Formatting
For a visual check, conditional formatting can color the matching cells without adding a helper column. In Excel for Microsoft 365 on Windows, select A2:A7, then choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. With A2:A7 as the selected range and A2 as its top-left cell, enter =COUNTIF($A$2:$A$7,A2)>1, choose a format, and confirm the rule. The rule evaluates TRUE for Maple and Cedar cells in the example, so those cells receive the chosen formatting.
The reference in the formula must match the top-left cell of the selected range. If you select A3:A20 instead, use A3 as the relative reference while adjusting the fixed range to the list you intend to check. Selecting a larger area or using an incorrect starting row can shift the checks and highlight the wrong cells. Excel for Mac also offers conditional formatting from the Home tab; the rule dialog may look different, but the formula logic is the same.
Conditional formatting only changes how cells look; it does not remove or alter the underlying values. That makes it useful for review, but the colors may need to be refreshed or rules adjusted if the range changes. A helper column is easier to filter and inspect when you need to make decisions about many repeated records.
Find Duplicates Based on Multiple Columns
A value can be a duplicate only when several fields match together. For example, a person’s name may appear in different departments, so checking the name alone could flag legitimate records. COUNTIFS checks multiple criteria at once. With names in A2:A6 and departments in B2:B6—Ana/Sales, Ben/Sales, Ana/Sales, Ana/Support, and Ben/Sales—enter =COUNTIFS($A$2:$A$6,A2,$B$2:$B$6,B2)>1 beside the rows. It returns TRUE for both Ana/Sales rows and both Ben/Sales rows, but FALSE for Ana/Support.
Each criteria range in COUNTIFS must have the same dimensions, and each criterion is paired with the range it checks. In the example, the first pair checks names and the second pair checks departments. Add another range-and-criterion pair when a duplicate definition also depends on a date, code, or other field. Be precise about what makes records equivalent: including a date, for instance, means that two otherwise identical records on different dates will not count as duplicates.
Blanks, Text, and Other Common Surprises
A blank cell can be counted as a match for another blank when the criterion refers to an empty cell. That can make an ordinary duplicate formula mark empty rows as repeats, especially when the selected range contains unused cells. If A2:A7 contains Maple, Cedar, Maple, a blank, Cedar, and Elm, enter =AND(A2<>"",COUNTIF($A$2:$A$7,A2)>1) beside the values and fill down. The blank row returns FALSE because the first test rejects an empty current cell; the Maple and Cedar rows return TRUE.
COUNTIF is not case-sensitive. It treats Maple and MAPLE as matching values, so it is not suitable when letter case is part of the identity. If case must matter, with values in A2:A7 and the value to check in A2, use =SUMPRODUCT(--EXACT($A$2:$A$7,A2))>1. For a list containing Maple, maple, Maple, Cedar, Cedar, and Elm, the formula returns TRUE for a Maple entry because that exact capitalization occurs twice; it would not count the lowercase maple as a match.
COUNTIF also recognizes the wildcard characters * and ? in text criteria: an asterisk can match any number of characters, and a question mark can match one character. This is useful for searching patterns, but it can surprise you if the cell being checked contains one of those characters and you intended a literal match. The tilde (~) is used to escape wildcard characters in criteria. Consider whether your values contain wildcard symbols before relying on COUNTIF for exact text matching.
Another source of confusion is inconsistent data. Extra spaces, different punctuation, and numbers stored as text can make values that look alike behave differently. If apparently identical entries are not being counted together, inspect the source values and their formatting rather than assuming the COUNTIF logic is wrong. Cleaning or standardizing the values before checking can make the results more reliable.
Check the Result and Choose a Cleanup Method
A TRUE flag means the value matches another item in the formula’s comparison range; it does not establish that either record should be deleted. Review relevant fields before removing rows. Repeated values may be valid—for example, multiple customers may share a surname, or separate transactions may have the same amount. Define what counts as a duplicate for the task before taking action.
- Filter a helper column for TRUE to review all repeated values or records.
- Use the running-count version if you only want to identify later occurrences.
- Use COUNTIFS when a duplicate is defined by a combination of columns.
- Keep a copy of the source data before deleting rows or changing records.
Excel’s Remove Duplicates command is a separate cleanup tool, not a COUNTIF formula. On Windows, select the data and choose Data > Remove Duplicates, then choose which columns Excel should use to decide whether rows match. The command removes duplicate rows from the selected data and keeps one instance. Make a copy first if you may need to restore or compare the original; conditional formatting and formulas let you inspect matches without deleting them.
Frequently Asked Questions
Does COUNTIF find all copies or only the extra ones?
With a fixed full-list range and a criterion from the current row, COUNTIF flags every occurrence of a repeated value, including its first appearance. Use a range that grows from the first row to the current row if you want to flag only later occurrences.
Can COUNTIF highlight duplicates automatically?
Yes. Create a formula-based conditional formatting rule using the duplicate test and apply it to the list. It highlights matching cells but does not delete or change their contents.
How do I check duplicates in two columns?
Use COUNTIFS with one criteria range for each column that defines a match. For example, check both name and department so a shared name in a different department is not treated as the same record.
Why does COUNTIF mark blank cells as duplicates?
A blank current cell can match other blank cells in the checked range. Add a test that the current cell is not empty before checking its count, or exclude unused rows from the range.
Does COUNTIF distinguish uppercase and lowercase?
No. COUNTIF is case-insensitive, so Maple and MAPLE count as matches. For a case-sensitive comparison, use a formula based on EXACT instead.
Will Remove Duplicates keep the first row?
Excel’s command removes duplicate rows according to the columns you select and retains one instance. It is a data-changing operation, so make a copy first and verify the selected columns define a duplicate correctly.
Related
- Compare Lists in Excel: Find Matches and Differences
- Combine Two Cells in Excel and Keep Both Text
- Combine in Excel: Text, Cells, and Data
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel MID Function: Extract Text from the Middle of a CellExcel’s MID function extracts a chosen number of characters from the middle of a text string. Its syntax…
- Excel IF SUM: How to Combine IF, SUMIF, and SUMIFS FormulasTo test a total and return one result or another, combine IF with SUM: for example, with amounts…
- Excel Insert Row Automatically Add Formula?Yes—Excel can automatically add a formula when you insert a row, most reliably when the data is an…
- Excel Table Query: How to Filter, Transform, and Refresh DataIn Excel for Microsoft 365 on Windows, the usual way to query an Excel table is to select…
- Excel Difference Between Dates: Formulas and ExamplesTo find the number of days between two dates in Excel, subtract the earlier date from the later…
- Excel Count Nonblank Cells: Formulas and ExamplesTo count nonblank cells in Excel, use =COUNTA(A2:A20), replacing the range with the cells you want to check.…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.