How to Create a Unique ID in Excel With a Formula

To create a unique ID in Excel, you join a short text prefix to a number that never repeats, such as EMP-0001 or SAL-003. Excel has no "make an ID" button, but a one-line formula with &, TEXT and sometimes LEFT or COUNTIF does the job. This guide shows the common ID styles, how to keep them from changing, and how to prove that no ID appears twice.

What makes a good unique ID?#

A good ID is unique, never changes once assigned, and is easy to read and type. In practice that means:

• A short prefix that says what the record is, such as EMP, INV or SAL.
• A padded number, such as 0001, so IDs sort correctly and have the same length.
• No meaning that might change. An ID built from a surname breaks when the person changes their name.

Here are the formulas in this guide:

How to create a simple ID with a prefix and number#

Join the prefix and a padded number. With headers in row 1, the first record is in row 2, so ROW()-1 gives 1, 2, 3 down the list:

="EMP-"&TEXT(ROW()-1,"0000")

• ROW() returns the current row number.
• TEXT(...,"0000") pads it to four digits, so 1 becomes 0001.
• & joins the prefix and the number into EMP-0001.

Fill it down (Windows: Ctrl+D; Mac: ⌘D) for EMP-0002, EMP-0003 and so on. The TEXT function guide and the list of TEXT format codes explain "0000". Other ways to number rows, including SEQUENCE, are in how to auto number rows.

To make a whole block of IDs at once in Microsoft 365 or Excel 2021, combine TEXT with SEQUENCE:

="EMP-"&TEXT(SEQUENCE(50),"0000")

This spills EMP-0001 to EMP-0050 into the 50 cells below. Because one formula produces every ID, paste the results as values once they are assigned, so they no longer depend on it.

You can also type EMP-0001 in the first cell and drag the fill handle down. Excel usually continues the number at the end of the text, and the result is fixed text from the start.

How to create IDs by category#

To number each department separately, count how many times the department has appeared so far. In C2:

=UPPER(LEFT(B2,3))&"-"&TEXT(COUNTIF($B$2:B2,B2),"000")

Fill it down. With this data, the IDs are:

How it works:

• LEFT(B2,3) takes the first three letters of the department. The LEFT function guide shows more examples. "HR" has only two letters, so LEFT returns HR.
• UPPER makes the prefix capitals.
• COUNTIF($B$2:B2,B2) counts matches from row 2 down to the current row. The first part is locked with $, and the second part grows as you fill down. In row 6 the range is B2:B6, which holds three Sales rows, so the count is 3.

Watch for departments that share the first three letters, such as "Marketing" and "Marine". Use a lookup table of fixed codes instead if that happens.

How to create date-based IDs#

Invoices and tickets often include the date. With the date in C2:

="INV-"&TEXT(C2,"yymmdd")&"-"&TEXT(COUNTIF($C$2:C2,C2),"00")

For the second invoice on 14 March 2025, this returns INV-250314-02. TEXT(C2,"yymmdd") turns the date into 250314, and the COUNTIF numbers invoices within the same day. The yymmdd order keeps IDs in date order when sorted as text.

How to keep leading zeros in IDs#

Leading zeros disappear when an ID is stored as a number: 0007 becomes 7. You have two options:

• Formula IDs: TEXT returns text, so TEXT(7,"0000") keeps 0007.
• Number IDs: keep the value 7 and apply a custom number format such as "EMP-"0000. The cell shows EMP-0007, but lookups must use the number 7.

The guide to leading zeros in Excel covers both, plus importing data without losing them.

How to stop IDs from changing#

An ID must stay with its record forever. Formula IDs can break this rule:

• ROW() changes when you sort the list or delete a row above.
• COUNTIF($B$2:B2,B2) changes if you sort, or insert a record in the middle.

So once IDs are assigned, freeze them. Select the ID column, copy it, and use Paste Special > Values (Windows: Ctrl+Alt+V, then V; Mac: ⌃⌘V). For new records, add them at the bottom and copy the formula down to the new rows only, then paste those as values too.

In an Excel table (Ctrl+T; Mac: ⌘T), a formula column fills itself for new rows, which is handy. Remember to paste the new IDs as values after adding records.

How to check that every ID is unique#

Never assume IDs are unique; check. Next to the first ID, type:

=COUNTIF($C$2:$C$100,C2)>1

Fill it down. TRUE means the ID appears more than once. For a single answer, =SUMPRODUCT(--(COUNTIF(C2:C6,C2:C6)>1)) counts the duplicate rows; 0 means all is well.

You can also highlight repeats with Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. For IDs built from names, such as emails, duplicates are common, so the check matters even more; see how to create an email address from a name. More joining techniques are in how to combine text in Excel.

Practise it#

Every ID formula in this guide is joined text plus a formatted number. Practise joining text with &, CONCAT and TEXTJOIN, then practise TEXT and VALUE to control zeros and formats: you type each formula and it checks it instantly. Or build the department IDs in the exercise on this page first.