To find duplicates and count matching values in Excel, use COUNTIF: with values in A2:A8, enter =COUNTIF($A$2:$A$8,A2) beside the first value and fill down. The result is how many times that value appears in the range; any result greater than 1 identifies a duplicate.
How COUNTIF finds and counts duplicates
COUNTIF checks a range for cells that meet one condition. Its syntax is COUNTIF(range, criteria): the range is where Excel looks, and the criteria is the value or rule to match. In the example above, the fixed range $A$2:$A$8 stays the same as the formula is copied, while A2 changes to A3, A4, and so on.
Suppose A2:A8 contains Apple, Pear, Apple, Banana, Pear, Apple, and Grape. The formula returns 3 for each Apple, 2 for each Pear, and 1 for each Banana or Grape. It counts every matching occurrence, including the current cell. That means the count answers both “Is this value repeated?” and “How many times does it occur?”
- A result of 1 means the value appears once in the checked range.
- A result greater than 1 means the value is repeated.
- A result of 0 means the criterion was not found, which is useful when counting a value typed in a separate cell.
COUNTIF is available in current desktop Excel and Excel for the web. It is usually the simplest option for one matching condition. For two or more conditions, use COUNTIFS instead.
Show a duplicate label instead of a count
If you want a clear text flag, use an IF formula around COUNTIF. With the same sample data in A2:A8 and the label formula entered in B2 and filled down, use =IF(COUNTIF($A$2:$A$8,A2)>1,"Duplicate","Unique"). It displays Duplicate for every value that occurs at least twice and Unique for values that occur once. The three Apple rows and two Pear rows are marked Duplicate; Banana and Grape are marked Unique.
This approach labels all copies, including the first appearance. That is often useful for reviewing a list because every member of a repeated group is marked. If you instead want to flag only the second and later appearances, use a growing range. With values in A2:A8 and the formula in B2 filled down, enter =IF(COUNTIF($A$2:A2,A2)>1,"Duplicate","First occurrence"). The first Apple is not flagged, while its later occurrences are. The range expands one row at a time as the formula is filled down.
These formulas do not remove or change the source values. They create a result in another column, so you can filter that column, review the records, and decide what to do without losing the original data.
Count a particular value in a list
To count how often a chosen value appears, put the value in a separate cell and refer to it as the COUNTIF criterion. With the list in A2:A8 and the value you want to check in D2, enter =COUNTIF($A$2:$A$8,D2) in E2. If D2 contains Apple, E2 returns 3. Change D2 to Pear and the result becomes 2, without editing the formula.
Using a cell reference is handy for a search box, summary table, or reusable report. It also avoids having to type a text criterion inside the formula. For a range of numbers, the same pattern counts a specific number. COUNTIF can also use criteria such as greater than or less than, but text and numeric comparisons should be chosen carefully when a column mixes data types.
Highlight duplicate values on the worksheet
For a visual check without adding a helper column, use Excel’s built-in duplicate highlighting. Select the cells to check, then choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. Choose a formatting style in the dialog and select OK. Excel highlights values that repeat within the selected range.
This is quick for a one-time review, but it does not display a count for each value. To apply a custom rule, select A2:A8, then choose Home > Conditional Formatting > New Rule. Select “Use a formula to determine which cells to format,” enter =COUNTIF($A$2:$A$8,A2)>1, choose a format, and confirm. The formula checks each selected cell against the full range; the relative A2 reference adjusts for each row.
Conditional formatting changes the display, not the underlying data. A highlight may disappear if the rule’s range excludes newly added rows, so edit the rule’s range when the list grows or use an Excel Table, whose structured range can expand with new records. The interface is similar in Excel for Mac, though some dialog details can differ. In Excel for the web, Conditional Formatting is also available from the Home tab.
Count matches using more than one condition
A value can be a duplicate only within a group. For example, the same employee name might appear once in each department, so a name by itself is not enough to identify a repeated employee record. COUNTIFS tests multiple matching conditions at once.
Assume names are in A2:A8 and departments are in B2:B8. To count records with both the same name and the same department as the current row, enter =COUNTIFS($A$2:$A$8,A2,$B$2:$B$8,B2) in C2 and fill down. If A2 is Jordan and B2 is Sales, the result is the number of rows where both Jordan and Sales appear together. A result above 1 identifies a repeated name-and-department combination, even if Jordan also appears in another department.
Every criteria range in COUNTIFS must cover the same number of rows and columns. This is an important difference from casually combining separate counts: counting the name and department independently would not establish that both values occur together on the same row.
Choose the right method for your task
Different ways of finding duplicates serve different purposes. Choose based on whether you need a quick visual check, a count, a reusable report, or a reviewable flag.
- Conditional Formatting: best for quickly seeing repeated values in the original list. It highlights but does not provide a numeric count.
- COUNTIF helper column: best when you need the number of occurrences beside every row or want to filter for duplicates.
- COUNTIFS: best when a duplicate means a repeated combination, such as the same product and location.
- Remove Duplicates: best only when you have decided which records to keep and want Excel to delete repeated entries from a selected range. Make a copy first; this changes the data, unlike counting or highlighting.
- PivotTable: useful for summarizing larger lists by category and counting records, especially when you need totals grouped by several fields. It is a summary rather than a row-by-row duplicate flag.
For a growing dataset, converting the range to a Table with Insert > Table helps keep related formulas and formatting organized as rows are added. Check the formula references or conditional-formatting rule after extending a list; a fixed range such as A2:A8 does not automatically include values entered in A9.
Common reasons duplicate counts look wrong
Excel generally treats text matching without regard to letter case, so Apple and APPLE are counted as matches by COUNTIF. However, visually similar entries may not be identical. A trailing space in “Pear ”, a different punctuation mark, or a nonbreaking space copied from a web page can make two entries compare differently. If a count seems unexpectedly low, inspect the source values and clean inconsistent spacing before relying on the results.
Numbers stored as text can also behave differently from genuine numeric values in mixed data. A cell containing the text “123” may look like the number 123 but be stored differently. Use Excel’s warning indicators or the Number Format and data-cleaning tools to check the column, then standardize the data if the distinction matters.
COUNTIF criteria can include wildcard characters: an asterisk matches a sequence of characters, and a question mark matches one character. As a result, a criterion containing those characters may match more than a literal value. To search for a literal wildcard, precede it with a tilde in the criterion. Also check for blank cells: counting a blank criterion can include empty cells and cells whose formulas return an empty string, which may be mistaken for a duplicate blank.
Finally, confirm that the range includes all the rows you intend to check and that copied formulas use absolute references for the full range. A formula that shifts its range downward as it is filled may compare each row against only part of the list, producing inconsistent counts.
Frequently Asked Questions
How do I count how many times a value appears in Excel?
With the list in A2:A100 and the value to search for in D2, enter =COUNTIF($A$2:$A$100,D2) in another cell. The result is the number of matching cells in that range.
How do I highlight duplicates but keep the first occurrence unmarked?
Select the data cells, create a conditional-formatting rule using a formula, and use a growing count such as =COUNTIF($A$2:A2,A2)>1 for a selection starting at A2. It highlights only later occurrences. The formula’s starting row must match the first row of the selected range.
Does COUNTIF count matching values in another column?
Yes. Set the first argument to the column or range to check and the second to the cell containing the value. For example, a criterion in D2 can be counted in a list in A2:A100 with =COUNTIF($A$2:$A$100,D2).
How do I find duplicates based on two columns?
Use COUNTIFS with one range-and-criterion pair for each column. For names in A2:A100 and departments in B2:B100, count rows matching the current row’s name and department with =COUNTIFS($A$2:$A$100,A2,$B$2:$B$100,B2). Counts above 1 indicate a repeated combination.
Is Remove Duplicates the same as counting duplicates?
No. COUNTIF and conditional formatting identify matches while leaving the records in place. Remove Duplicates deletes repeated entries from the selected data, so copy the data or save a backup before using it if you may need the original records.
Related
- Excel Data Validation List Without a Cell Reference?
- Excel Data Validation List: Can You Select Multiple?
- Excel Data Validation List: Remove Duplicates
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Keep Format Painter On in Excel for Multiple CellsTo keep Format Painter active in Excel, select the cell or range with the formatting you want, then…
- Excel Not Responding: How Long Should You Wait?There is no fixed time after which Excel is safe to close: wait at least a few minutes,…
- Excel Text to Columns: Split at the First SpaceNo. In Excel for Microsoft 365 on Windows, Data > Text to Columns splits at every space delimiter…
- Google Sheets Data Validation: How to Show Help TextYes. In Google Sheets in a web browser, select the validated cell or range, open Data > Data…
- Upper Excel Shortcut: How to Capitalize TextExcel has no built-in keyboard shortcut that turns the selected cell’s text into uppercase. With the text maria…
- Excel Keyboard Shortcuts to Switch Between Worksheet TabsIn Excel for Microsoft 365 on Windows, press Ctrl+Page Down to move to the next sheet tab, or…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.