Spreadsheet design

In your exam, you’ll be asked to put together a small spreadsheet consisting of one of the elements you learn in the other units – it could be something like partnership profit distribution or a small budget or cash flow. It’s worth, therefore, considering a few of the design principles behind a decent spreadsheet.

At this juncture, I want to stress one very important thing: NEVER have data in a formula. For instance, if you have a formula multiplying a cell value by the VAT rate, =B6*20%, what happens if the VAT rate changes? Far better to have the VAT rate in a separate cell, so your formula says (e.g.) =B6*G1. That way, all you need to do is amend cell G1 with the new VAT rate and everything should cascade down. Learn how to do this here.

1. Top of the list is what do you want your spreadsheet to do? Is it a place to get calculations done, or to store data for further processing elsewhere? What kind of data will go into the spreadsheet, what information do you want to get out of it? Once you know this, you can then think about…
2. The layout. Commonly we have titles at the top and sides, data in the middle and formulae at the bottom and/or right. Sometimes I sketch out on paper what I need to achieve, before I start typing away and have to rehash it several times before I’m happy with what I get.
3. Next, think about what type of data is being presented. Will that mean you have to lay it out in a different way from how you first imagined? Sometimes your spreadsheet works better, for instance, with months across the top; sometimes, with months down the side.
4. Use as little formatting as possible. It adds unnecessary bulk to your file size, it can make it harder to read (I personally hate it when people overlay the perfectly useful grid with a black grid, it does my eyes in) and you can seriously waste time – especially, don’t get distracted with the pretty-pretty when you don’t have the time to waste in the exam.
5. Now, it’s worth thinking about the user. Will your users be good with Excel, will they be freaked out by your advanced formulae, would it actually work better to have a series of formulae which calculate in steps, so people can see how you’re thinking? Will you be able to remember what your formula does in six months?!
6. Should you restrict data entry, so accuracy is maintained? Think about no. 5 at this point – sometimes, restrictions have led users to tell me the spreadsheet is ‘broken’. I have been known to have a little note somewhere saying ‘Don’t panic, you will only be able to click into certain cells – this is to prevent accidental overwriting, it doesn’t mean it’s broken!’ just to reassure the beginner users! However, you can use in-cell drop down choices, password protection, cell locking and so on to guide people.
7. Design your spreadsheet with long-term use in mind. As an advanced user, I find it frustrating when the spreadsheets for y/e 2014, y/e 2015 and y/e 2016 are all slightly different just because the design’s not flexible enough – it means year-on-year comparison is harder than it should be. If your data accumulates by the month, you can actually have your formulae at the top. In this image, that’s what I’ve done: the formulae will have cell references like A4 to A100000 so there’s no possible way my data will get missed out. (Unless I really will have more than 100,000 rows, in which case… but I’m sure you get the idea!) I have also used Freeze Panes to allow for useful scrolling – see here for an explanation of what that is and how you do it.
8. Can you write any explanatory notes to help explain what you’re doing, such as colour-coding? I quite often have a final column at the end of all my data called Notes where I describe what the data is, where I got the data from, maybe who to contact for queries… anything to help out. I must admit I’m more pragmatic than considerate at this point: I’ve wasted way too many hours over the years fixing the problems users have created, so I try and make it as easy an experience for them as I can to save myself hassle further down the line!

Being consistent, being methodical and keeping it simple are often the ways to go with the best spreadsheets. Seek guidance from those you work with and learn from the messes other people make (heh!). I’ll leave you with two things: Firstly the ICAEW’s excellent Twenty Principles for Good Spreadsheet Practice (pdf), and secondly, the cautionary observation that as slick and trick as I am with my Excel, I have often got myself in a stupid tangle just simply because I’m being too clever. Don’t be a smart alec like me!

I’ve put together a little guide on what to do if you have to start working with a spreadsheet that someone else designed, which is cumbersome or irritating to use. Have a peep here if you’re so inclined – but it has nothing to do with the exam, so don’t worry about it if you’re here for studying only.

If you’re following the suggested Learning Plan, please click here to go to the next post.