Document Your Work
What separates the great spreadsheets from the rest, is documentation! This will help future users of the spreadsheets to really understand your work. This is even true when you are the future user. Your spreadsheet should contain a ‘Documentation’ sheet as the first, or last worksheet. Use this sheet to explain to the future reader what the spreadsheet is for, where you obtained the data and where the results may end up being used.
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. 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. The color-codes can be used in the table of contents and the categories can be explained in the documentation sheet.
When a spreadsheet is used and edited by a lot of people, it is worth also creating a ‘Changes’ worksheet. In this sheet you track all the changed formulas, who changed them and why.
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. With PerfectXL 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 flow or a spaghetti spreadsheet?
The image shows the visualization of a spreadsheet in PerfectXL. Read more about the spreadsheet visualization feature.
Comments in Separate Columns or Sheets
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.