Excel - Keeping Things Simple

When I started in my career as a finance administrator I was a "user" of spreadsheets, following processes that had been set out by senior managers. I plugged numbers in, I recorded what I needed to record and that was that. It was a great experience and gave me a foot in the door with a great employer, it was a gentle step into the crazy world of the regulated energy industry in the UK. Not long after starting my first role did I begin to see inefficiencies in processes and potential for human error. It was at this point I began to tinker with Excel and begin a journey of discovery. Up to this point, the extent of my Exel knowledge was what I learned in high school.

Not long after starting my first role did I begin to see inefficiencies in processes and potential for human error

My tinkering led me to create all sorts of complex spreadsheets with array formulae, PivotTables on every tab, throw in a macro or two that would automatically copy and paste the contents of other workbooks and I would be satisfied with my work.

It wasn't until I started to move away from the deep detail and started to communicate with those that needed the data did I realise that I needed to reevaluate what I considered a "good" spreadsheet. I was starting to have problems with spreadsheets that I had left abandoned in a handover document from a previous role. These types of problems can lead to incorrect spreadsheets, even recently BBC News covered a story about a spreadsheet error that delayed the opening of a hospital.

So where am I getting with all this? I guess it's to say this:- KEEP IT SIMPLE. In recent years I've realised that these wonderfully complex spreadsheets are prone to breaking, they do not lend themselves to collaboration and are a nightmare to maintain. My philosophy now is to seek the simplest solution to complex problems.

Just because you have the skills to do something, it doesn't make it the best solution.

With that in mind, I've put together the following list of good practices:-

1. Plan: Think about data flows in the spreadsheet. Are you creating a MI dashboard? If so put effort into designing something that looks elegant and easy to understand and then think about how inputs can feed into outputs, avoid writing out too many formulas until you've planned the mechanics of the workbook.
2. Document: This is particularly true of bigger projects with a team but also individual projects. Consider drawing a quick diagram of how data flows, highlight what is an input, what is a calculated field. Perhaps include a data dictionary for tables, add comments. These things can be so helpful when it comes to sending the spreadsheet to a colleague or handing a piece of work over. If there is a process map or local working procedure document then consider updating it with a summary on how to use the spreadsheet.
3. Continuously Improve: There is always room for improvement and hopefully, you will be learning things in Excel all of the time. Have you recently learned how to use INDEX (MATCH) instead of VLOOKUP, swap out the functions and see the improvement in processing time.

Quick Sidenote on Macros

I'm not saying there isn't a place for macros, I recently developed a tool that extracts payment data from a messy table of data and converts it into a much neater and well-formatted dashboard, specifically so senior managers can approve payments to be made to customers. Just because you have the skills to do something, it doesn't make it the best solution. Before even considering a macro consider the following:-

• How long will it take to develop the macro? How does this compare to doing the task manually and how many times is the task being repeated. Hopefully, this is obvious but if it takes a 38 hour week to develop a macro that saves 10 minutes each week then it would take 228 weeks to get a return on that development time. In this case, it probably wasn't worth it.
• Who is going to be using the file, is it a small cohort of colleagues, are they well-skilled, if something were to go wrong would they be able to debug the errors?
• Your company's security policy. Some businesses do not like macros being enabled at all and will prevent this being an option.

Final Thoughts

Before rushing into development, stop and take at a look at the overall scope of the project. Don't be afraid to redevelop early on and test a few ideas, once you've pinned down what works then expand it to cover the entire problem.

What are your thoughts? Do we analysts tend to overcomplicate things? Does anyone have any stories of a spreadsheet being more complicated than it needs to be?