There is fair amount of good practices to follow when constructing a spreadsheet. Sticking to them would require a tiny bit of effort but will go a long way in helping you and all stakeholders in the future. Of course, following such guidelines is annoying at first but will quickly become natural to you so it won’t come in the way of your work. Also, I have never seen a XLS, even relatively small in size, that manages to perfectly implement all such advice. However, striving for such high work standards is what matters and eventually makes you great at practice.
Unless you have to do something “quick and dirty”, when timing is crucial, here are 3 principles to follow when crafting your beautiful files.
1.Consider who the user would be.
There are very few instances in which you will happen to be the sole user of your tables. Exchanging files with clients, both external and internal (your team is included here), is the norm. Therefore, you have to consider what kind of people would interact with your work and try to tailor it to their needs and expertise.
To make matters a little funnier, a single reporting cycle (of a month lets say) might be enough for you to forget how your spreadsheet was built and then having to spend significant time reverse-engineering or building from scratch. I have been there — the shame is even worse than the extra work 🙂
What you need to do:
- In case you don’t already know it, try to find out who the (end) user would be and how detailed you need to craft your work in order to be useful and comprehensible for them.
If the document is internal for your team chances are there is much smaller need of notes, abbreviation explanations or laying out steps in calculations — presumably you are all well aware what is going on. On the other hand, I have seen how other teams that I work closely with, get confused by something I consider straightforward and elementary.
- In case you don’t know who will be on the receiving end, simply assume they have very little (or absolutely no) knowledge of the subject and lack the time to trace figures in your work.
- Have you already interacted with the recipient? Can you recall what they asked you about last time? As enlightening as it was, you are not going to rely on them remembering the little exchange you had back then, right?
What you can expect in return:
- Less questions asked — People value your time but value much more their own, so if they can understand something there is little probability of getting back to you with questions.
- Slowly “teaching” people you regularly interact with what to expect and how to digest it. From establishing terms to adoption of calculation methodologies, I have witnessed how well defined and explained approaches can surface on top.
- Less interruptions — most importantly. Regardless of it being a call or a meeting, having less events that break your concentration is always good.
2. Build-in checks as you go.
With every error made we learn a bit more about the importance of checks. And the more of them you have, the better. What I am trying to do when working is to put tests, numerical or logical, for every piece of input AND for the results of the calculations. Then I will consolidate the checks somewhere I can have a birds view and try to make the spreadsheet signal to me as visibly as possible when something is not right.
Numerical checks are easy and when it comes to inputs — usually finite in number.
- For example, you might have input that is a percentage split of categories — do they make 100% when put together? Arguably, if the inputs are separated (different file with appropriate checks or DB that is populated after validation), you can skip this in the core model. However, I have seen people going far in their efforts to change the working of a source template so I would try implement at least a handful of tests when transferring the data from the original source.
- Once you start crunching the figures, endless number of reconciliation checks can be made. Focus on the most important as usually they are sufficient to indicate if there is a problem. However, if your model is going to be extensively used, having checks that pin-point issues makes sense.
It is always the case of countless logical checks that can be performed. Once again the more you do, the better. This may vary from simple negative number check (eg. you may want to watch if the cash position in the BS turns negative) to much more sophisticated tests. Either way, the presence of sanity checks has proven to me as way to mitigate embarrassing errors.
Once a check does not pass I will try to make it as obvious as possible. Even though I am not a fan of conditional formatting (this I will cover another time), I don’t hesitate using it in this case. Have you ever seen someone masking error in a cell with white background by making the text color white as well? You don’t want to be this person. Not only because of the epic facepalm this attitude represents but also because it will all come back to you once searching the root of the issue that will certainly pop-up later.
Provided the time, I will consolidate the checks or types of checks from the entire file on a single sheet where I will easily have overview if everything seems to be running fine. I call the sheet the not-very-descriptive Huston because… a cliché.
3.No hard inputs in the formulas
This might seem as a misstep that only a rookie would do but it is not. Typing an exchange rate, ratio or other value right into the formula text is done by even the most experienced professionals. Sometimes I find myself doing it as well.
Consider the following situation — For more than 20 years in the country I live in (Bulgaria) the local currency (BGN) has been pegged to the Euro at a fixed exchange rate (1 EUR = 1.95583 BGN). As you can expect after such a long period of time everyone has become so used to the figure that multiplying by it now feels normal. This is so because unless in the event of a serious economic event, the fixing can be expected to remain unchanged. However, as would tell you the head of the ALM department of a bank I worked for — this is a risk often forgotten but far from completely absent. Now imagine such rate event materializes — not only you will have to deal with the emergency of much different economic environment but also spend the time searching, replacing and checking all your hard inputs with the new value. Had you followed a good advice, you were likely to change a single value.
In business contract terms, targets and other key inputs change and models change with them. Masking variables in the formula bar instead of having them input separately is a recipe for disaster. Unless you want to make your spreadsheet harder in terms of traceability and maintenance, take the very few moments to build appropriate references.
Following those rules has helped me be productive by making the Excel files more friendly to both me and everyone I share them with. It is sometimes a hassle to stick with such guidelines but it is well worth at the end when you confidently take a look at your creation.