If you enjoy word puzzles or want to create your own classroom activities, Excel can help you build custom cryptograms with surprisingly little manual work. By combining a few built-in functions, you can automatically encode text, generate random cipher keys, and even create multiple versions of the same puzzle.

In this tutorial, you’ll learn how to create a cryptogram in Microsoft Excel using functions like MID, RANDBETWEEN, and VLOOKUP. Along the way, you’ll see how these functions work together to transform plain text into an encoded puzzle that you can print, share, or customize.

Why create a cryptogram in Excel?

While online generators can create cryptograms instantly, building one in Excel gives you complete control over the puzzle. You can customize the text, generate multiple randomized versions, modify the cipher, and learn several useful Excel functions along the way. It’s a fun project for teachers, puzzle enthusiasts, or anyone looking to practice Excel in a creative way.

What You’ll Learn

  • How to build a simple substitution cipher in Excel
  • How to split text into individual characters using the MID function
  • How to generate a randomized cipher key with RANDBETWEEN
  • How to encode text using VLOOKUP
  • How to create multiple versions of the same cryptogram
  • Why copying and pasting values preserves your finished puzzle

Common Uses

  • Creating classroom activities
  • Homeschool learning exercises
  • Printable word puzzles
  • Escape room challenges
  • Educational games
  • Family game nights
  • Learning Excel functions through a fun project
  • Creating multiple puzzle versions to discourage answer sharing

Requirements / Compatibility

This tutorial was originally recorded in 2020, and the functions used continue to work in current versions of Microsoft Excel, including:

  • Microsoft 365
  • Excel 2024
  • Excel 2021
  • Excel 2019
  • Excel 2016

The formulas and workflow shown in this tutorial remain fully compatible with modern versions of Excel.

Practice & Resources (Practice Workbook)

Want to work through the examples yourself? This tutorial includes a practice workbook you can use while following along with the video and step-by-step instructions.

The workbook includes the formulas and worksheet layout used throughout the video, making it easy to experiment with different phrases, generate new cipher keys, and create your own custom cryptograms.

Download the sample workbook to build your own cryptogram as you follow the tutorial.

View Workbook

Video Tutorial

Breaking Down the Concept

This project combines several Excel functions and features to automatically transform plain text into a cryptogram. Each one serves a specific purpose, and together they create a reusable system for generating custom substitution ciphers.

LEN – Determine the Longest Clue

=LEN(A1)

The LEN function counts the number of characters in a cell.

In this workbook, it’s used to find the length of your longest clue so you know how many columns you’ll need when splitting the text into individual letters.

Why it’s used:

  • Counts every character, including spaces and punctuation.
  • Helps determine the worksheet layout before building the cryptogram.

MID – Split Text into Individual Letters

=MID($A1,COLUMNS($A$1:A$1),1)

The MID function extracts a specific number of characters from the middle of a text string. Here, it returns one character at a time, allowing each clue to be separated into individual letters.

As the formula is copied across the worksheet, the character position automatically increases, pulling the next letter from the clue until the entire phrase has been split into separate columns.

Why it’s used:

  • Breaks words into individual letters.
  • Makes it possible to translate each letter independently.

RANDBETWEEN – Generate a Random Cipher Key

=RANDBETWEEN(1,1000)

The RANDBETWEEN function generates a random number for each letter of the alphabet.

After generating the numbers, the alphabet is sorted by that random-number column. This creates a randomized substitution key while leaving the original alphabet unchanged.

Why it’s used:

  • Creates a different cipher each time.
  • Makes it easy to generate multiple versions of the same cryptogram.

Because random numbers can occasionally repeat, it’s a good idea to check for duplicates before sorting. You can quickly do this with Conditional Formatting → Highlight Cell Rules → Duplicate Values. If any duplicates appear, simply generate a new set of random numbers before continuing.

VLOOKUP – Encode Each Letter

=VLOOKUP(C2,$A$1:$B$26,2,FALSE)

The VLOOKUP function replaces each original letter with its matching letter from the randomized cipher key.

It searches the first column of the lookup table (the original alphabet) and returns the corresponding letter from the randomized alphabet.

Why it’s used:

  • Automatically translates every letter.
  • Eliminates the need to manually replace characters.

Why Are There Dollar Signs ($) in the Formula?

You may notice dollar signs in the VLOOKUP formula: $A$1:$B$26

These create an absolute cell reference, which tells Excel to keep the lookup table fixed when the formula is copied to other cells.

Without the dollar signs, the lookup range would shift down each row as the formula is copied, causing incorrect results or errors.

Think of the dollar signs as “pinning” the cipher key in place while everything else updates normally.

IFERROR – Handle Blank Cells (Optional)

As the formulas move beyond the end of shorter clues, Excel may return errors for blank cells or spaces.

Wrapping the lookup inside an IFERROR function allows those cells to remain blank instead of displaying error messages.

Example: =IFERROR(VLOOKUP(C2,$A$1:$B$26,2,FALSE),””)

This isn’t required, but it can make the finished cryptogram look cleaner.

Copy & Paste Values – Lock in the Finished Puzzle

Functions like RANDBETWEEN recalculate whenever the workbook changes, which means your cipher key can change unexpectedly.

Once you’re happy with the finished cryptogram, copy the completed puzzle and use Paste Special → Values.

This replaces the formulas with their results, creating a permanent version that can be printed or shared without changing later.

Creating Multiple Versions

One advantage of building your cryptogram in Excel is that you can quickly generate additional versions.

Simply create a new randomized key, encode the clues again, and save or paste the results as values. Each version uses the same clues but a different substitution key, making it useful for classrooms, puzzle clubs, or group activities where you want to discourage answer sharing.

Step-by-Step Instructions

  1. Enter your clues in a column, one per row. Keep them short.
  2. Find the longest clue’s length: click an empty cell, use =LEN(cell) to count its characters — this tells you how many columns you’ll need.
  3. Split each clue into letters: in the cell next to your text, use =MID(text_cell, 1, 1) for the first letter, drag it across enough columns, and increase the middle number by 1 for each column (this is easiest if you write the formula once with a relative column reference and copy it across).
  4. Build the alphabet key: in one column, list A–Z. In a second column, list A–Z again.
  5. Attach a random number: next to your second A–Z list, add =RANDBETWEEN(1,1000) for each letter.
  6. Check for duplicate random numbers: select that column → Conditional Formatting → Highlight Cell Rules → Duplicate Values. Re-generate if anything’s flagged.
  7. Shuffle the key: select the second alphabet column and its random-number column together → Data → Sort → sort by the random-number column, smallest to largest. This scrambles the second alphabet while the first stays A–Z — that’s your cipher key.
  8. Convert clue letters to cipher letters: next to each split letter, use =VLOOKUP(letter_cell, $A$1:$B$26, 2, FALSE) (lock the key range with $ so it doesn’t shift when you copy the formula).
  9. Clean up gaps: delete stray errors from blank/space characters, or wrap the formula in IFERROR(…) to pass them through untouched.
  10. Freeze a version to print: copy the finished cryptogram cells → Paste Special → Values into a fresh area/sheet, so it stops changing if the key gets re-sorted later.
  11. Make more versions (optional): re-sort the random-number column again to shuffle the key differently — handy for giving each participant a unique cryptogram.

Additional Tips

  • After generating your cipher, always Copy → Paste Values before saving or printing. Otherwise, the random key may change when Excel recalculates.
  • If duplicate random numbers occur, simply generate a new set before sorting.
  • You can easily create multiple versions of the same puzzle by generating a new random key each time.
  • This technique can be adapted for symbols, numbers, or other custom substitution systems—not just letters.

Related Features

This project combines several Excel techniques, including:

  • MID
  • VLOOKUP
  • RANDBETWEEN
  • Sorting
  • Copy & Paste Values

You may also enjoy my companion tutorial: How to Solve a Cryptogram Using Excel & VLOOKUP

Enjoyed this tutorial? Subscribe to my YouTube channel for more step-by-step software tutorials, tips, and practical ways to work more efficiently.