Sudoku Spreadsheet

If you found this page, you probably already know what Soduku is, but just in case you don't, it's a puzzle of Japanese origin where you start with a 9x9 grid divided into nine 3x3 squares with some numbers, always from 1 to 9, pre-placed. The object is to complete the grid, filling in all the blank spaces with 1 to 9 values so that every row, every column, and every 3x3 square contains the numbers 1 to 9 (once each).

Simple Sudoku puzzles can be solved by "chasing" numbers around the grid ("this space must be a 1 because this square must have a 1, and every other space in the square is already filled or already has a 1 in the same row or column"). The more interesting problems (well, that's what I think) are ones where you need to do more than that.

What's presented here is an Excel spreadsheet which may be used to play with Sudoku problems. There are several ways to use it, depending on what you find interesting. Note that to reduce bandwidth this and the other spreadsheet here have been converted to zip files. The spreadsheet contains no macros, although some macros might be helpful, as you probably wouldn't choose to run a spreadsheet with macros from an unknown source.

If you just stick to the left hand side of the first sheet (named 'Soduku') then you can enter a puzzle and go about solving it. The main thing it gives you, other than being able to change your answers easily, is that if you enter an impossible answer, the cells round the edge of the puzzle will "light up" (in red) to show you - an R at the end of each row with an error, a C at the top and bottom of each column with an error, a dozen Ss to indicate a 3x3 square with an error - and an X at each corner if there are any errors.

But behind this, the spreadsheet gives you some extra help - if you want it. This can be in the form of "hints", which show the possible values which can be entered in each cell, and "solutions", which show some definite values in the solution. Note that the latter is (usually) incomplete, it shows you only the next few solution values which can be deduced from the corresponding hints.

The hints can be seen in the 'Hints' sheet, and the solutions in the 'Solutions' sheet - if you request it. The lower of the two pull-down lists on the 'Sudoku' sheet (shown in red and by default set to "Hint/Solution Level?") controls this. It allows three settings, "Level 1", "Level 2" and "Level 3". These use increasing levels of complexity of logic to improve the hints, and find more solution values. (Details are given below.) When this is set to any of these levels, the hints can be viewed in the left hand grid on the 'Hints' sheet, and the solutions on the 'Solutions' sheet. Note that in the former, the word "Any" is used rather than a complete list 123456789 when there is no information restricting a cell's value. In the latter, values already entered (in the left hand grid on the 'Sudoku' sheet) are shown in black, new solution values are shown in red. If there is an error (no values are possible in a cell) then this will be shown by a red X (the only use of colour in the hints grid).

The hints or solutions can also be displayed in the right hand grid on the 'Sudoku' sheet, by choosing "Hints" or "Solutions" from the upper of the two pull-down lists on the 'Sudoku' sheet (also shown in red and by default set to "Hints or Solutions?"). The hints display is as on the 'Hints' sheet, except that the font size is smaller (in order that the hints can also be displayed).

Level 1 of this spreadsheet is based on simple "chasing numbers" logic, eliminating numbers which are already used in the corresponding row, column or 3x3 square. Use of this logic can be seen in the first two examples in the examples spreadsheet. In order to save bandwidth, what this provides is multiple sheets each of which is a Sodoku problem, or a stage in solving it. You can copy the 81 cells of each of these Sudoku problems into the appropriate cells of the left hand grid on the 'Sudoku' sheet of the main spreadsheet.

The first example starts with the sheet 'Example1'. Using the solutions at level 1 repeatedly produces the sheets 'Example1a' to 'Example1f'. Note that actually it doesn't matter if you copy all of the solutions sheet, part of it, or even just one value at a time. (Note that as you copy the solutions into the main grid, the solutions sheet changes and you may make new changes before you have finished the old ones. This does not matter, whether you can solve the problem at a given level is independent of this order.)

The second example, from 'Example2' to 'Example2i' is much the same. It is however slower, which corresponds to a more difficult problem for a person to solve, although still just "number chasing".

A faster way to solve this problem is shown in 'Example3' to 'Example3e'. 'Example3' is in fact identical to 'Example2' but the solution steps are then faster. This is done by using the solutions at level 2. At this level we use that if in a 3x3 square all of the 1s (for example) are in one row of the square then, although we do not know where the 1 is exactly, we know enough to eliminate the possibility of a 1 in the remainder of that row (6 squares, in the other two 3x3 squares). Obviously the same applies to columns, and it also applies exchanging the roles of rows (or columns) and squares: if in a row (or column) we do not know where the 1 (for example) is, but we do know which 3x3 square it is in along that row (or column), then we can eliminate the possibility of a 1 elsewhere in that square.

For 'Example3' the use of level 2 solutions speeds the final solution up. But in other cases it makes the problem soluble. This can be seen in 'Example4'. Sheets 'Example4a' to 'Example4d' use level 1 solutions. However at this point using level 1 solutions grinds to a halt. However using level 2 solutions the solution continues from 'Example4e' to 'Example4k'. (You can also increase the solution level to get over a difficult point and then reduce it again if you want.)

It is of course up to the user whether the solutions provided at level 2 are interesting or not. If you think they is interesting, then just use level 1 to get to a point like 'Example4d' and continue by hand. If you think it isn't interesting, then use level 2. Of course even in the former case, the level 2 hints (especially compared with the level 1 hints) provides you with help on learning to use this technique.

Next we have 'Example5'. A single number can be added to it (using level 1 or level 2) as 'Example5a' and then it stops. At this point we have an interesting problem, which can however be logically solved, without using trial and error, by inspecting the level 1 or level 2 hints (in this case, in others the level 2 hints might help more than the level 1 hints). Now (with 'Example5a' installed) in the level 1 or level 2 hints you can find three cells in the same (centre) row containing 89, 89 and 59. Now each of the first two must be an 8 or a 9, so one is an 8 and one is a 9. Which is which we don't yet know, but don't worry about that. The cell marked 59 must be a 5 or a 9. But there must already be a 9 on the same row (one of the 89 cells), so the 59 cell can't be a 9, it must be a 5 - and that turns out to be all you need, giving 'Example5b'. (There is actually a second deduction that can be made using similar logic, the 489 in the same row must be a 4, but this is not included in 'Example5b', although it could have been.) The rest of the puzzle is just number chasing again, use of level 1 is sufficient. (It is not presented here, but it terminates with the solution 'Example5c'.)

This type of inference can also be done by the spreadsheet, using level 3 hints and solutions. This applies this type of logic to rows, as in the example above, and also to columns and to 3x3 squares. It gives the 5 and 4 as described above when applied to 'Example5a'. Level 3 hints and solutions only work for two cells as described, they do not consider the possibility of, for example, three cells containing 123 in a single row, column or 3x3 square making a 1234 also in the same row, column or 3x3 square into a 4. However I have yet to see this in a real Sudoku problem. It also, for example, allows two cells containing 12 in the same row, column or 3x3 square to convert a 1234 into a 34.

There are Sudoku problems which go further than this, needing additional logic to solve. One case is that it might be that applying the new logic of level 2 again after that of level 3 could be useful, or applying the level 3 approach more than once might reduce options further. But even these (which the spreadsheet will not do, although it could be expanded to do so, and also see the comments on use of your own logic below) are not always sufficient. An example is given as 'Example6'. Using the spreadsheet can get you to 'Example6a', but then even level 3 does not help.

However it is possible to logically solve this problem. 'Hints6a' is what thge level 3 hints gives at this point. At the top left of the middle right 3x3 square is a cell with the possible values 12. In the same row as the 12 is a cell 19, and in the same column as the 12 is a cell 29. Now regardless of whether the 12 is a 1 or a 2, one of the 12 or the 29 is a 9, although we don't know which. But in the same column as the 19, and in the same row as the 29, is another 19 (in the top right of the top middle 3x3 square). Now regardless of which of the first 19 or the 29 is the 9, then the second 19 has a 9 in the same row or the same column, so must be a 1. Adding this to 'Example6a' gives 'Example6b'. Further use of the spreadsheet solutions now leads to the final solution 'Example6c'. (This example and method of solution was taken from a posting to the Usenet group sci.math. I have not worked out how to include it in this spreadsheet.)

A final facility provided is a second grid on the 'Hints' sheet. This may be used as a "scratchpad" for working out your own solutions. A good way to use this is to copy (by value, use Excel's Paste Special, under Edit) the hints, at whatever level you choose and experiment. Unlike the other parts of the spreadsheet (except the left hand grid on the 'Sudoku' sheet) these cells are not protected. How you use them is up to you. However there is an additional option. If you set the top left hand cell on this sheet to "Right" (it is set to "Left" by default) then this scratchpad hints grid is used rather than the spreadsheet created grid in determining the solutions. For this to be valid, the scratchpad hints grid must be consistent with the spreadsheet hints grid (at the current level) or be equal to the entries on the left of the 'Sudoku' sheet. This means that all options eliminated by the spreadsheet must also be eliminated on the scratchpad, if not then solutions will be invalid. To check this, cells around the scratchpad will "light up" in a similar way to those around the main problem grid, except that there is no "S" option. As a special case, an empty cell on the scratcpad is treated as equal to the spreadsheet hints sheet. Because of this it is never necessary to enter that all options are possible. ("Any" is not permitted, and "123456789" does not fit into the cell.)

The scratchpad hints grid can be used as a manual aid to solving other Sudoku variants that this spreadsheet cannot handle, provided they are based on the same 9x9 grid. One such is sometimes known as "Killer Sudoku" where the grid is started empty, but as well as the usual Sudoku constraints, various groups of cells have their totals indicated. These can be used to constain the hints. (They do not do so directly, but deductions made from them in various ways can be recorded on the scratchpad for convenience, and this may show solutions too. It may be more appropriate to only use this after the problem has been started rather than from the beginning.)

Finally there are five other sheets (Work0 to Work4). These aren't intended to be looked at - they contain all the messy calculations to determine the errors, hints and solutions. But (and here comes the health warning) of course they might contain errors - the way I've done it (better suggestions welcome) there's quite a few formulas created by editing, not simply copying and whilst I've done quite a bit of testing, errors might have slipped through. If you find any errors, do please let me know - my thanks to Andrew Huber for pointing out a fault in the error checking in an earlier version of the spreadsheet, and to Archie McKerrell for finding an error in the level 3 logic. Similarly any other good ideas for improvement are welcome. Also if you found the spreadsheet of any use at all I'd appreciate knowing. Thanks to those who have already done so.

One such use (not suggested by anyone else) is so-called "Killer" Sudoku problems. For what they are, and how to use the spreadsheet to help (but not actually solve - that would be a whole new piece of work) follow this link.

Please send any feedback to: chris@mnemosyne.demon.co.uk

You may also be interested in my Kakuro spreadsheet.

Return to home page

Last modified 3rd May 2009: Corrected fault in spreadsheet error detection.