Microsoft Excel (and its clones like Google Sheets) is both one of the greatest pieces of software ever created, and one of the most dangerous. There are a huge number of use cases for spreadsheeting software, but not all of them are a good idea. I’d like to go over some of the most common ones, dividing them into the ones where Excel really shines, and ones where spreadsheets are best avoided!
Where Excel Can Work Well
When Excel was being developed, back in the 1980s, one use case was at the forefront of the product team’s minds: financial analysis. Excel was built from the ground up to support serious financial calculations for the banking and investment industries. If you want to know how long it will take to pay off your mortgage, or what the ROI from some business decision will be, you open up Excel.
Quick Lists and Notes
The second (and probably most common) use for spreadsheets is for keeping quick lists with multiple data points that might be kept for a few weeks while a project is ongoing. For example:
- Tracking the small renovation/maintenance jobs in a new house, which room they appear in, and whether they’ve been completed.
- Listing options for renewing your businesses insurance policy.
- Collecting responses to a survey.
If you need to chart some data, there’s nothing that beats the speed and ease of use that Excel provides. It’s perfect for visualising data of all sorts, no matter where it came from. Which is easier to understand – a list of 20 numbers, each subtly different, or a single line chart? This graphing capability, combined with the financial analysis functions built into Excel, makes spreadsheets the powerhouse program for ad-hoc reporting.
Where Excel Might Be Ok
Whilst Excel is great for ad-hoc reporting and graphing, if you’re generating the same report every month (or even every week or day!) then there’s a better tool for the job. Whilst you can use Excel templates to accomplish this goal, it’s clunky and prone to time-consuming errors.
What should you use instead? A common enterprise solution is Microsoft Power BI, a business intelligence and data visualisation tool perfect for reporting on organisational data. There are also various other tools ranging from free online tools to powerful open-source tools. The only downside to these tools is that they require a larger upfront time investment to learn and build the necessary reports. However, this investment is paid back very quickly in most cases.
If you’d rather not spend your own time building routine reporting tools and dashboards for your business, this is a service we’ve been offering to our clients for over five years now.
If you have one person in your organisation looking after a permanent store of data within Excel, then you essentially have a single-user database. Excel works fine for this for the most part. There are however some dangerous issues that it pays to be aware of:
- Excel doesn’t easily link pieces of data together like a normal database would. For example, if you’re tracking addresses, you might see the suburb of Mornington. In Australia there are five suburbs named Mornington, so you’ll need to track the state and postcode to get a unique match. Excel makes it more difficult to treat these three attributes as a unique identifier than a purpose-designed database would.
- Excel doesn’t prevent against copy-and-paste mistakes which destroy data integrity.
- There’s no auditing of user actions. Track changes does some of the job, but not all.
- It’s incredibly easy to email somebody a copy of an entire organisation’s most important data. This might be a great thing – or it could be the end of your business as you know it.
Proof of Concepts
Another good use case for Excel is as a testbed for another system. You might be looking to implement a customer relationship management system, but aren’t sure what data your business needs to track. You could begin by using Excel (taking into account the points about single-user databases found above) and making modifications to the structure on the fly. Did you just decide to record the client’s ABN? Add a column for it! Need to track the last time they sent you a paper letter for some reason? Add another column!
Building the testbed for your permanent database this way allows you to develop in a more agile manner. You can gather requirements on the fly before handing them over to the team that will implement the full-featured version.
Just remember to actually implement it!
When To Avoid Excel
The Multi-User Database
There’s one nightmare scenario for Excel that should be avoided at all costs. That is the multi-user, multi-table database. What does this mean? Let’s break it down…
- Multi-user means that multiple people are accessing the Excel file, usually over a shared network. Whilst Office now handles multiple people reading and writing to a spreadsheet at the same time, it’s definitely an indicator that it’s time to upgrade to something that handles multiple users properly, including better data security and permissions handling.
- Multi-table means that you have multiple worksheets (called tables in database terminology), each tracking different objects (for example, invoices, clients, and products).
- A database means that it’s a permanent store of information for the business, rather than exported data being used for analysis. One uncaught copy and paste error means a potential permanent loss of data for your business.
Why are these things a problem? Simply put, it’s because Excel wasn’t designed to handle them. There are almost always better tools for the job that will provide an easier to use interface and better data integrity. It’s rare these days that there isn’t already pre-built software out there that will handle at least 90% of what your Excel ‘database’ is doing, and customisation (which we can help you with) will get you that last 10% for the perfect system.
What do you do with Excel? Do you feel like it works, or do you wonder if there’s a better tool? Let us know in the comments, we’d love to hear about it!