When Should You Use Excel?
Aaahhhh, spreadsheets. In some individuals, the word alone is enough to inspire feelings of dread, loathing, and reluctant resignation. For others, spreadsheets instill a sense of organization, productivity, and satisfaction.
Okay, yes, I admit; I’m a sucker for spreadsheets. Sure, they can be a bit monotonous, but once you have every row and column appropriately filled and all the functions are in place, a well-crafted spreadsheet is a thing of beauty. But Microsoft Excel (or the free Google equivalent, Sheets), isn’t always the best option for your data management needs.
So when should you use Excel? When it was first designed, Excel’s spreadsheets were based on the paper spreadsheets used by accountants to keep track of finances. So you would think that using it for bookkeeping and accounting purposes seems reasonable, right?
If you’re a small business, sure. But for all of Excel’s boasting that it can support approximate 65,000 rows of data, it doesn’t truly scale all that well as businesses grow. 65,000 rows is a lot of data, after all, and sifting through it all is not only monotonous, but at that large of a scale, the likelihood of human errors is pretty high. Using accounting software can reduce the frequency of human error, and many automatically track who is making changes, so that if someone does make a mistake, you can pinpoint exactly who made the mistake, when they made the mistake, and where.
I never really understood Dilbert until I started working in an office job….
(Disclaimer: My boss is way smarter than Dilbert’s boss, and that’s why he’s in charge.)
Okay, maybe even small businesses shouldn’t be using Excel for accounting, especially when they don’t have someone on staff who is experienced with handling finances. Excel is relatively cheap to obtain, as is Google Sheets. If you can’t afford to spring for fancy software, what alternatives are there?
There are a variety of free accounting options available for the discerning small business user, but GnuCash is the one that I’m the most familiar with. It’s a great open source alternative to accounting software like Quickbooks that’s available across all platforms. It can feel a little fiddly at times, especially if you’ve never used accounting software before, but once you get used to it, it can be used to handle everything from employee expenses to client invoicing.
But what about data storage? I did mention that it can hold up to 65,000 rows of data, which is a lot of data. It’s enough to significantly impact your operating speed if you tried to actually open and run the file. Maybe it could be useful as a way to archive old data?
As it turns out, Microsoft Excel is NOT a database. Yes, it stores large quantities of data and yes, many database can export their tables as .csv files, but there is still a big difference between them. For one thing, databases are far more secure. For another, databases can store a wider variety of data, and are much better at linking datatables together. So when is it time to upgrade your spreadsheets to a database?
As you can see, GnuCash doesn’t look entirely dissimilar to a spreadsheet
If your spreadsheets aren’t in active use, and are primarily used for long-term storage, you should probably move them to a database.
If multiple people need simultaneous access to the spreadsheets, then you should probably upgrade to a database. Sharing technology has improved greatly over the past few years, but databases minimize the amount of hiccups that come from simultaneous use.
Is your data sensitive? Important to your daily operations? Would your business fail if you lost the data? Better move that spreadsheet to a database.
Okay, so if it’s not good for data storage, and it’s not good for accounting, just what the heck is Microsoft good for? In my opinion, there are three things that Excel does extremely well:
It’s great for visualizing data. Between the pivot tables, charts, graphs, and clustered columns, Excel excels at taking data and turning it useful visualizations that can add emphasis to business reports and marketing material
Conditional formatting highlights important data. In a decent sized company, spreadsheets can get pretty big pretty quickly, making it easy for data to get lost. Conditional formatting enables you to highlight rule violations, create and compare lists, and find duplicates or other discrepancies.
With important data highlighted and visualized in graphs, it’s much easier to identify trends. This can be used to make predictions about future outcomes, which can be then used to set sales quotas, adjust productivity metrics, and track the progress of a project.
With all that in mind, my personal opinion as an expertly trained Excel user is that it’s best used for generating specific reports, or just as a place to work out rough drafts. It’s a good way to get your data organized so that you can turn it into something flashy for presentations, whether that’s through Excel’s excellent selection of charts and graphs, or by importing it into a program with a little more oomph.
(I’ve also heard it’s great for generating knitting patterns.)