Making the most of spreadsheets

Posted on Published

Given the producer role is one that aggregates information, it’s not surprising that spreadsheets are one of the producer’s go-to tools. Most of the time, we only use spreadsheets in a very simple way (usually adding up some sort of breakdown to get a total figure). The things we do can also be done easily with a calculator, but doing it in a spreadsheet is efficient and lessens the chance of error because:

  • It’s easy to make changes without having to redo all the calculations every time;
  • It’s easier to see links between numbers and to check calculations;
  • It allows someone else to check your workings.

For a spreadsheet to be most effective:

  • It should have clear headings, especially where there are units concerned (sometimes I’ve seen spreadsheets where I don’t know where the figure calculated is for the number of hours or number of days);
  • Items should be clearly annotated so that it’s clear what a figure relates to;
  • If you are transferring figures to another document, make sure the items in your other document match up to items in your spreadsheet, so you don’t have to do any tricky manual calculations that makes it hard to compare the two for errors (e.g. don’t do calculations in your spreadsheet without GST if your final document’s figures have to include GST; make sure you include the same subtotals in your spreadsheet as you are including in your final document);
  • Try to keep magic numbers (e.g. “8” for hours in a day) out of your formulas — it can be hard to remember what a number relates to, and if the number is used in multiple places, it will be a pain to replace them all if it changes. It’s better to list it as a “variable” on your spreadsheet so you can see it clearly, and so you can link to the same cell from all your formulas;
  • When summing totals, try and make it easy to check that you’ve included all the cells that are required (e.g. try moving all your subtotals into a separate column from your individual amounts so that you can sum the entire column, rather than having to select cells individually);
  • For critical figures (e.g. grand totals for estimates), it can be useful to have some sort of basic check for errors (e.g. summing all the individual amounts and checking that the total comes to the same number as when you sum all the subtotals).

If a spreadsheet isn’t set out clearly, then its value in terms of efficiency is limited because you’ll have to spend so much time checking your figures for errors, and no one (including your future self) will be able to decipher it later.

Also published on Medium.

Share this post

Leave a Reply

Your email address will not be published. Required fields are marked *