Houd formules dichtbij de input
What separates the great spreadsheets from the rest, is documentation! This will help future users of the spreadsheets to really understand your work. Your spreadsheet 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. 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?
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 software. To learn more about this click here.
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.