Cryptograms are a fun way to challenge your brain, but solving them by hand can be time-consuming. In this tutorial, you’ll learn how to use Microsoft Excel and the VLOOKUP function to build your own interactive cryptogram solver. As you identify letters in the cipher, Excel automatically fills in every matching letter throughout the puzzle, making it much faster to spot words and solve the message.
Even if you’ve never used VLOOKUP before, this project is a great way to learn how lookup formulas work in a practical, hands-on example.
What You’ll Learn
- How to set up a simple substitution cryptogram in Excel
- How the VLOOKUP function works
- How to build a lookup table (cipher key)
- How to automatically decode letters throughout a puzzle
- Why absolute references (
$) are important - How to hide unnecessary zero values for a cleaner worksheet
- How to use Excel as a problem-solving tool
Common Uses
- Solving simple substitution cryptograms
- Learning how VLOOKUP works
- Creating educational classroom activities
- Building puzzle-solving worksheets
- Teaching lookup functions through a fun project
- Exploring logical problem-solving with Excel
Requirements / Compatibility
This tutorial was originally recorded in 2020 and remains compatible with current versions of Microsoft Excel, including:
- Microsoft 365
- Excel 2024
- Excel 2021
- Excel 2019
- Excel 2016
- Excel for Mac
This tutorial is designed for simple substitution ciphers (sometimes called Aristocrat cryptograms) where each encrypted letter always represents the same decoded letter.
Practice & Resources
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.
Click the button below to view the resources available for this tutorial.
Video Tutorial
Breaking Down the Concepts
This project combines several simple Excel techniques to create an interactive cryptogram solver.
VLOOKUP
=VLOOKUP(lookup_value, table_array, column_index, FALSE)
The VLOOKUP function searches for a value in one column and returns the matching value from another column.
In this project:
- Column A contains the encrypted letters.
- Column B contains the letters you’ve identified.
- Every letter in the puzzle uses VLOOKUP to look up its decoded equivalent.
For example, if you discover that:
F = B
every F in the puzzle automatically becomes B.
Why it’s used:
Instead of replacing every letter manually, you only enter each substitution once. Excel updates the entire puzzle automatically.
Lookup Table
The lookup table is simply your cipher key.
| Cipher | Actual |
|---|---|
| A | |
| B | |
| C | |
| … |
As you solve the puzzle, you gradually fill in the second column.
Because every VLOOKUP formula references this same table, every matching letter updates instantly.
Absolute References ($)
Your VLOOKUP formula uses dollar signs:
$A$2:$B$27
The dollar signs “lock” the lookup table so it doesn’t move when you copy the formula across your worksheet.
Without them, Excel would shift the lookup range one column at a time, causing incorrect results.
Why it’s used:
- Keeps every formula looking at the same cipher key.
- Makes copying formulas quick and reliable.
FALSE (Exact Match)
The last argument in VLOOKUP is:
FALSE
This tells Excel to return a result only if it finds an exact match.
That’s important because every encrypted letter should translate to only one specific letter.
Paste Values
After copying formulas throughout your worksheet, you may eventually want to preserve the results.
Using Paste Special → Values replaces the formulas with the letters themselves.
Why it’s used:
- Prevents formulas from changing accidentally.
- Makes the worksheet easier to share or print.
Optional Cleanup
During the video, zero values are hidden to make the worksheet easier to read.
This doesn’t change the puzzle, it simply removes visual clutter while you’re solving it.
Step-by-Step Instructions
Build the Cipher Key
- Create two columns for your lookup table.
- Enter the alphabet (A–Z) in the first column.
- Leave the second column blank for your decoded letters.
Enter the Puzzle
- Type or paste the cryptogram into Excel.
- Place one character in each cell across the worksheet.
- Leave blank cells where spaces occur.
Create the VLOOKUP Formula
- Select the first cell below your puzzle.
- Insert a VLOOKUP function.
- Set:
- Lookup value = the encrypted letter above
- Table array = your cipher key
- Column index = 2
- Range lookup = FALSE
- Lock the lookup table with dollar signs.
- Copy the formula across the worksheet.
- Copy it down for each row of the puzzle.
Solve the Puzzle
As you identify letters:
- Enter them into the second column of your lookup table.
- Watch the decoded message update automatically.
- Continue until the entire puzzle is solved.
Additional Tips
- Begin by identifying common three-letter words like THE or AND.
- Look for repeated letter patterns to narrow down possibilities.
- Center-align your worksheet to make the decoded text easier to read.
- Hide zero values if they make the worksheet distracting.
- If you’re creating your own puzzles, you may also enjoy my tutorial on How to Create a Cryptogram in Excel.
If you’re interested in the products I mentioned, they can be found here on Amazon:
- Hunt a Killer: Death at the Dive Bar – https://amzn.to/2Tx5PY4
- Brain Games: Cold Case Puzzles – https://amzn.to/3oropPo
Enjoyed this tutorial? Subscribe to my YouTube channel for more step-by-step software tutorials, tips, and practical ways to work more efficiently.
Simple Software Tutorials 