Excel Challenge 17 : Generate Sequential IDs

What You Will Learn

Five team members in A2:A6. Each needs two IDs: a simple one (EMP-1, EMP-2...) and a padded one with leading zeros (EMP-001, EMP-002...). The formula uses the row number as the base, so the sequence is automatic and never skips.

Simple ID (column C):

="EMP-" & ROW()-1

ROW() returns the current row number. Since data starts on row 2, subtracting 1 makes the first ID equal to 1 instead of 2. The & symbol joins the text prefix with the number.

Padded ID (column D):

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

TEXT(..., "000") formats the number to always show three digits. 1 becomes "001", 2 becomes "002", and so on. The padding keeps IDs aligned when sorted alphabetically — "EMP-010" comes after "EMP-009" instead of appearing right after "EMP-001".

ROW() gives the number. TEXT adds leading zeros. & joins the prefix.

The audit formulas:

B9: =ROWS(A2:A6) — counts the number of rows, which equals the last ID number issued (since the sequence starts at 1).
B10: =COUNTA(A2:A6) — counts total personnel records.

Alternative: SEQUENCE for Bulk Generation

In Excel 365, you can generate a whole block of padded IDs at once with SEQUENCE:

="ID-" & TEXT(SEQUENCE(5), "000")

This spills "ID-001" through "ID-005" vertically in one formula. It is useful for blank templates or auto-generating a roster, but the row-based approach is more practical when adding new employees over time.

The offset is the detail that trips people up. If the data started on row 2 and you forget to subtract 1, the first person gets ID EMP-2 instead of EMP-1. Adjust the offset whenever the data starts on a different row.

Also note that the simple ID (EMP-1, EMP-2, EMP-10) sorts alphabetically differently than the padded version (EMP-001, EMP-002, EMP-010). Without padding, EMP-10 comes right after EMP-1 in an alphabetical sort because "10" starts with "1". The padded version sorts correctly because "010" is greater than "009" and less than "011". That is the main reason padded IDs exist — not just appearance, but correct sorting.