Spreadsheet documentation: document your work

What separates great spreadsheets from the rest, is documentation. Documentation will help future users of the spreadsheets to really understand your work. Your file should contain a ‘Documentation’ sheet as the first, or last worksheet.

A documentation worksheet

As your spreadsheet grows larger, adding a worksheet dedicated to documentation, including a table of contents, is not a luxury. It will significantly increase the comprehensibility and transparency of the spreadsheet.

Use tab colors

Did you know you can change the color of the tab by right clicking on it? Color-coded tabs allow you to categorize your sheets (such as input, output, and calculations). The color-codes can be used in the table of contents and the categories can be explained in the documentation sheet.

A changes worksheet

When a spreadsheet is used and edited by a lot of people, it is worth also creating a ‘Changes’ worksheet. In this sheet you (and other users) track all the changes in formulas and data, who changed them and why. It also allows you to keep a log of different file versions. Document your work for the future user!

Flowcharts

A more sophisticated and useful addition to the documentation can be a flow chart that explains the dynamics of the spreadsheet. A flowchart shows which worksheets are related to each other and which external sources are being used.

Create a flowchart with PerfectXL

With PerfectXL Explore you can create a flow chart in less than a minute. Creating a flowchart can also help you to understand the quality your spreadsheet. Did you make a spreadsheet with a clear, coherent structure or a spaghetti spreadsheet?

This image is one example of a sophisticated flow chart, it represents the structure of an Excel model. This particular visualization was generated by PerfectXL Explore.

Comments

Excel contains the possibility to add comments on a cell level (right click, insert comment). A little red triangle then notifies the user that a cell contains a comment. We do not recommend this approach. We believe that most spreadsheets are complicated enough already and the comments will only distract the user. We encourage users to place comments in a separate column or sheet.

Note: When adding an explanatory text in a cell, Excel can sometimes see it as a formula, like when ‘=’ or ‘+’ is used. To avoid this, you should start your sentences with an apostrophe.