Guidelines:
- Divide a workbook into logical modules.
- Modules are clearly separated and named.
- The modules have a flow that fits the context.
A fundamental principle of good spreadsheet design is the use of a modular structure. Using modules makes a spreadsheet easier to understand, use, modify, and test.
Organize content into a modular structure
We should organise our workbook content into modules, with each tab performing one – and only one – purpose. This contrasts with a typical tab arrangement, which generally involves conflating a variety of content types on each tab, such as: inputs, modelling, and presentation of results.
Combining different types of content on a worksheet is OK for very small workbooks. We do so is some of our demonstration workbooks, for simplicity. But such a design does not scale well. For non-trivial workbooks, a modular design is much better.
Examples of these two approaches are shown below:
- A typical workbook consists of a largely unstructured collection of tabs, often with generic tab names.
- A modular structure organizes content by type. Related tabs are adjacent, and they may have the same colour to reinforce their relationships.
Recommended modules
The specific modules you'll need in a spreadsheet depend on the situation. A recommended modular workbook structure includes the following worksheets:
- About. Describes the workbook's purpose. May include a table of contents, version number, copyright, and disclaimer if applicable.
- Notes. Documentation for the workbook's users, including instructions for using and updating the workbook. May include references for sources, though often it is better to indicate the source adjacent to the data. Include a key for styles used in the workbook, either on the Notes or About worksheet.
- Inputs. Data and assumptions that the user is expected to input.
- Results. Presents the results of the model, including key outputs and key charts. If there are many charts, then a separate Charts worksheet might be appropriate.
- Analysis. Performs the analysis needed to produce the results. Parts of the analysis may be in separate sub-modules.
- Data. Holds the model's data. There may be a combination of local and imported data. The data may be presented in stages: raw data, data cleansing steps, and final data for use in the analysis.
- Tests. Performs a set of automated tests, confirming the workbook's correctness. May also collate the results of manual tests, where applicable. All other worksheets should include an alert to show when there is a problem with the test results.
- Control. Support for the workbook's developers. Holds values used in the workbook that the user would not normally change – such as constants and source tables for drop-down lists. Also has version history (version number, date, description, developer, reviewer), if applicable. Can include development notes, though complex workbooks may require separate developer documentation.
Characteristics of modules
Reflecting the diverse uses of spreadsheets, modularity is a very flexible concept.
Some aspects to consider when designed a modular structure:
- Define the modular structure of a new workbook when it is created. Spreadsheet development is much easier and less error-prone when you start with a good design.
- The workbook structure – including the modules, their order, and their names – will depend on its purpose. Ask the users what they want/expect. Having designed a structure, check that it makes sense to the users.
- In some circumstances, the order listed above is preferred. In other circumstances, the users may require that the Tests be near the front, to give them confidence that the spreadsheet works OK before they consider the Results.
- The modules should be as separate and self-contained as possible. However, for ease-of-use, it may be reasonable to include some key high-level inputs, like scenario selection, in a Model or Results module.
- Modules should be small and self-contained, with minimal connections between modules.
- A workbook may require a module to be divided across multiple worksheets. That is, each module is divided into sub-modules. A technique that may be useful in this case is to use worksheet tab colours to show worksheets that are closely related.
- Some workbooks may combine multiple modules on the same worksheet. For example, if there are few notes, then they could be included on the About worksheet. When combining modules, it may be appropriate to delineate the structure using headings, formatting, and white space.
- Sometimes usability is enhanced by putting user inputs adjacent to results or charts, so the user can immediately see the impact of changes. If so, then it must be clear which cells are inputs and which are results.
- The flow of data and results between modules needs to be clear and consistent. A workbook that has many tangled references between modules – a form of spaghetti code – is confusing and error-prone. Such a structure suggests opportunities for improving the design.
Advantages and disadvantages of modules
Although modules are generally a good thing, there are advantages and disadvantages that need to be considered.
Advantages of a modular approach include:
- Dividing a workbook into modules makes it easier to build, use, and test.
- A logical structure helps define the flow of information through a workbook.
- Clearly delineated modules reduce errors resulting from complexity.
- Changes to a part of the workbook can be made partially in isolation from the rest of the workbook.
- Parts of a workbook can be used as a "black box", without detailed knowledge of their internal structure.
- Development is more efficient. Developers may be able to work on separate modules in parallel.
- Modules can be reused in another workbook.
Disadvantages of a modular approach include:
- The chain of logic from data, through analysis, to results can be longer and may include multiple worksheets. This might make the logic more difficult to follow and understand.
- There may be more formulae, which may increase the risk of error.
- Usability may be reduced if the user can't see the effect of changing an input.
Some of these advantages and disadvantages are contradictory. That is the nature of the trade-offs that is inherent in many of these guidelines.
Example of modular structure
The following workbook, which can be downloaded, has eight worksheets that adhere to the guidelines above.
