April 02, 2018
in
#codes and ciphers

You don't always need to use a programming language for cryptography. In this post, I describe how to use a spreadsheet to encipher and deciper with a Caesar cipher.

You can follow along with a copy of the spreadsheet on Google Drive. You'll find this post makes a lot more sense if you can see how all the bits fit together in the spreadsheet, as well as being able to see the results of the formulas used. Make your own copy of it if you want to encipher and decipher your own messages.

Describing how the spreadsheet works is tricky, as you can't just quote snippets of program code and discuss them. I've broken the spreadsheet down into regions (see the diagram below) and will go through them, region by region, describing how it all fits together.

At the top of the spreadsheet, in cells **B2** to **B6**, is the "dashboard" summarising the cipher breaking.

- Cell
**B2**has the original message - Cell
**B4**has the Caesar key to use - Cell
**B6**is the generated plaintext using that key (it contains the formula`=AA47`

). - Cell
**B7**is the generated ciphertext using that key (it contains the formula`=AA48`

).

To use the spreadsheet, you put your message in cell **B2** and the key in cell **B4**. The spreadsheet will then encipher the message and put the result in cell **B7** and decipher the message with the result in cell **B6**. That way, you don't have to tell the spreadsheet whether you want the message enciphered or decipered: it does both for you.

During the processing, the spreadsheet uses uppercase for ciphertext letters and lowercase for plaintext letters. That means that case isn't preserved in the generated plaintext and ciphertext.

Just a copy of the alphabet.

Simply all the shifts for the Caesar cipher, 0–25.

Each cell in this block says what each plaintext letter would be, with the Caesar shift given in the corresponding row in **A16:A41**.

A | B | C | D | … | Z | AA | |
---|---|---|---|---|---|---|---|

10 |
a | b | c | … | y | z | |

\( \vdots \) | |||||||

16 |
0 | `=B10` |
`=C10` |
`=C10` |
… | `=Z10` |
`=AA10` |

17 |
1 | `=C16` |
`=D16` |
`=E16` |
… | `=AA16` |
`=B16` |

18 |
2 | `=C17` |
`=D17` |
`=E17` |
… | `=AA17` |
`=B17` |

\( \vdots \) | \( \vdots \) | \( \vdots \) | \( \vdots \) | \( \vdots \) | \( \vdots \) | \( \vdots \) | \( \vdots \) |

41 |
25 | `=C40` |
`=D40` |
`=E40` |
… | `=AA40` |
`=B40` |

Cells **B16** to **AA16** just contain the formulas `=B10`

to `=AA10`

: a Caesar shift of 0 gives the original alphabet.

Cells **B17** to **Z17** each contain the formula `=C16`

to `=AA16`

: in other words, the content of the cell one above and to the right. Cell **AA17** contains the formula `=B16`

. Together, these formulas rotate the row above one space to the left, with the leftmost cell of the block moving to the right.

Cut and paste row **B17:AA17** into the remaining rows of the block, **B18:AA41**. You'll end up with all the shifts of the alphabet, with row **41** containing `z a b c … w x y`

.

Now we know how to do convert plaintext to ciphertext letters for all shifts, we can do the enciphering and deciphering for the shift given in cell **B4**.

Each column in the shifted plaintext block shows how a single plaintext letter is transformed with different shifts. Going down column **B**, you can see how `a`

is transformed into `a`

, `b`

, `c`

, … as the Caesar shift changes from 0, 1, 2, …

We treat the shifted plaintext block as a large lookup table, giving us the ciphertext letters we need. The `LOOKUP`

function does the lookups for us.

B | C | D | … | AA | |
---|---|---|---|---|---|

44 |
`=UPPER(LOOKUP($B$4, $A$16:$A$41, B16:B41))` |
`=UPPER(LOOKUP($B$4, $A$16:$A$41, C16:C41))` |
`=UPPER(LOOKUP($B$4, $A$16:$A$41, D16:D41))` |
… | `=UPPER(LOOKUP($B$4, $A$16:$A$41, AA16:AA41))` |

45 |
`=B10` |
`=C10` |
`=D10` |
… | `=AA10` |

`LOOKUP`

can be used in a couple of ways. The way I'm using it here has this signature:

```
LOOKUP(search_key, search_range, result_range)
```

The function looks for the `search_key`

in the `search_range`

(either a row or a column). If it finds the key, it moves along the `result_range`

the same number of steps and returns the value it finds there. The `result_range`

can be either a row or a column, but need not be the same shape as the `search range`

.

In this case, the `search_key`

is the identified Caesar shift, found in cell **B4**. The `search_range`

is the list of possible shifts in the cells **A16:A41**. The `result_range`

depends on the plaintext letter we're after. It's the column under the appropriate plaintext letter from the alphabet block. For `a`

, the `result_range`

is **B16:B41**. For `b`

, the result range is **C16:C41**, and so on, up until **AA16:AA41** for the column corresponding to `z`

.

Therefore, we can say the formula `LOOKUP($B$4, $A$16:$A$41, B16:B41)`

will give the ciphertext letter corresponding to plaintext `a`

for the Caesar shift we're using.

As we're using uppercase letters for ciphertext, we wrap the result in a call to `UPPER()`

to convert the ciphertext letter to uppercase.

That explains row **44**: it's the ciphertext letters for each . Row **45** is just those plaintext letters, copied down from **B10:AA10**.

Now for rows **47** and **48**.

B | C | D | … | AA | |
---|---|---|---|---|---|

47 |
`=SUBSTITUTE(UPPER($B$2), B44, B45)` |
`=SUBSTITUTE(B47, C44, C45)` |
`=SUBSTITUTE(C47, D44, D45)` |
… | `=SUBSTITUTE(Z47, AA44, AA45)` |

48 |
`=SUBSTITUTE(LOWER($B$2), B45, B44)` |
`=SUBSTITUTE(B47, C45, C45)` |
`=SUBSTITUTE(C47, D45, D44)` |
… | `=SUBSTITUTE(Z47, AA45, AA44)` |

The `SUBSTITUTE(where, search, replace)`

command looks in `where`

for text `search`

and replaces it with `replace`

. When enciphering, we use it to substitute platinext letters with ciphertext letters. When deciphering, we replace ciphertext letters with plaintext.

Cells **B44:AA44** and **B45:AA45** together show how each ciphertext letter relates to a plaintext letter. The cells in **B47:AA47** do the conversion from ciphertext to plaintext. With a shift of 21, **B44:B45** will say that ciphertext `V`

corresponds to plaintext `a`

.

The formula in **B47** converts all the `V`

s in the message into `a`

s. The formula in **C47** takes the result of in **B47** and then converts all the `W`

s into `b`

s. **D47** takes the result in **C47** and converts all the `X`

s into `c`

s, and so on until **AA47** converts all the `U`

s into `z`

s. The result in **AA47** is the final, extracted plaintext, and that result is copied up to the dashboard by cell **B6**.

This is why the use of case to distinguish plaintext and ciphertext is important. With a shift of 21, ciphertext `v`

converts to plaintext `a`

, and ciphertext `a`

converts to plaintext `f`

. If we just did those two conversions one after the other, the final plaintext would have no `a`

s but a whole bunch of `f`

s. However, if we use case, we can distinguish between the conversions `V`

to `a`

and `A`

to `f`

. That's why where's a call to `UPPER`

in cell **B47**: it ensures that what we're treating as ciphertext is all uppercase.

In the same way, the cells **B48:AA48** convert the message into ciphertext.

The two results, **AA47** and **AA48**, a copied back up to the dashboard.

And that's the result! That's how you can use a spreadsheet to implement Caesar ciphers.

There's no particular reason why you can't extend this approach to other ciphers, such as the affine cipher. But there is a practical limit to do with the number of keys. The Caesar cipher has 25 possible keys. The affine cipher has 12 × 26 × 2 = 624 possible keys. That would mean that the spreadsheet would need 624 rows in the large blocks, as you still need one row for each possible key.

I've not got around to building a spreadsheet that large, but there's no reason why you couldn't.

Post cover image by NordWood Themes.