Puzzle Building in Microsoft Excel

The mighty spreadsheet is a canvas for a puzzle constructor. Rows and columns are merely suggestions to someone who sees a vegetable garden. If you work professionally with Excel, you may have seen eye-popping dashboards that make you think Excel had run into a phone booth for a quick change of attire.

Check out the first 20 seconds of this video:

While my goal is less ambitious than creating a puzzle-building dashboard, I push Excel to the limits of my ability. I have created templates for generating half a dozen puzzle types, including cryptograms, hashiwokakero, logic puzzles and sudoku. Some of the templates are just for layout, while others have interactive design tools that really speed the process of generating puzzles.

Raw Data

Excel can handle enormous amounts of data. Thankfully, I only need it to import small datasets: sudoku data downloaded from the web and my database of 279,500 English words and their definitions.

Graphics

Second only to pencil and paper, Excel is my tool of choice for prototyping. I can open a template and start designing within seconds. The design elements are good enough for most puzzles. I play with geometric shapes, lines, grids and artistic text for labels. During the prototyping stage, it's nice to be able to slap things down on the sheet and drag them about as needed. When a puzzle is complete, I'm either going to make a screen capture or I'm going to rebuild it in Affinity Publisher (especially if the screen capture doesn't scale up to a clear image.) Since I'm publishing in black and white, I don't worry about background colors, though I do play with shadows and contrast.

Automation

I use two programming languages that are built into Excel. One language, VBA, automates the stuff that shows on the spreadsheet. The other language, M, handles that massive database of words, slicing, dicing and filtering it into little lists of words for different puzzles. Together with the templates, these languages turn Excel into a puzzle factory.

While the built-in formulas and sorting and filtering tools are great for general-purpose organization, I've written some cool time-saving functions that are especially useful for word-based puzzles. A shuffler rearranges the alphabet to create cryptogram codes. The same shuffler randomizes any list that I give it. Another function gives me all the permutations of a list, such as ABC, ACB, BAC, BCA, CAB, CBA. This is handy for building skyscrapers and related puzzles that only allow a unique arrangement of digits in rows and columns.

Although I don't create Fill-in puzzles with Excel, I use it to sort the clue words alphabetically by length. I even have a special scrambler for Jumble-style puzzles, which compares the scrambled words to the letters in the quip answer to ensure that all of them are present.

Excel formulas place the scrambled letters from the table into the puzzle layout. (Sharp-eyed readers will note that a different order appears in the final puzzle. This is due to Excel’s automatic recalculation. The letters are shuffled each time I type another word.)

Dashboards and Protocols

No factory is complete without a control center, from which one can observe the creation process. Since my templates are for in-house use only, I don't have to worry about silly regulations like safety switches and grounded wires. It's just a hot mess of conveyor belts, levers and bins of shiny parts. But it's not chaotic: I have meticulously outlined the steps required to turn out each puzzle type.

Discovery

Just as data scientists analyze information to extract insights, I use the power of Excel to explore interesting relationships between words. This goes beyond anagrams, which I explore with another program. My word “research” has included ideas such as:

Finding ten-letter words consisting of a four-letter word followed a six-letter word, both of which begin with the same letter. (e.g., COLDCOCKED, MINIMARKET)

Finding four-letter stems that create seven-letter words when both AGE and ION are appended (e.g., MILL-, PASS-)

Often, the results inspire me to change the original puzzle idea. Maybe I couldn’t find enough candidates. This happened with AGE and ION. I could only find a few such matches, so I turned the puzzle into a Boggle-style grid with just the stems. Then I relaxed the rules for the ION words and appended them as an additional challenge. You’ll find the puzzle in Mind-bending Variety Puzzles, Volume 1, page 50.

Puzzle Validation

Of all the things I love creating with Excel, the logic grid puzzle tops the list. Currently, I have a purely manual process. However, by recreating the grid on a spreadsheet, I can fill it in, one clue at a time. I use a different color for each clue. As I fill in the grid, I can easily see where more information is needed. If I mess up, I clear the grid and try again. Once I think I have enough clues, I erase the grid and try to solve it logically.

The key to making this fun and effective is for me to record the explanation in a separate text file as I go along. This method is slow and can be excruciating, but it shows me when I’m providing either duplicate or irrelevant clues.

As I gain experience constructing logic grid puzzles, Excel makes it easy for me to focus on trickier clues. Of course, it’s up to me to make interesting scenarios. Excel is no good at that!