← ACARA Digital Technologies Years 9–10

Decrypting a Caesar cipher in a spreadsheet

Shifts a letter back by the key (3) to decrypt it. Subtracting the key undoes adding it, and MOD 26 wraps A-Z around so the result stays a letter.

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

Understanding Decrypting a Caesar cipher in a spreadsheet in ACARA Digital Technologies Years 9–10

What it does: The only change from the encrypt formula is -3 instead of +3, and that change reverses the encryption. Each row's decrypt formula reads the cipher letter in column B and returns the plain letter in column C. Fill it down to decrypt the whole message. Encrypting adds the key to shift forward. Decrypting subtracts the key to shift back to the original letter. For a letter like A shifted back by 3, MOD 26 wraps round to X instead of leaving the alphabet. A correct decrypt gives back exactly the original message, so matching the original text proves it works.

Common mistakes: The alphabet has 26 letters, so the wrap-around must use MOD 26. MOD 25 gives wrong letters. It should be MOD(C2,26). To decrypt you shift back, so the key is subtracted. Adding 3 would shift the letter forward again. It should be MOD(B2-3,26).

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 Decrypting a Caesar cipher 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
To decrypt a Caesar cipher with a key of 3 in a spreadsheet, which change do you make to the encrypt formula?
Subtract the key: CODE(B2)-65-3Add the key again: CODE(B2)-65+3
Matches
=CHAR(MOD(CODE(B2)-65+3,26)+65)
↓
This adds the key instead of subtracting it, so it shifts the letter forward again and does not decrypt it
Applying
Meanings
Term
=CHAR(MOD(CODE(B2)-65-3,26)+65)
Meaning
Shifts a letter back by the key (3) to decrypt it. Subtracting the key undoes adding it, and MOD 26 wraps A-Z around so the result stays a letter.
Practice
=CHAR(MOD(CODE(B2)-65-3,26)+65)
Testing
Gaps
=CHAR(MOD(CODE(B2)-653,26)+65)
Check Answer
Spots
2C2: =B2-65-3
3D2: =CHAR(MOD(C2,25)+65)

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

The 'Viperfish' badge — Deep Debugger, 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. To decrypt a Caesar cipher with a key of 3 in a spreadsheet, which change do you make to the encrypt formula?
A. Subtract the key: CODE(B2)-65-3 ✓
B. Add the key again: CODE(B2)-65+3
Encrypting adds the key to shift forward. Decrypting subtracts the key to shift back to the original letter.
2. The cipher letter in B2 is D and the key is 3. Which formula gives the original letter?
A. =CHAR(MOD(CODE(B2)-65+3,26)+65)
B. =CHAR(MOD(CODE(B2)-65-3,26)+65) ✓
D is shifted back 3 places to A. Adding 3 would shift it forward to G instead.
3. Why does subtracting the key undo the encryption?
A. Subtracting turns the letters into numbers
B. Shifting back by 3 reverses a shift forward by 3 ✓
A shift forward and a shift back by the same amount cancel out, so you land on the original letter.
4. A decrypt column is next to the cipher text. Which cell does its formula read from?
A. The cipher letter in the encrypted column, such as B2 ✓
B. The key cell only, with no cipher letters
The decrypt formula needs the encrypted letter to shift back, so it must read from the encrypted column.
5. What does MOD(...,26) do in the decrypt formula?
A. Wraps the shift around so A-Z stays a letter ✓
B. Doubles the key to make it stronger
For a letter like A shifted back by 3, MOD 26 wraps round to X instead of leaving the alphabet.

Match the Pairs

=CHAR(MOD(CODE(B2)-65-3,26)+65) → Decrypts one letter by shifting it back 3 places and wrapping around from A to Z
=CHAR(MOD(CODE(B2)-65+3,26)+65) → Encrypts one letter by shifting it forward 3 places, which is the opposite of decrypting
To decrypt, subtract the key from the letter number instead of adding it, because shifting back undoes shifting forward → =CHAR(MOD(CODE(B2)-65-3,26)+65)
A decrypt column in column C turns the cipher letters in column B back into the original letters → =CHAR(MOD(CODE(B2)-65-3,26)+65)
=CHAR(MOD(CODE(B2)-65+3,26)+65) → This adds the key instead of subtracting it, so it shifts the letter forward again and does not decrypt it
=CHAR(CODE(B2)-3) → There is no MOD 26 wrap-around, so letters near the start like A, B and C shift back to symbols instead of letters near Z

Key Term

=CHAR(MOD(CODE(B2)-65-3,26)+65)
Shifts a letter back by the key (3) to decrypt it. Subtracting the key undoes adding it, and MOD 26 wraps A-Z around so the result stays a letter.

Fill in the Blanks

1. =CHAR(MOD(CODE(B2)-65-3,26)+65)
Hint: Type the operator that shifts the letter back by the key, undoing the encryption.
2. =CHAR(MOD(CODE(B2)-65-3,26)+65)
Hint: Type the number of letters in the alphabet, so the shift wraps around.
3. =CHAR(MOD(CODE(B2)-65-3,26)+65)
Hint: Type the function that wraps a number around so it stays in the A-Z range.
4. =CHAR(MOD(CODE(B2)-65-3,26)+65)
Hint: Type the function that turns the encrypted letter into its character number.
5. =IF(C2=A2,"Match","Different")
Hint: Type the function that picks one of two results to check the decrypt column against the original text.

Spot the Error

Question 1
A B2: =CODE(A2)
B C2: =B2-65-3
C D2: =CHAR(MOD(C2,25)+65)
Line C — The alphabet has 26 letters, so the wrap-around must use MOD 26. MOD 25 gives wrong letters. It should be MOD(C2,26).
Question 2
A A2: K
B B2: =CODE(A2)-65
C C2: =CHAR(MOD(B2+3,26)+65)
Line C — To decrypt you shift back, so the key is subtracted. Adding 3 would shift the letter forward again. It should be MOD(B2-3,26).
Question 3
A F1: 3
B A2: H
C B2: =CODE(A2)-65
D C2: =CHAR(MOD(B2-$F$1,26))
Line D — MOD gives a number from 0 to 25, which is not a letter code. You must add 65 back before CHAR, so it should end with +65).
Question 4
A E2: H
B C2: =CHAR(MOD(B2-3,26)+65)
C F2: =IF(C2<>E2,"OK","BAD")
Line C — The check should say OK when the decrypted letter equals the original letter. <> tests for 'not equal', so the result is backwards. It should be C2=E2.
Question 5
A A2: K
B B2: =CODE(A2)-65
C C2: =MOD(B2-3,26)
D D2: =CODE(C2+65)
Line D — CODE turns a letter into a number, but this step needs to turn a number back into a letter. It should be =CHAR(C2+65).

Student with a class code? Join your class →