As I’ve started to look at more and more spreadsheets with Audit, I’ve begun to wonder if there are ways I could change the design to make it cleaner and easier to check for errors. I’ve come up with a few techniques, detailed below, that seem to work well, especially alongside Audit:
- Make it read left to right, top to bottom
- Use headers to make it clear what each cell means
- Pull constants into their own cells
- Don’t nest formulas too deep, use separate and meaningful operations
- Try to group formulas in contiguous blocks
For example, in the image above I’ve placed an X on a cell to investigate it. Because I know this section is supposed to read left to right, I know that the highlighted cells that feed into it are supposed to be behind the cell I’m investigating. Seeing this will prompt me to go through and figure out what is going on when I see behavior like in the image below:
Use consistent and descriptive labels
A spreadsheet, like most complicated models, wouldn’t make much sense without some language describing what is going on; this isn’t news. The difficulty comes when spreadsheets get a little more complex and formulas need to get pulled out into separate sheets. Once that starts happening, the intermediate steps aren’t going to be examined by anyone other than the creator, or at least, that’s very tempting to believe.
The problem doesn’t come up when the sheet is being created though, it comes up down the road, when you’ve inherited a sheet from someone else and you have to change something about the way the model works. Without reasonable labels above columns and on sheets, it’s almost impossible to update a spreadsheet without making a mistake.
Put constants in their own cells
Consider the following:
I have a number, in euros, for the cost of a part (10,000) in a budget I’m assembling, and everything else is in dollars. That’s fine though, I have Google handy and according to Google, one euro is worth 1.5513 U.S. dollars. Great. So I setup a little formula in a nearby cell converting the machine like this: =A3*1.5513. Having fixed my unit problem I move on and finish my spreadsheet. Time passes.
It’s a year or two later and I want to bring this old budget up and use it for a new project, and as I do it I’m lucky and I at least notice in one cell where I have the magic number 1.5513 times some value. That’s great, but a year out I’m not likely to recall what that number was for because it wasn’t labeled. Moreover, even if I’m very clever and do know what I was doing in the past, its still going to be very difficult to find the other four or five places in the spreadsheet where I did this, and then update their values to the new exchange rate.
So, as you’re building a spreadsheet and you have a value embedded in a formula its worth considering whether the value is apt to change and whether or not its clear what the formula is doing, especially when you come back to look at it a year later.
Separate formulas into multiple steps
I’ve read articles that suggest you do just the opposite of this, so clearly there is some debate about whether it’s better to have fewer, but more complex formulas, or more, simpler formulas. I’ve taken to trying for the latter, especially in conjunction with Audit for the following reasons:
- With the cell highlighting in Audit, it’s easy to visualize how your spreadsheet works, even if the highlighting goes back over several different cells.
- If you’re approving regions in your spreadsheet, you can track your progress as you get each simple formula approved, rather than having to test the one very complex formula and risk losing your place.
- Excel’s formula bar is not a very friendly way to read a formula, especially as they get long. Why strain your eyes if you don’t have to?
- If you separate out complex formulas and use good header names, your spreadsheet becomes self documenting, meaning that it’s very easy to read through and see what all of the steps in a calculation really are.
Put formulas in contiguous blocks
The more I use Audit, the more I come to rely on regions to see how my spreadsheet works, and as a result I’ve taken to constructing my spreadsheets to try and create fewer regions. The more regions in a spreadsheet, the more complex it is, and the more difficult it is to audit, so by slimming down the structure I can save a lot of time and improve my confidence in the model.
Take a look at the following example:
In the image above I have the yearly data divided up with spacing between columns. That may improve readability, but it also breaks apart my regions. What I’ve taken to doing now is removing those spaces and re-adding them only if I actually need them.
Another trick for reducing the number of regions is to be aware of how Audit builds them. Specifically, Audit will try and make vertical regions rather than horizontal regions, so if I have a large square of identical formulas Audit will create a series of vertical regions. Most of the time this makes a lot of sense, but in some applications you’ll create a spreadsheet that looks like this:
Rather than having a bunch of two-cell vertical regions, I’d rather have to long horizontal regions. One easy way to fix that is to insert a row between the regions, forcing them apart and making Audit draw the regions horizontally.
Posted by: Erik Rehn, Product Manager, RedRover Software Inc