Document Your Work
What separates the great spreadsheets from the other ones, is documentation! This will help future users of the spreadsheets to really understand your work. Even when the future user is you. Your spreadsheet should contain a ‘Documentation’ sheet as first, or last worksheet. Explain the future reader what the spreadsheet is for, where you obtained the data and where the results may be used.
A Documentation Worksheet to Increase Comprehensibility of your Spreadsheet
As your spreadsheet grows larger, adding a worksheet dedicated to documentation, including a table of contents is not a luxury. It will increase the comprehensibility and transparency of the spreadsheet a lot. And 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 reappear 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 creating a ‘Changes’ worksheet too. In this sheet you track all the changed formulas, and who changed them and why.
A more sophisticated, but useful addition to the documentation is a flow chart that explains the dynamics of the spreadsheet. Which worksheets are related and what external sources are being used as input? With PerfectXL you can create a flow chart in less than a minute. Creating a flow chart can also help you to understand the quality your spreadsheet at the same time. Did you make a clear 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 provides for the possibility to add comments on a cellular level (right click, insert comment). A little red triangle notifies the user that a cell contains a comment. We do not recommend the use of this option. We believe that the functioning of a spreadsheet is usually complicated already, and the comments will only distract the user.
We prefer commenting in a separate column or sheet, depending on the objective.
Note: Sometimes, when adding an explanatory text in a cell, Excel creates a formula. For example when using ‘=’ or ‘+’. To avoid this, you should start your sentence with an apostrophe.