Stop using Excel for everything, just stop it.
Excel needs to be lead round to the back of the stable block and put out of its misery.
Don’t get me wrong, Excel is an amazing piece of software. Many a fantastic innovation has started life as a spreadsheet, but so many people are comfortable and proficient with Excel that it gets used for things it simply isn’t good for.
Businesses get stuck with systems and processes that depend on spreadsheets that someone built years ago. That person has either left or forgotten how it all works, and now it’s all getting slower and slower as more and more rows get added. More and more versions of the spreadsheet are having to be maintained for different people and different software. There’s an excel plugin in there somewhere that’s no longer supported and the next big version update is going to bring the whole thing down in a heap.
“If your only tool is a hammer then every problem looks like a nail.”
This quote (probably wrongly) attributed to Mark Twain sums up the problem nicely; if you’re not a software person but you can do a pivot table then you will naturally reach for Excel whenever you have a problem to solve. And that’s fine. I use Excel to deal with one off tasks and prototyping but if you need to deploy a solution beyond your own desktop then stop.
What happens all too often is that the company’s Excel ninja puts together something that does the job; but then as the business tries to scale the solution to more users or more data things go pear shaped.
Excel Is Not a Database
Spreadsheets really aren’t a good place to keep any significant amount of data, you can’t denormalise it properly and the relational capabilities of Excel are basic. Officially, Excel can handle just over a million rows of data, but unless you’ve got a seriously powerful computer you’ll start seeing performance issues at less than a 10th of that, especially if you’ve got formulae.
Use Excel to get your data structure right and then get someone to build you a database.
Excel is Not a Data Visualisation Tool
A freelance friend of mine got asked recently to work on a project to produce “advanced BI dashboards” in Excel. He said no. If you want “advanced BI dashboards”, or any sort of dashboard for that matter look beyond Excel. Yes, Excel will produce graphs and pie charts etc, but sharing the dashboard becomes a nuisance. Emailing people a new version of a dashboard every few days isn’t ideal; what if you’re not there to send it? What if it they open an old version by mistake? What if they accidently make an edit and end up looking at a different set of numbers to everyone else?
A single source of truth – preferably a database – feeding data to a single online data visualisation that all your users can view is a much nicer solution. What’s more, this stuff isn’t anywhere near as complex or expensive as it once was. Google Data Studio is free and will let you produce professional dashboards from a wide variety of data sources and share them with the rest of your organisation. Or if you really want to smash it out of the park then spend the money on Tableau. No, it’s not cheap but it’s awesome.
If it’s a place to keep data that you need then there’s a million and one options that you might consider depending on what type of data you’ve got. If it’s customer data then use a CRM system, if it’s product data then use a proper PIM like Akeneo.
If you really must use a spreadsheet then consider Google Sheets, at least you can share a single version with all your colleagues and it will connect seamlessly to Data Studio.
Excel has its place, but know where that place is and more importantly where it isn’t.