Viewpoint






2021-09-01

SPREADSHEET GUIDELINES

I've spent a lot of time making and reviewing budgets, forecasts, financial models and investment projections in Excel and Google Sheets. Over time, I've developed a couple of guidelines that I believe could be of value to anyone making investment cases and financial models for others to view, understand and ultimately base their investment and strategy decisions on. Although most of my work has been in MS Excel and Google Sheets, the guidelines hold relevance to all modern spreadsheets (I believe).


  1. Structure and label your data logically and coherently. Data is the basis of information, and information is the basis of intelligence, and all together, they form the basis for decision-making.

  2. Use the sheets and the linking of data between them. Don't cram everything into one sheet if you have separate types of data and information. Remember, your calculations, file and figures need to be easy to find and understand.

  3. Set apart and highlight key assumptions and outcome drivers, and make it easy for an outsider to understand the logic behind your assumptions.

  4. Go easy on colours and fonts. Decide early on on what font, font size, label types, and colours you will go with, and make your selection make it easy for an outsider to understand your data and figures.

  5. If you have a few cells which require figures from a reader, or which invites a reader to experiment with the file and its information, mark those specific cells with a certain colour and label. This makes it easy to understand where to enter data and which data can be manipulated without destroying formulas.

  6. If your file and spreadsheet require input from its readers, write clear and easy to understand instructions, and preferably, provide examples of what you want from them. If possible, use drop-down menus, lock cells and formulas to avoid unnecessary deletions or mistakes.

  7. To make your data and conclusions more readable, use the grouping feature (not the hide columns or row feature), as this allows a reader to expand or compress underlying figures when needed.

  8. Be clear, to the point of being explicit, when labelling rows, columns and data; and last but not least, be consistent in the labelling of your data. If you call income as revenue, stay true to your choice instead of sometimes calling it income and other times revenue.

  9. Keep formulas simple and easy to understand. It's better to use more cells with less complicated formulas structured logically, as opposed to one "super-cell" cell containing all the formulas packed into one cell. Make it easy to understand your calculations. Clarity removes unnecessary puzzling and ambiguity.

  10. Use graphs to visualize data and information, as it makes it easier for a reader or an outsider to grasp your conclusions and facts.

  11. Keep graphs simple. The graphs should highlight underlying data, not the other way around.

  12. If needed or wanted, build scenarios by duplicating your tables and aggregating data into graphs where outcomes can easily be compared.

  13. To make large sets of data and conclusions more readable, form a summer sheet where you highlight selected data into a "one-pager". This allows a reader to easily get a high-level view of your work without having to guess and spend much time understanding your figures and the underlying data; unless of course wanted, when they, of course, can take a deep dive into the file and its contents and formulas.

  14. If you anticipate different types of readers, form different summary sheets adapted to the different reader groups, where e.g. different KPI:s and assumptions are highlighted.

  15. For summaries where you want to combine text with figures, and where you want the combination of them to be aligned with and updated with the figures in your file as they are changed, build your text and sentences with formulas and functions such as xlookup, vlookup, hlookup, concatenate, max, min, etc.


For sure there are more tips and tricks to get the work done and make your work easily understood for review and decision-making, but following the above guidelines will take you far.


Spreadsheet Jockey = Someone who can do kick-ass shit in Microsoft Excel. (Source: Urban Dictionary)


Image Source: Andertoons