Microsoft Office

If you love suduko, check out this Microsoft Excel blog, courtesy of Joseph Chirilov

Today’s author, Charlie Ellis, a Program Manager on the Excel team, shares a spreadsheet he built in Excel for solving Sudoku puzzles. The spreadsheet can be found in the attachments at the bottom of this post.

For those of you who don’t already know, Sudoku is a type of logic puzzle (that I was completely addicted to about three years ago) that requires you to place the numbers 1-9 into a grid obeying certain rules (lots more information on Sudoku is available on the web).

A while back, a fellow PM on the Excel team, Dan Cory, wrote a spreadsheet for solving Sudoku puzzles using Excel formulas and made it available on Office Online (here). Dan’s spreadsheet was great in that, unlike many of the Sudoku solving spreadsheets out there, it didn’t use any VBA or other scripting to do the work of solving the puzzles, and relied instead on the iterative calculation feature of Excel. It’s quite cool and has been a popular download, but one thing about the spreadsheet that I wanted to see if I couldn’t improve upon was just how complicated it is. In fact, Dan made every single cell its own different formula, and he ended up having to use VBA to create the formulas because maintaining and debugging it without VBA to write all those different formulas in an automated way was impossible.

As soon as I saw Dan’s spreadsheet, I wanted to make my own version of a Sudoku solver that not only used only formulas, but also one where the formulas were relatively understandable and there were a small number of distinct formulas. It turned out to not be that tough to build, but I think I learned a fair amount trying different approaches to the problems of making an iterative model like this one perform well and at the same time be reasonably maintainable and understandable. I think it might even have turned up a reasonably useful way at looking at abstraction within formulas given the Excel formula language. I’ve always wanted to blog about the process of creating this spreadsheet and about how iterative formulas work to show the power of Excel’s formula language, because it illustrates the usefulness of circular references and iterative calculation, and because I just think it’s an incredible amount of fun so here goes. Lots of people have created more powerful solvers, many as spreadsheets, some using just formulas, but I wanted to try to explain how you can go about creating a solver and hopefully share some formula tricks that people find useful.