In this video, I show you how to create your own cryptogram/cipher in Excel using multiple functions.

We start by breaking down strings of text into individual columns, creating a randomized key then using VLOOKUP to translate your text. This also provides the option of creating multiple versions with different keys to discourage answer sharing if giving this to a group.

Below is an overview & functions used:

Enter text in column A

Split the text strings into individual columns using =MID($A1, COLUMNS($A$1:A$1), 1)

Create a key using =RANDBETWEEN(1,1000)

Check for duplicates (optional)


Use VLOOKUP to encode the text =VLOOKUP(X1,$A:$B,2,FALSE) -change X2 to the cell you’re looking up or just enter the information into the VLOOKUP function as explained in the video

Copy & paste values

Related video: How to Solve a Cryptogram Using Excel