Decrypting a Caesar cipher in a spreadsheet

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

Decrypting a Caesar cipher in a spreadsheet

Name:
Date:
Class:
A

Quick Questions

Shade in the A or B circle for each question.

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
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)
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
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
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
B

Match the Pairs

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

Left columnRight column
1=CHAR(MOD(CODE(B2)-65-3,26)+65) AThis adds the key instead of subtracting it, so it shifts the letter forward again and does not decrypt it
2=CHAR(MOD(CODE(B2)-65+3,26)+65) B=CHAR(MOD(CODE(B2)-65-3,26)+65)
3To decrypt, subtract the key from the letter number instead of adding it, because shifting back undoes shifting forward C=CHAR(MOD(CODE(B2)-65-3,26)+65)
4A decrypt column in column C turns the cipher letters in column B back into the original letters DEncrypts one letter by shifting it forward 3 places, which is the opposite of decrypting
5=CHAR(MOD(CODE(B2)-65+3,26)+65) EDecrypts one letter by shifting it back 3 places and wrapping around from A to Z
6=CHAR(CODE(B2)-3) FThere 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
C

Definitions

=CHAR(MOD(CODE(B2)-65-3,26)+65)
D

Fill in the Blanks

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

1.
=CHAR(MOD(CODE(B2)-65_3,26)+65)
2.
=CHAR(MOD(CODE(B2)-65-3,__)+65)
3.
=CHAR(___(CODE(B2)-65-3,26)+65)
4.
=CHAR(MOD(____(B2)-65-3,26)+65)
5.
=__(C2=A2,"Match","Different")
E

Spot the Error

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

Question 1
A B2: =CODE(A2)
B C2: =B2-65-3
C D2: =CHAR(MOD(C2,25)+65)
Question 2
A A2: K
B B2: =CODE(A2)-65
C C2: =CHAR(MOD(B2+3,26)+65)
Question 3
A F1: 3
B A2: H
C B2: =CODE(A2)-65
D C2: =CHAR(MOD(B2-$F$1,26))
Question 4
A E2: H
B C2: =CHAR(MOD(B2-3,26)+65)
C F2: =IF(C2<>E2,"OK","BAD")
Question 5
A A2: K
B B2: =CODE(A2)-65
C C2: =MOD(B2-3,26)
D D2: =CODE(C2+65)

Answer Key

Decrypting a Caesar cipher in a spreadsheet
Section A: Quick Questions
Q1
A — Subtract the key: CODE(B2)-65-3
Q2
B — =CHAR(MOD(CODE(B2)-65-3,26)+65)
Q3
B — Shifting back by 3 reverses a shift forward by 3
Q4
A — The cipher letter in the encrypted column, such as B2
Q5
A — Wraps the shift around so A-Z stays a letter
Section B: Match the Pairs
Q1
=CHAR(MOD(CODE(B2)-65-3,26)+65) → Decrypts one letter by shifting it back 3 places and wrapping around from A to Z
Q2
=CHAR(MOD(CODE(B2)-65+3,26)+65) → Encrypts one letter by shifting it forward 3 places, which is the opposite of decrypting
Q3
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)
Q4
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)
Q5
=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
Q6
=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
Section C: Definitions
Q1
=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.
Section D: Fill in the Blanks
Q1
=CHAR(MOD(CODE(B2)-65-3,26)+65)
Q2
=CHAR(MOD(CODE(B2)-65-3,26)+65)
Q3
=CHAR(MOD(CODE(B2)-65-3,26)+65)
Q4
=CHAR(MOD(CODE(B2)-65-3,26)+65)
Q5
=IF(C2=A2,"Match","Different")
Section E: Spot the Error
Q1
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).
Q2
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).
Q3
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).
Q4
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.
Q5
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).