Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to generate a unique ID in Excel and Google Sheets
Excel & Google Sheets
="CUST-"&TEXT(ROW()-1,"0000")Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
Step by step
- 1For a sequential ID with a prefix, put ="CUST-"&TEXT(ROW()-1,"0000") in the first data row; it yields CUST-0001, CUST-0002 and so on.
- 2For a composite key from existing columns, join them with a separator: =A2&"-"&B2&"-"&TEXT(C2,"YYYYMMDD").
- 3For a random ID, use =DEC2HEX(RANDBETWEEN(0,4294967295),8) to get an 8-character hex code.
- 4Paste-special as Values once the IDs are generated so volatile functions (ROW, RANDBETWEEN) do not renumber existing records later.
- 5Add a duplicate check (see tips) before you rely on any ID as a key.
Tips
- Composite keys are the most robust — built from data that is already unique such as email plus date, they never collide and need no separate counter.
- Flag accidental duplicates with =IF(COUNTIF($A$2:$A$1000,A2)>1,"DUPLICATE","") next to the ID column.
- TEXT(...,"0000") controls the zero-padding width — use "000000" for six digits once you expect more than 9999 records.
- RANDBETWEEN is volatile and reshuffles on every edit — always convert random IDs to static values immediately after generating them.
- In Google Sheets you can also build a running sequence with =SEQUENCE(100) and concatenate a prefix.
Working on a sheet you inherited? Run the Auditor on the whole file first — it flags every #REF!, #N/A, broken column pattern, and inconsistent formula in seconds, free, no signup.
Frequently asked
How do I make an auto-incrementing ID?
Base it on the row number: ="ID-"&TEXT(ROW()-1,"0000"). It increments automatically as you add rows. Convert to static values if you need the numbers to stop shifting when rows are deleted.
How can I be sure the IDs are actually unique?
Add a COUNTIF check: =IF(COUNTIF($A$2:$A$1000,A2)>1,"DUPLICATE","") beside the ID column highlights any collision so you can resolve it.
How do I create a random unique ID?
Use =DEC2HEX(RANDBETWEEN(0,4294967295),8) for an 8-character hex code, then paste-special as values. For very large datasets, combine it with a timestamp to make collisions effectively impossible.
Do Excel and Google Sheets differ here?
The core formulas are identical. Google Sheets and Excel 365 both offer SEQUENCE for quick numeric runs.