How to get Excel working for your business
Updated: Apr 24
Excel: Friend or Foe for your Business?
Wondering why your spreadsheets “don’t work”, leading you to flawed models, hours spent maintaining them, or reaching for the calculator to double-check your numbers?
"The glory of Excel? It's content created on the fly by users. The curse of Excel? It's content created on the fly by users."
Love it or loathe it, Excel is part of the MS Office suite and as such it is here to stay. Chances are, your IT team won't touch it with a barge pole and of, the rest of your staff, half are scared of it and half are popping out baby spreadsheets on a weekly basis. Personally, I'm a big fan, but shudder when I think about some of the trainwrecks I've seen businesses in thrall to, because of bad decisions or insufficient knowledge.
At its most glorious: Mastering the basic systems everyone uses – email, word processors and spreadsheet tools - is one of the single biggest productivity gains out there. It doesn’t have to involve time off work, paid training or a special project. Instead, this is the ideal area for self-directed, on-the-job learning …. As a problem comes up, work out how to solve it with the system at hand and you will know how next time. The more you know, the more aware you will become of the system’s functionality, and the better at harnessing its benefits.
When the curse strikes: Nowhere is a lack of basic system knowledge worse for productivity - and profits - than Excel. I’ve worked in companies where up to 20% of staff time is going into managing spreadsheets. Worse still, when this happens it’s almost always been a combination of 2 factors:
Failure Reason 1: The spreadsheet should never have been built in the first place
Or the curse of users not being able to control themselves. Where, as the result of impatience, lack of care, lack of priority or sudden shift in direction, one individual decided that the best (AKA The Quickest) solution was a new spreadsheet.
Imagine a scenario where there is actually a system managing the process (often an "enterprise solution" with hundreds of thousands of dollars invested in it). All the data is theoretically available directly from the system. But one or more of the following has happened:
a. Systems had not been properly set-up based on process or user experience
b. Systems had not been integrated, meaning data was fragmented
c. There had been no investment in reporting
d. There had been insufficient investment in training
As a result, someone loses patience, gets resentful or can't get the results they need. They lose faith in the system and create an ancillary spreadsheet, which then generates an ongoing requirement to update and maintain it. Years later, that spreadsheet runs the show, dual data entry is the bane of everyone's existence and the owners are left questioning why they ever invested in the official system.
Failure Reason 2: Whether or not the spreadsheet should have been created, it was badly done
Or the curse of users not knowing what they are doing. The ultimate problem with Excel is that, because it is ubiquitous, everyone is expected to be able to use it ... at some level. And, like the Emperor's new clothes in the legend, no-one wants to be the dummy who admits to now knowing how to do the basics in Excel. We don't expect someone to walk into a kitchen and be able to not only boil a kettle, but make a souffle or whip up a croquembouche. Why should we expect our new employee to have encountered all the basics in Excel - design, structure, formatting, formulas, look-ups, referencing, charts, modelling and more?
This is the typical user-created, user-maintained spreadsheet suffering from one or more – and, to be honest, often ALL – of several common flaws. All too often the "baby" of a manager, it is a time-guzzling machine delivering little to no value at high running cost.
What are the potential costs of your Bad Spreadsheet?
Before we plough into the top habits to avoid, think about the associated costs. A spreadsheet which becomes central to operations must be maintained day in, day in. If that costs an administrative staff member one hour a day, that equates to approximately $7,500 a year in time alone.
Add errors, misreporting, unproductive waiting time, bug fixing and lost data, and the true cost could easily be 5-10 times that!
Worse still, there is unlimited risk if the errors resulted in financial exposure or a legal issue. There are tales of large companies going out of business, or taking million dollar hits, as a direct result of spreadsheet issues.
Choose Wisely: Is Excel really the best option?
As with any tools, in particular user-regulated ones, there are pros and cons of using spreadsheets within business. Think carefully about the why, who and how before unleashing the power of Excel.
Spreadsheets certainly have their place. As a Business Analyst, I have used Excel to create some incredibly powerful data entry forms, checklists, logs, calculators, reporting suites and more. As a Change Manager I have used Excel to create powerful tactical tools to manage new processes in a dynamic way, to provide control and enable the perfect strategic system to be designed and implemented. However, there is a hidden cost, even to the most powerful and clever of spreadsheets.
At one firm, over a period of 3 years we created a beautifully branded bespoke quote form, which combined complex mathematical formulae with customer data and a system-originated price list, to allow any employee to create – in seconds – an accurate, quality Quote, Predicted Usage and ROI report for renewable energy systems. Later it was developed still further to comply immediately with government requirements for the Renewable Energy Buyback Scheme submissions. It was an awesome tool, that would have won many skeptics over to Excel ….. but I would estimate that the time to develop and maintain it over those 3 years would have been upwards of $30K, money that could have been invested in a quick prototype followed by robust system-based data entry and reports.
On the other hand, Excel is a perfect place to quickly, independently and flexibly create tactical solutions, some of which can have lasting value. At a bank I worked for, within a day I created a Stamp Duty Calculator which was still being used years later by teams there, with immediate benefits to reduced times, consistent branding, centralised data control and 0% calculation errors – that would have saved $1000’s each week, for a total of let’s say $400!
Think carefully before going down the Excel route; there may well be a better way. However, If you decide it is the right solution (or the right solution for Right Now), then there are a number of cardinal sins you need to avoid, if you want a tool that works for your business.
The Top 10 Bad Habits in Excel
Don’t even think of using Excel, if you or other users are committing any of these cardinal sins. It simply will not work!
The scary thing is, many of these will seem unthinkable to anyone familiar with Excel ..... but I have seen each and every one of these done on multiple occasions, and am prepared to add even more basic issues to this list over time!
Blank rows or columns, within tables (formatting using blank lines)
Mixing up rows and columns (e.g. data over 2 rows and multiple columns)
Dates - that aren't actually dates (Excel doesn't recognise 22.04.20)
Combining data (2+ pieces of information in 1 cell, e.g status & notes combined)
Free text data entry (e.g. for categories, where should be restricted)
Mixed references (e.g. Invoices referred to as both INV10001 and 10002)
Manual colour coding (it's nonsensical, stops filters ... and is plain ugly)
Data entry in many areas (e.g. using a different tab for each month)
We will release a series of articles on how to spot - and avoid - each of these bad habits. Over time, this list will become a series of hyperlinks so you can master your Excel problems.
What must you do as a business owner?
Ultimately, it is your business. You might have a staff member or team who is responsible for inputting, managing or reporting on the data. But YOU are responsible for the profitability and productivity across your business …. And poor Excel discipline will kill these things.
Tend your garden - weed and fertilize often
So, make sure you are across all Excel use …. Like a hawk! For some reason, people have a tendency to want to create spreadsheets, so they will keep popping up like weeds. And, like weeds, they're always easier to get rid of before they put down roots.
In your 1:1’s, get in the habit of asking, "Are you having to use any spreadsheets?" If so, do a quick “5 Why’s” exercise to get to the bottom of it. It will nearly always be due to either:
a process or system failing (and you need to start considering the business case for investing in a systemic solution vs a tactical Excel fix)
an individual foible, such as failure to release control (in which case you need to address as a leader, to get your staff member to recognise how their actions impact your results, and help them find the best version of themselves)
Agree if the spreadsheet should be a) abolished, b) a tactical tool / continued to bridge a period to a more permanent fix, or c) a longer-term / strategic solution.
If there are any new spreadsheets (that should be tactical or strategic tools), look at them to assess for any of the bad habits above.
If you see any, make sure you are across them enough to suggest “The Right Way” (or just share this article with the creator) and educate them to learn good Excel habits.
Any necessary spreadsheet will need to be fertilized and pruned. You may need to allocate training to the developer, create an internal working group, additional internal support resources or invest in outside expertise to build the best spreadsheet. Treating any approved spreadsheet seriously will force you to keep your eyes open as the true costs evolves.
Consider investing in training
In extreme cases, bad Excel habits (especially in managers) can be disastrous for profitability and productivity. In this case, you will need to train your people. This can be an external provider, or even one on one, or group sessions internally. Most people learn better one piece at a time – so consider doing one session on each of the Bad Habits above.
Often, you will find you have staff members who "get" Excel. Identify and invest in these people and you will have a quick way to bridge the gulf between business use and IT bias, as well as a rich internal source of training for other team members. Remember, it's a rare individual who is knowledgeable across all areas of capable, handling data modelling, referencing, design, user experience, automation and error-proofing with equal ease.
Acknowledge all approved spreadsheets as official systems
You must keep track of all processes, systems and templates. And that absolutely includes spreadsheets.
Ensure any spreadsheets are clearly linked on your process maps, as Manual Processes. This is important as they will be key areas for improvement or removal. Each spreadsheet needs an official Owner (not necessarily the key user(s), but quite possibly a team leader, to be accountable for the spreadsheet, including the cost of time allocated to maintenance, and how Excel integrates into your policies, processes and architecture.
Excel can be your best friend, or it can be your enemy. But, as the saying goes, "Keep your friends close and your enemies closer". For better or worse, Excel is here to stay and spreadsheets are part of every business. So you need to get on board. Embrace Excel. Train in it. Use it. Choose wisely Weed out the bad. Take active control.
Done right, Excel will be your friend. Done wrong, it could spell disaster.