Excel: Assign Unique Numbers to Duplicate Values in Rows

Formulas & functions · Updated November 19, 2026

Yes. In Excel for Microsoft 365 on Windows, you can number each repeated value by its occurrence with a running COUNTIF formula. If instead you want every copy of a value to share one group ID, use a different formula; the right choice depends on what you mean by “unique number.”

What a number for duplicates can mean

There are two common numbering tasks. In an occurrence number list, the first time a value appears it gets 1, its next appearance gets 2, and so on. For example, entries “Oak,” “Pine,” “Oak” receive 1, 1, 2. Each repeated entry can then be identified by its value and occurrence number together.

In a group-ID list, all identical values receive the same number: “Oak,” “Pine,” “Oak” might receive 1, 2, 1. That number identifies the value’s group, not an individual row. Neither approach is the same as giving every row a different, permanent record ID. A formula-generated occurrence number can change when you insert or sort rows.

Excel’s Remove Duplicates command is different again: it removes repeated records from a selected range. It does not assign numbers to the copies. Decide whether you need a count of each occurrence, a shared group label, or a one-time ID before choosing a method.

Number each occurrence with COUNTIF

Suppose the values to check are in A2:A20, with a heading in A1. Put the occurrence formula in B2, then fill it down beside the list. The expanding range starts at A2 and ends at the current row, so each formula counts how many times that value has appeared so far.

With values in A2:A20 and the first result in B2, enter: =COUNTIF($A$2:A2,A2)

Copy B2 down through B20 using the fill handle or copy and paste. The dollar signs keep the starting cell fixed while the ending cell expands. If A2 contains “Oak,” the first result is 1. If “Oak” appears again in A5, the formula in B5 counts the matching entries from A2 through A5 and returns 2. A third “Oak” returns 3.

This is a useful numbering scheme when the combination of the original value and its occurrence number distinguishes repeated rows. For instance, a list of product codes might show code X17 with occurrence numbers 1, 2, and 3. The numbers are meaningful within that list, but are not permanent identifiers: changing row order changes which copy gets which occurrence number.

Skip blank cells and handle wildcard characters

If the source column contains empty rows, the basic formula counts blank cells as matches. To leave the corresponding result blank instead, use an IF check. With source values in A2:A20 and the formula starting in B2, enter:

=IF(A2="","",COUNTIF($A$2:A2,A2))

Fill it down as before. The check treats a cell that evaluates to an empty string as blank too, so a formula in column A that returns "" will not receive a number. If a blank has a meaningful role in your data, do not skip it automatically; choose a separate label or rule for those rows.

There is a less obvious COUNTIF limitation: its criteria interpret the asterisk and question mark as wildcard characters. If your values may contain those characters literally, COUNTIF can match more than the exact text. For exact comparisons in a simple list, an alternative is SUMPRODUCT. With values in A2:A20 and the result formula in B2, use:

=SUMPRODUCT(--($A$2:A2=A2))

Fill down. This counts entries equal to the current cell in the expanding range without treating asterisk or question mark as wildcards. It is not case-sensitive, and, like other formulas, it can return an error if the source data contains errors. For ordinary names and codes without wildcard characters, COUNTIF is usually simpler and easier to maintain.

Give every duplicate group the same ID

For a shared ID, use the distinct values in the list to assign a number according to the order in which each value first appears. Excel 2021 and Microsoft 365 include the UNIQUE function, which returns the distinct values from a range. MATCH then finds the current value’s position in that distinct list.

With the complete source list in A2:A20 and a group-ID formula in B2, enter:

=MATCH(A2,UNIQUE($A$2:$A$20),0)

Fill the formula down. If A2:A6 contains “Oak,” “Pine,” “Oak,” “Maple,” “Pine,” the IDs are 1, 2, 1, 3, 2. The 0 requests an exact match. The numbers follow first appearance, so the first distinct value gets 1, the second gets 2, and so on.

This formula refers to the full list, rather than just the rows above the current one. If you add data beyond row 20, extend the range in the formula or use an Excel table so the source range can expand with the data. A blank in the source range may also be treated as a distinct value; decide whether blanks should receive an ID and adjust the data or formula accordingly.

Choose a formula based on your goal

Use occurrence numbers when repeated rows need to be distinguished in sequence. Use a shared group ID when you want to group, summarize, or filter all copies of a value together. If you want to label only whether a value is repeated, a number may be unnecessary: a duplicate check can return a simple yes/no result, while Conditional Formatting can highlight repeated cells visually.

  • Occurrence numbering: the same value receives 1, 2, 3 as it appears. Useful for comparing repeated records or identifying a particular copy.
  • Shared group ID: every copy receives the same number. Useful for grouping rows or creating a compact category column.
  • Visual duplicate check: cells are marked but not assigned IDs. Useful when you only need to inspect or review repeats.
  • Permanent row ID: every record receives a stable identifier independent of sorting. A formula based on row position is not a dependable permanent ID; store fixed IDs or use a suitable data-generation process instead.

Also decide what counts as the same value. COUNTIF and the equality comparison in the SUMPRODUCT example are not case-sensitive, so “oak” and “Oak” are generally treated as matches. Extra spaces can make values look alike while preventing a match. If records should be compared using more than one column—for example, a customer name and date—build the matching rule around both fields rather than numbering names alone.

Keep the numbers reliable when data changes

Formulas recalculate as the source list changes. Inserting a new row in the middle can change occurrence numbers below it. Sorting the source data changes the order of occurrences, and therefore which copy receives 1 or 2. The shared-ID formula also assigns IDs in first-appearance order, so sorting can change the numeric labels even if the groups themselves remain the same.

If the numbers are only needed for a report or a temporary check, recalculating them is often desirable. If you need to preserve the displayed numbers as a snapshot, copy the results and use Paste Special > Values. This replaces formulas with their current results; keep a copy of the formulas or original workbook if you may need to recalculate later.

For recurring lists, consider turning the range into an Excel table with Insert > Table. Tables expand when you add rows and can make formulas easier to fill consistently. Still check the formula’s referenced range and the resulting IDs when new records arrive, especially if rows are added outside the table or the list is sorted.

Common problems and checks

  • Every row shows 1: confirm the formula’s ending reference moves as you fill down. In the first formula, the range should be $A$2:A2, not $A$2:$A$2 in every row.
  • Similar-looking entries get different numbers: check for leading or trailing spaces, different punctuation, or inconsistent data types. TRIM can remove ordinary extra spaces, but review the data before changing it.
  • Different capitalization is counted together: the examples are not case-sensitive. If case must distinguish values, use a case-sensitive comparison approach rather than assuming COUNTIF separates them.
  • Wildcard-containing text is counted unexpectedly: COUNTIF treats * and ? specially in criteria. Use the SUMPRODUCT comparison shown above when literal matching is needed.
  • The group formula returns an error: check that the current cell’s value is included in the UNIQUE range and that the source does not contain an error value. Also confirm that the Excel version supports UNIQUE.

One useful check is to filter the result column for a particular source value and confirm that occurrence numbers rise in order, or that a shared ID stays constant. Test a few edge cases—blank cells, repeated text, and any special characters used in your data—before relying on the results in a larger workflow.

Frequently Asked Questions

Can Excel automatically number duplicate values as I add rows?
Yes, formulas recalculate when the data changes. Put the formula in an adjacent column and use an Excel table to help carry formulas into added table rows. The numbers can still change when data is sorted or inserted, so they are not permanent IDs.

How do I number the first duplicate as 1 and the next as 2?
With the values in A2:A20, enter =COUNTIF($A$2:A2,A2) in B2 and fill down. It returns the running occurrence count for the value in each row.

How do I give identical values the same number?
In Excel 2021 or Microsoft 365, use =MATCH(A2,UNIQUE($A$2:$A$20),0) in B2, with the source list in A2:A20, then fill down. Each distinct value gets one ID based on its first appearance.

Does Remove Duplicates assign numbers?
No. Data > Remove Duplicates deletes repeated entries from the selected range, keeping one copy according to the selected columns. Make a copy first if you need to retain every row; use a formula when you want to label duplicates without deleting data.

Can I make the numbering case-sensitive?
Not with the basic COUNTIF formula or the SUMPRODUCT equality example; they treat text matches without regard to case. If “Oak” and “oak” must get different results, use a formula designed for case-sensitive comparison and test it against the full set of values.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.