A few simple rules to keep your spreadsheet readable and error-free.
- Use one set of column headings through the entire worksheet and freeze the panes to the top of the sheet. If you need a completely different table, create a new worksheet in your workbook
- Avoid using too many colours, use shades of grey instead
- Round data by dividing by thousands, millions so that they are readable. Round to 1-2 digits behind the comma. Excel will continue to calculate things with the highest precision, even if you do not see it
- No underlines, italics, but use bold, uppercase, and bold upper case
- Use long, descriptive row headings
- Write your formulas top down, a lower row depends on a result that was calculated the line above. Keep formulas as simple as possible. A spreadsheet is a form of computer code: it should be readable the week after you created it. One formula with 15 cell references is the equivalent of code spaghetti.
- Add totals where ever you can to check for formula errors and to keep things readable. The brain often memorises a number quicker than a description (35.4 instead of “North Africa sales 2014 H1”)
- If you need to source data out of a “data dump” keep that data in a separate worksheet and pull the numbers via links into your analysis. It creates a nice clean separation between your data source and the analysis, and makes it possible to over write your source data with new information and have the analysis updated instantly (always double check)