Spreadsheets – when the right formula is the wrong formula

That’s an awesome spreadsheet but how does it work?!

If you work in finance, or almost any other office based role, you will have most likely, at some point, ‘inherited’ a spreadsheet which is an integral part of a key business process. The spreadsheet does some cool stuff and it’s obvious that a good deal of thought went in to it: this is good, spreadsheets are supposed to be labour-saving, but what happens when it breaks and you realise that it uses formulae and functions which you’re not familiar with?

It’s not about you

This is what you need to keep at the front of your mind when developing spreadsheets for your team or clients. Think, “If I’m not around to support it, will they have the skills to do so?”. If you get this wrong, you could end up creating the most cutting-edge, super slick spreadsheet, but as soon as it hits a small problem and you’re not there, it gets abandoned and the team revert to manual processes. And even if you are still there, you may not want to be forever known as the ‘spreadsheet support person’ for the rest of your time in that organisation!

When the right formula is the wrong formula…

Creating a ‘sustainable’ spreadsheet that can be supported in the future often requires a balanced approach because the ‘right’ formula/function from an Excel efficiency perspective, may not always be the right formula/function for the team who will have to support it. Using the occasional formula or function that may be new to others isn’t always a bad thing; it can streamline a significant part of the processing and it may give any future end-user a cool new thing to learn. Just don’t get carried away as it can be all too easy to find a host of new formulae, functions and techniques and want to squeeze them ALL into your masterpiece. That’s when you create a spreadsheet with a potentially limited life-span!

Golden rules

So when building spreadsheets that will be used by others, and before your inner-geek gets carried away with the excitement of discovering and mastering new techniques, remember the following:

• Know your ‘audience’ - Identify the level of spreadsheet skills that the team/users you’re building it for have and develop it for them, not you.
• Document - If you use a new/unusual/funky technique, add notes in a separate tab (worksheet) in the workbook that explain just enough about it.
• Knowledge transfer - Do a run-through with the team/users so that they know how to use it and support it (then add any questions they raise, and your answers, to the ‘notes’ tab).