← ACARA Digital Technologies Years 7–8

Creating ID codes with a formula

Builds a unique ID code like P01 for each row. Copy it down, paste as values only so the codes stay, then delete the name column.

Part of: ACARA Digital Technologies Years 7–8 — Digital Footprints and Data Minimisation — Module 45

Understanding Creating ID codes with a formula in ACARA Digital Technologies Years 7–8

What it does: The & symbol joins the text "P" to the formatted row number, so row 2 gives P01. TEXT with the format "00" adds a leading zero, so 5 becomes 05 and codes line up neatly. ROW() gives the row number. Data starts in row 2, so ROW()-1 gives 1, 2, 3 and so on, one for each person. Copying the formula down is quick. ROW() changes for each row, so each row gets its own unique code. Pasting as values only turns the formulas into plain text. Then the codes stay put and do not change or break when other data is changed or deleted.

Common mistakes: Text and numbers are joined with &, not +. Using + causes a #VALUE! error. It should be ="P"&TEXT(ROW()-1,"00"). The codes must be pasted as values only so they become fixed text. If they stay as formulas, they can change when rows are sorted or deleted. It should be 'Paste B2:B9 as values only'.

Class: _____________________  ·  Name: _____________________  ·  Date: _________

Free printable worksheet with full answer key — or add this module to a class and your students practise it interactively.

🖨 Print worksheet + answer key Add Creating ID codes with a formula to a class →

This module's key terms are also in a printable crossword (Modules 752–770) and word search (Modules 696–770).

Take this worksheet further — free with Cloodit

Each concept is reinforced from every angle — learned, then applied, then tested.

Learning
Duo
Which formula builds the ID code P01 for row 2?
="P"+TEXT(ROW()-1,"00")="P"&TEXT(ROW()-1,"00")
Matches
=P&TEXT(ROW()-1,"00")
↓
The letter P is not in quotes, so the spreadsheet looks for a name called P and shows an error. Fix: ="P"&TEXT(ROW()-1,"00")
Applying
Meanings
Term
="P"&TEXT(ROW()-1,"00")
Meaning
Builds a unique ID code like P01 for each row. Copy it down, paste as values only so the codes stay, then delete the name column.
Practice
="P0"&COUNTA($A$2:A2)
Testing
Gaps
="P"&(ROW()-1,"00")
Check Answer
Spots
2B1: Student ID
3B2: ="P"+TEXT(ROW()-1,"00")

Cloodit turns curriculum-aligned coding content into journeys students work through one biome at a time.

The 'Wolverine' badge — Fearless Coder, one of Cloodit's 106 collectible badges
Students earn collectable badges

Over 100 of them, across seven natural biomes.

Study Buddy
Students build a real study resource

Turns their work into revision material ready for Flash Cards and Exam study.

Diamond Dash — one of Cloodit's live, competitive Sprint games
Students lock in with Sprints

Live, competitive practice for the start of the lesson or a teacher-led lesson finisher.

A Cloodit certificate of achievement, ready to personalise and print
Custom printable certificates

Honour your top performers, or encourage a student who's trying hard — ready to print in seconds.

Add to class →

Quick Questions

1. Which formula builds the ID code P01 for row 2?
A. ="P"&TEXT(ROW()-1,"00") ✓
B. ="P"+TEXT(ROW()-1,"00")
The & symbol joins text and numbers together in a spreadsheet. The + symbol is for adding numbers, so it gives an error with the text "P".
2. Why does the formula use ROW()-1?
A. Row 1 holds the headings, so subtracting 1 makes the first data row number 1 ✓
B. It hides the heading row so that it cannot be seen
ROW() gives the row number. Data starts in row 2, so ROW()-1 gives 1, 2, 3 and so on, one for each person.
3. What does TEXT(ROW()-1,"00") do to the number 5?
A. It turns it into 500, so the number is bigger
B. It turns it into 05, so every code has two digits ✓
The format "00" pads the number with a leading zero. This keeps codes tidy and the same length: P05, P06, P10.
4. You wrote the formula in the first row. What should you do next to get a code for every row?
A. Retype the formula by hand in every row
B. Copy the formula down the column ✓
Copying the formula down is quick. ROW() changes for each row, so each row gets its own unique code.
5. Before deleting the name column, what must you do to the ID column?
A. Paste as values only, so the codes become fixed text ✓
B. Nothing, because the formulas will work without the names
Pasting as values only turns the formulas into plain text. Then the codes stay put and do not change or break when other data is changed or deleted.

Match the Pairs

="P"&TEXT(ROW()-1,"00") → Builds an ID code like P01 from the row number so each row gets its own code
="S"&ROW() → Joins the letter S to the current row number, giving codes like S2, S3 and S4
Join the letter P and a two-digit number into one text code, using the & symbol → ="P"&TEXT(ROW()-1,"00")
Show the number 5 as the two-digit text 05 so every code is the same length → =TEXT(5,"00")
=P&TEXT(ROW()-1,"00") → The letter P is not in quotes, so the spreadsheet looks for a name called P and shows an error. Fix: ="P"&TEXT(ROW()-1,"00")
Copy the ID codes, delete the name column, and keep the formulas as they are → The codes are still formulas that depend on the sheet, so they can change or break when columns are deleted. Paste as values only first, then delete the names.

Key Term

="P"&TEXT(ROW()-1,"00")
Builds a unique ID code like P01 for each row. Copy it down, paste as values only so the codes stay, then delete the name column.

Fill in the Blanks

1. ="P"&TEXT(ROW()-1,"00")
Hint: Type the function name that turns a number into text with a set format.
2. ="P"&TEXT(ROW()-1,"00")
Hint: Type the operator that joins the letter and the number together.
3. ="P"&TEXT(ROW()-1,"00")
Hint: Type the function that gives the row number of the cell the formula is in.
4. ="P"&TEXT(ROW()-1,"00")
Hint: Type the number to subtract so the header row is not counted and the first student gets 01.
5. ="P"&TEXT(ROW()-1,"00")
Hint: Type the format characters inside the quotes that always show two digits, adding a leading zero when needed.

Spot the Error

Question 1
A A1: Name
B B1: Student ID
C B2: ="P"+TEXT(ROW()-1,"00")
D Copy B2 down to B9
Line C — Text and numbers are joined with &, not +. Using + causes a #VALUE! error. It should be ="P"&TEXT(ROW()-1,"00").
Question 2
A A2:A9 hold the names
B B2: ="P"&TEXT(ROW()-1,"00")
C Copy B2 down to B9
D Paste B2:B9 as formulas
E Delete column A
Line D — The codes must be pasted as values only so they become fixed text. If they stay as formulas, they can change when rows are sorted or deleted. It should be 'Paste B2:B9 as values only'.
Question 3
A A1: Name
B B1: ID
C B2: ="P"&TEXT(ROW()-1,00)
D Copy B2 down to B9
Line C — The format given to TEXT must be in quotes, because it is text. It should be TEXT(ROW()-1,"00").
Question 4
A Row 1 holds the headings
B B2: ="P"&TEXT(ROW(),"00")
C Row 2 is the first student
Line B — The first student is in row 2, so ROW() gives 2 and the first code would be P02. Subtracting 1 for the heading row gives P01. It should be ROW()-1.
Question 5
A B1: ID
B B2: ="P"&TEXT(ROW()-1,"00")
C Drag fill handle across B2:E2
Line C — Each student row needs its own code, so the formula is copied down the column, not across the row. It should be 'Drag fill handle down B2:B9'.

Student with a class code? Join your class →