My Sudoku Spreadsheet

Using the link just below, you can download the Excel spreadsheet I wrote to help solve Sudoku puzzles. You may use it, and pass it on to others if you wish. It was written in Microsoft Excel 2003, using basic Excel only (no macros). I've been told that it does not work using Microsoft "Works". Scroll down on this page if you want to learn more about it.

To download it in Windows, right-click on the link just below, and select “Save target as ...”.

The Sudoku Spreadsheet

[On 11/30/06, I updated the above to a version which includes a separate Excel "sheet" (Sheet2) with a large version of the solution (or partial solution) for easy printing.]

Here's a screen image of a corner of the spreadsheet, partially filled in with numbers:

Sudoku spreadsheet, upper-left corner

The corner shown illustrates how the spreadsheet propagates Sudoku constraints: when a number is entered into an entry (on the left side of the entry, in red, in the light-blue spreadsheet cells), it is struck out of the “Possibility List” (on the right side of the entry) everywhere in that entry's row, column, and box.

If you want a Wordoku spreadsheet, click here
If you want to send me mail, click here

More detailed instructions:

The following instructions are included in the spreadsheet. That's a better place to read them, because the instructions in the spreadsheet give more examples in spreadsheet form. But if you want to get an idea of what the spreadsheet does before you download it, you can read the instructions here.

The spreadsheet handles the mechanics of constraint propagation in a Sudoku grid. In the spreadsheet, each "entry" in the Sudoku grid is represented by a lightly outlined set of 12 spreadsheet cells, 4 wide by 3 high. To fill in an entry, you put a number into the middle cell of the leftmost column (which has a lightly colored background). The nine cells in the other columns of that entry are that entry's "Possibility List", showing which numbers can still possibly be placed in that entry, based on the other entries that have been set.

Once you fill in a value, the number you enter is struck out of the Possibility List everywhere in that entry's row, column, and 3 X 3 box. Thus the spreadsheet does all the "bookkeeping" needed to see what numbers are still possible in each entry.

When only one possibility remains in an entry, a blue "S" appears in that entry's upper-left-hand cell. See these instructions in the spreadsheet for an example. The S (for "Set") is to make it easy to find entries that have been determined by the constraints. But they must still be set by hand - the spreadsheet will not set them for you automatically.

Additional important information is provided in the "Row Counts", which the spreadsheet also computes. These reveal counts of how often each number appears in the Possibility Lists of each row. The thing to look for there are any counts of "1". For example, a "1" in the "6" position of a Row Count means a "6" is possible only in ONE entry in that row. Scan the row to find a "6" in a Possibility List. There may be other numbers in the Possibility List for that entry, but you can fill that entry in with a "6", because this is the ONLY entry in that row that can be a "6", and there MUST be a "6" somewhere in that row.

Similarly, "Column counts" and "Box counts" are also provided.

A magenta X in the lower-left cell of an entry means a contradiction has been encountered: that entry has not been set, but the Possibility List is empty.

A red "XXX" at the top of the spreadsheet indicates an error (a number appears twice in a row, column, or box).

Some tips:

The spreadsheed is protected. You can enter numbers only in the cells with colored backgrounds. A "Tab" will advance from one unprotected cell to the next, in lexical order.

I copy the spreadsheet to a file with a new name before using it, so I don't have to later clear all the entries. To clear a single entry, select the cell with a colored background, and hit "Delete". Putting text such as a space into that cell will not "clear" it (from the point of view of the formulas).

Backtracking: There's another reason for saving the entire spreadsheet under a new name: If you get to a point where you can't make any further deductions, you can save the entire sheet to preserve your position, and then guess at an entry with multiple possibilities. If that leads to a dead end, exit without saving, reload, and try a different value. In hard problems, you may have to make multiple guesses - keep track of where you've been.

For a while, I always used to choose an entry with only two values in the Possibility List to guess at. But here's something I've found experimentally: it can be better to guess at an entry with more than two possibilities. In one particularly hard puzzle, when I picked entries with only two values in the Possibility List, and tried one of those two, I quickly got to a second dead end. I then picked another entry with only two values in the Possibility List and tried one of them, and arrived at a third dead end. This continued to a depth of five or six (while tracing out the tree on a piece of paper, so I wouldn't get lost). However, starting from the first dead end in the same puzzle, when I picked an entry with five numbers in the Possibility List, and tried them one after another, each trial led quickly to either a dead end or a solution.

Advanced techniques:

The instructions in the spreadsheet illustrate some advanced techniques for solving difficult puzzles. Suppose a row (or column or box) has entries which contain ONLY two possibilities in the Possibility List, for example, ONLY the two numbers 2 and 7. It follows that one of those entries must contain the 2, and the other the 7.

Why? Because if a 2 or a 7 were anywhere else in the row (or column or box), you wouldn't be able to fill both these entries. So you know these entries contain the 2 and 7, although you don't yet know which is which.

This means that you can strike 2 and 7 from the Possibility Lists everywhere in any row, column, or box containing BOTH the entries in question (the instructions in the spreadsheet show an example).

How can we strike things from the Possibility List in the spreadsheet? The spreadsheet cells comprising the Possibility List contain formulas, and are protected.

One way is to just keep it in your head that these possibilities are not really valid. Or, you can print out the sheet, and cross them off.

But there's another way, although it is a bit dangerous:

  • Be sure you SAVED the basic spreadsheet before you started!
  • Unprotect the sheet: Tools >> Protection >> Unprotect sheet
  • Select the numbers that you want to eliminate from the Possibility Lists, and hit "Delete".
  • Re-protect the sheet (Tools >> Protection >> Protect sheet), and continue working.

But once you do the above, you've destroyed this copy of the spreadsheet for future use!

Philosophy: Is using this spreadsheet "cheating"?

Not from my point of view. All it's doing is constraint propagation, and helping to search for places in which a number appears in only one Possibility List in a row, column, or box. These are the purely mechanical parts of solving a Sudoku problem. Sometimes I still do puzzles by hand, and just use the spreadsheet to check my work for errors. If you think it's "cheating", well, just don't use it.

I've never found a Sudoko puzzle I couldn't solve with the aid of this spreadsheet.

If you want to send me mail, click here
Click here to go to my base Sudoku page.
Click here to go to Larry Krakauer's home page
This page was last updated November 30, 2006