← ACARA Digital Technologies Years 9–10

Encrypting a letter with a Caesar formula in a spreadsheet

Shifts the capital letter in A2 forward by the key in locked cell $B$1, wrapping Z back to A, and returns the encrypted letter. Copy it across so each cell encrypts one letter.

Part of: ACARA Digital Technologies Years 9–10 — Cyber Security Threats and Vulnerabilities — Module 118

Understanding Encrypting a letter with a Caesar formula in a spreadsheet in ACARA Digital Technologies Years 9–10

What it does: A has the code 65, so taking away 65 gives A = 0, B = 1 and so on, ready for the shift to be added. MOD gives the remainder after dividing by 26, so any position past 25 loops round to the start of the alphabet. $B$1 is locked, so the key cell stays the same when the formula is copied. Without the dollar signs, the reference moves to C1, D1 and so on, and the key is lost. The alphabet has 26 letters. MOD keeps the result between 0 and 25, so with a key of 3, Y (24) becomes 27 MOD 26 = 1, which is B.

Common mistakes: The alphabet has 26 letters, so MOD must divide by 26 to wrap Z back to A. Using 25 gives the wrong letters. It should be 26)+65). CHAR turns a number into a letter, but A2 holds a letter, so CODE is needed to get its number first. It should be C2: =CODE(A2)-65.

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 Encrypting a letter with a Caesar formula in a spreadsheet to a class →

This module's key terms are also in a printable crossword (Modules 860–875) and word search (Modules 860–944).

Take this worksheet further — free with Cloodit

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

Learning
Duo
Which formula correctly encrypts the capital letter in A2 using the key in B1, so it can be copied across?
=CHAR(MOD(CODE(A2)-65+$B$1,26)+65)=CHAR(MOD(CODE(A2)-65+B1,26)+65)
Matches
=CHAR(MOD(CODE(A2)-65+B1,26)+65)
↓
The key B1 is not locked, so when the formula is copied across it becomes C1, D1 and so on and the shift changes
Applying
Meanings
Term
=CHAR(MOD(CODE(A2)-65+$B$1,26)+65)
Meaning
Shifts the capital letter in A2 forward by the key in locked cell $B$1, wrapping Z back to A, and returns the encrypted letter. Copy it across so each cell encrypts one letter.
Practice
=CHAR(MOD(CODE(A2)-65+$C$2,26)+65)
Testing
Gaps
=(MOD(CODE(A2)-65+$B$1,26)+65)
Check Answer
Spots
2 CODE(A2)-65+$B$1,
3 25)+65)

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

The 'Warthog' badge — Definition Digger, 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 correctly encrypts the capital letter in A2 using the key in B1, so it can be copied across?
A. =CHAR(MOD(CODE(A2)-65+B1,26)+65)
B. =CHAR(MOD(CODE(A2)-65+$B$1,26)+65) ✓
$B$1 is locked, so the key cell stays the same when the formula is copied. Without the dollar signs, the reference moves to C1, D1 and so on, and the key is lost.
2. What does CODE(A2) return if A2 contains the letter C?
A. 67 ✓
B. 3
CODE gives the character code of the letter. C is 67 in the character set, so subtracting 65 turns it into its alphabet position, with A as 0 and C as 2.
3. Why does the formula subtract 65 from CODE(A2)?
A. So the key is hidden from the interceptor
B. So A becomes 0, B becomes 1 and Z becomes 25 ✓
The code for A is 65. Subtracting 65 gives a position from 0 to 25, which makes the MOD 26 wrap-around work. The 65 is added back at the end.
4. What is the job of MOD(...,26) in the Caesar formula?
A. It wraps a shift past Z back around to A ✓
B. It makes the letter lower case
The alphabet has 26 letters. MOD keeps the result between 0 and 25, so with a key of 3, Y (24) becomes 27 MOD 26 = 1, which is B.
5. The key in B1 is 3 and A2 contains X. Which letter does the formula return?
A. A ✓
B. AA
X is position 23. 23 + 3 = 26, and 26 MOD 26 = 0, which is A. The wrap-around gives a single letter, not a new column of letters.

Match the Pairs

=CHAR(MOD(CODE(A2)-65+$B$1,26)+65) → Shifts the capital letter in A2 forward by the key in $B$1 and returns the encrypted letter
$B$1 → A locked cell reference that keeps pointing at the key when the formula is copied
Subtracting 65 turns a capital letter's code into a position from 0 to 25 (A = 0) → CODE(A2)-65
MOD with 26 wraps a shifted position back to the start of the alphabet so Z plus 1 becomes A → MOD(CODE(A2)-65+$B$1,26)
=CHAR(MOD(CODE(A2)-65+B1,26)+65) → The key B1 is not locked, so when the formula is copied across it becomes C1, D1 and so on and the shift changes
=CHAR(MOD(CODE(A2)-65+$B$1,26)) → The +65 is missing at the end, so CHAR gets a number from 0 to 25 instead of a letter code

Key Term

=CHAR(MOD(CODE(A2)-65+$B$1,26)+65)
Shifts the capital letter in A2 forward by the key in locked cell $B$1, wrapping Z back to A, and returns the encrypted letter. Copy it across so each cell encrypts one letter.

Fill in the Blanks

1. =CHAR(MOD(CODE(A2)-65+$B$1,26)+65)
Hint: Type the function that turns a number back into a letter.
2. =CHAR(MOD(CODE(A2)-65+$B$1,26)+65)
Hint: Type the function that turns the letter in A2 into its number.
3. =CHAR(MOD(CODE(A2)-65+$B$1,26)+65)
Hint: Type the locked cell reference that holds the key, with dollar signs so it stays fixed when copied.
4. =CHAR(MOD(CODE(A2)-65+$B$1,26)+65)
Hint: Type the number of letters in the alphabet, so the shift wraps from Z back to A.
5. =CHAR(MOD(CODE(B2)-65+$B$1,26)+65)
Hint: This formula was copied one cell to the right. Type the unlocked reference for the next letter.

Spot the Error

Question 1
A =CHAR(MOD(
B CODE(A2)-65+$B$1,
C 25)+65)
Line C — The alphabet has 26 letters, so MOD must divide by 26 to wrap Z back to A. Using 25 gives the wrong letters. It should be 26)+65).
Question 2
A C2: =CHAR(A2)-65
B D2: =MOD(C2+$B$1,26)
C E2: =CHAR(D2+65)
Line A — CHAR turns a number into a letter, but A2 holds a letter, so CODE is needed to get its number first. It should be C2: =CODE(A2)-65.
Question 3
A =CHAR(MOD(
B CODE(A2)-65+$B$1,
C 26)
D +64)
Line D — After MOD gives a number from 0 to 25, you add 65 to turn it back into a capital-letter code. Adding 64 makes every letter one step too early. It should be +65).
Question 4
A B1: three
B A2: H
C B2: =CODE(A2)-65+$B$1
Line A — The key must be a number so the formula can add it to the letter's number. The word three would cause a #VALUE! error. It should be B1: 3.
Question 5
A =CHAR(
B MOD(CODE(A2)-65+$B$1,26)
C -65)
Line C — The 65 was subtracted at the start to get a number from 0 to 25. It must be added back at the end so CHAR gets a capital-letter code. It should be +65).

Student with a class code? Join your class →