Creating ID codes with a formula

Tip: Choose Save as PDF in the print dialog to download. Use A4 paper size.

Creating ID codes with a formula

Name:
Date:
Class:
A

Quick Questions

Shade in the A or B circle for each question.

1. Which formula builds the ID code P01 for row 2?
A ="P"&TEXT(ROW()-1,"00")
B ="P"+TEXT(ROW()-1,"00")
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
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
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
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
B

Match the Pairs

Draw a line connecting each item on the left to its match on the right.

Left columnRight column
1="P"&TEXT(ROW()-1,"00") A=TEXT(5,"00")
2="S"&ROW() BBuilds an ID code like P01 from the row number so each row gets its own code
3Join the letter P and a two-digit number into one text code, using the & symbol CJoins the letter S to the current row number, giving codes like S2, S3 and S4
4Show the number 5 as the two-digit text 05 so every code is the same length DThe 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")
5=P&TEXT(ROW()-1,"00") EThe 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.
6Copy the ID codes, delete the name column, and keep the formulas as they are F="P"&TEXT(ROW()-1,"00")
C

Definitions

="P"&TEXT(ROW()-1,"00")
D

Fill in the Blanks

Complete each line of code by filling in the missing parts.

1.
="P"&____(ROW()-1,"00")
2.
="P"_TEXT(ROW()-1,"00")
3.
="P"&TEXT(___()-1,"00")
4.
="P"&TEXT(ROW()-_,"00")
5.
="P"&TEXT(ROW()-1,"__")
E

Spot the Error

Shade in the letter of the line that contains an error.

Question 1
A A1: Name
B B1: Student ID
C B2: ="P"+TEXT(ROW()-1,"00")
D Copy B2 down to B9
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
Question 3
A A1: Name
B B1: ID
C B2: ="P"&TEXT(ROW()-1,00)
D Copy B2 down to B9
Question 4
A Row 1 holds the headings
B B2: ="P"&TEXT(ROW(),"00")
C Row 2 is the first student
Question 5
A B1: ID
B B2: ="P"&TEXT(ROW()-1,"00")
C Drag fill handle across B2:E2

Answer Key

Creating ID codes with a formula
Section A: Quick Questions
Q1
A — ="P"&TEXT(ROW()-1,"00")
Q2
A — Row 1 holds the headings, so subtracting 1 makes the first data row number 1
Q3
B — It turns it into 05, so every code has two digits
Q4
B — Copy the formula down the column
Q5
A — Paste as values only, so the codes become fixed text
Section B: Match the Pairs
Q1
="P"&TEXT(ROW()-1,"00") → Builds an ID code like P01 from the row number so each row gets its own code
Q2
="S"&ROW() → Joins the letter S to the current row number, giving codes like S2, S3 and S4
Q3
Join the letter P and a two-digit number into one text code, using the & symbol → ="P"&TEXT(ROW()-1,"00")
Q4
Show the number 5 as the two-digit text 05 so every code is the same length → =TEXT(5,"00")
Q5
=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")
Q6
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.
Section C: Definitions
Q1
="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.
Section D: Fill in the Blanks
Q1
="P"&TEXT(ROW()-1,"00")
Q2
="P"&TEXT(ROW()-1,"00")
Q3
="P"&TEXT(ROW()-1,"00")
Q4
="P"&TEXT(ROW()-1,"00")
Q5
="P"&TEXT(ROW()-1,"00")
Section E: Spot the Error
Q1
C — Text and numbers are joined with &, not +. Using + causes a #VALUE! error. It should be ="P"&TEXT(ROW()-1,"00").
Q2
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'.
Q3
C — The format given to TEXT must be in quotes, because it is text. It should be TEXT(ROW()-1,"00").
Q4
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.
Q5
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'.