Excel Challenge 15: Generate Sequential IDs

Challenge Scenario

When onboarding a new batch of employees, HR departments need to assign unique, standardized employee identification codes (such as "EMP-1", "EMP-2", etc.) without manually typing numbers one by one.

Typing ID codes manually often leads to typos, skipped numbers, or duplicates. By using Excel's built-in ROW() function combined with the text join operator (&), you can create sequential IDs that automatically adjust to each row.

As an HR Operations Specialist, your tasks are:

Generated Employee ID (Column C): Create sequential badge numbers in the format "EMP-N" using ="EMP-" & (ROW()-1).
Total Staff (Cell B8): Count total personnel listed using COUNTA.
Last Assigned ID (Cell C8): Reference the final generated ID from cell C5.
First Assigned ID (Cell D8): Reference the starting generated ID from cell C2.

Your Sheet Layout

Here is an overview of the roster sheet structure:

Employee Roster (Columns A & B): Lists incoming staff names and their respective departments (Engineering, Sales, Logistics, Security).
ID Generation Zone (Column C): Where your formula dynamically constructs the employee badge code.
Batch Summary Audit (Rows 7 & 8): Summary row containing staff headcounts and boundary checks for the first and last generated badge numbers.

Cell Range	Column Name	Section	Formula / Approach	What It Does
`A2:B5`	Staff Member, Department	HR Roster	Incoming staff personnel records	Read-only staff list to be assigned company IDs.
`C2:C5`	Generated Employee ID	Key Generation	`="EMP-" & (ROW()-1)`	Generates standard badge codes based on the current spreadsheet row.
`B8`	Total Staff	Audit Summary	`=COUNTA(A2:A5)`	Counts total active employee records (4).
`C8`	Last Assigned ID	Audit Summary	`=C5`	Direct cell reference to the highest badge ID assigned ("EMP-4").
`D8`	First Assigned ID	Audit Summary	`=C2`	Direct cell reference to the starting badge ID assigned ("EMP-1").

Challenge Objectives

Complete the objectives below in the live spreadsheet editor. Each objective card will turn green once your formula evaluates successfully.

Objective #1

Cell: C2-C5

In Column C (C2:C5), generate sequential IDs in "EMP-N" format using ="EMP-" & (ROW()-1).

Objective #2

Cell: B8

In cell B8, count total staff using COUNTA(A2:A5).

Objective #3

Cell: C8

In cell C8, reference the last generated ID in cell C5 (=C5).

Objective #4

Cell: D8

In cell D8, reference the first generated ID in cell C2 (=C2).

Solution & Formula Breakdown

Let's understand how Excel calculates dynamic row numbers and joins text:

1. Dynamic ID Generation with ROW() (C2:C5)

="EMP-" & (ROW()-1)

Let's break down how this formula works:

ROW() Function: Returns the row number of the current cell. In cell C2, ROW() returns 2.
Why -1? Because row 1 contains table headers, subtracting 1 aligns Row 2 with employee #1 (2 - 1 = 1).
& Concatenation: Joins the prefix string "EMP-" with the number, yielding "EMP-1".
Row 2 (Alice Green): ="EMP-" & (2 - 1) → "EMP-1"
Row 3 (Bob Vance): ="EMP-" & (3 - 1) → "EMP-2"
Row 4 (Charlie Day): ="EMP-" & (4 - 1) → "EMP-3"
Row 5 (Diana Prince): ="EMP-" & (5 - 1) → "EMP-4"

2. Summary Auditing & Direct References (B8, C8, D8)

Total Staff (Cell B8): =COUNTA(A2:A5) counts all non-empty name cells, returning 4.
Last Assigned ID (Cell C8): =C5 pulls the value from the last employee row, returning "EMP-4".
First Assigned ID (Cell D8): =C2 pulls the value from the first employee row, returning "EMP-1".

Using ROW() is much more powerful than typing numbers manually. If you insert new rows in between, the formula automatically updates every employee's sequential number instantly!