Sudoku: Options for layout and analysis in a Spreadsheet

To state the bleedin' obvious, a spreadsheet is a a two dimensional array of addressed cells. When working with an environment that has 3 or more factors that require analysis its presentation in terms of layout and organisation are of crucial importance.

A Sudoku is unusual in that it presents 3 dimensional factors (row, columns and sub-grids) in a 2 dimensional surface. A further confusion is that Sudoku uses that values 1 - 9, but these are not numbers and you cannot employ them in simple arithmetic. A pervasive view is that spreadsheets are exclusively for arithmetic purposes (like monetary accounting). I have always railed against that view and here I consider that options for modelling the solution to a Sudoku puzzle in a spreadsheet environment.

The Sudoku model and its Purpose

For many, the only purpose of an automated (or semi-automated) approach is to find a solution quickly. But you can do that by instinct and guesswork far more quickly than building a spreadsheet model. I thought that my model should demonstrate

• the step wise route that any individual used to solve the puzzle, and
• verify by analysis that those steps were valid.

There has been a long running debate among spreadsheet users and constructors about how much of the sheet should be visible when in every day use. This discussion is concerned with the integrity of spreadsheets in a commercial or administrative environment. Sudoku is a game and everything should be visible. My one concession to security is that I protect the individual worksheets in the process to prevent accidental changes to model verification elements, but they can easily be unprotected. There are no passwords.

How much detail?

This really depends on the sophistication of the analytical methods that any individual intends to employ.  Since it does not take long for enthusiastic players to get moderately sophisticated, I chose to include sufficient detail within the model to handle that. 

The puzzle for solution is normally presented as a 9 x 9 grid. For example

Most experts on Sudoku present the detail of a puzzle in the format below. There various forms of the method of presentation but they all have the same fundamental format of a large 3 x 3 grid each containing a medium 3 x 3 (the basic Sudoku cells) and each cell contains a 3 x 3 grid of the potential Identities. For presentation purposes, solutions are presented as large Identities that overwrite the lowest level of potential Id's.

In theory, I could mimic this in Excel, but it was clear that the verification analysis would become very difficult and muddled. The choice of presenting the available Sudoku Id's for each cell in the seemed both convenient for presentation and for the analytical verification that would be required.

Even so a presentation that potentially contained all the detail of the puzzle, was likely to be difficult to understand at first glance (see below). A visual key to the Sudoku cells would be a huge advantage.

I would to make use of the presentational aspects of Excel that were inherent within a standard package. This suggested that an approach as shown below would be both functional and quickly understood.

The fist level of detail need to generate the presentation is discussed at A Game of Control and Error Management