Ah, Microsoft Excel. As professionals, we really can’t live without it. Sadly, despite the myriad benefits this nifty software holds, its disadvantages can also drive us insane — many of us will remember the nearly 16,000 COVID cases that went unreported in England due to an Excel error. Whether your spreadsheet usage is as critical as an entire country’s pandemic control or not, here are a few mistakes you should never make while using Excel and why.
1) Fail to protect your spreadsheets
If you’re merely making a shopping list on Excel you can skip this, but more likely than not, in business use, your spreadsheets host some important information. Be it intellectual property in the form of complex equations that can give a real boost to your competition, or sensitive data about clients and employees, it could be devastating — and sometimes even illegal — if it were to be leaked.
Making copies of your files and placing them in insecure locations is a known pitfall of the software, and should be avoided. Emails are also a security risk, as they can be easily penetrated. Luckily, MS Excel allows users to password-protect workbooks, which can act as a first line of defence against breaches of the sort. You can also copyright your data within a spreadsheet which can be useful in some cases. However, both of these solutions provide minimal protection, potentially leading to hours of trying to protect spreadsheets “only to be easily defeated by a few simple hacks,” as the experts at EASA put it.
If you truly want to protect the data on your spreadsheet, you’d have to invest in another software. In case you’re prepared to let go of the use of spreadsheets altogether, a low-code programme might be the best solution. Unfortunately, if you have any macros or VBA in your spreadsheet, this will not be an option for you. EASA, on the other hand, allows you to continue using your spreadsheet (complete with macros or VBAs) through an online interface that can be tied into your internal authentication system, adding another layer of protection.
2) Overwrite existing data
We cannot think of anything more frustrating than working hard on a spreadsheet just for it to be violated by unwanted changes. Whether it’s colleagues playing around with it or even someone’s pet stepping over their keyboard, hours of your time and energy could dissipate as quickly as you can say ‘Microsoft Excel’. What’s more, it’s not just about outside influence — who hasn’t accidentally overwritten important data when trying to adjust a spreadsheet?
Beyond mistakes, some practices can also cause you to overwrite data you may need in the future. For example, you regularly record your sales for the week in a spreadsheet by deleting the data for the previous week and reinserting the updated information. Although this may seem reasonable, you never know when you’ll need to pull these figures again, so it’s best to abstain from overwriting data at all costs. To avoid losing important information, complex formulae or meticulous filters every time you want to edit something about your spreadsheet, simply duplicate it and work on the copy.
However, when it comes to other stakeholders who need access to your workbook, it may be worthwhile to lock the most important cells (or the entire file altogether). If you have a particularly important spreadsheet, though, save it as an .xltm file — this is an Excel template format that will force users to save any changes into a new document. Another way is to convert your spreadsheet into a web app, which will enable data input without overwriting.
3) Keep the default settings
Speaking of templates, it stands to reason that your business has certain elements of your spreadsheets that need to be reused over and over again. While Excel defaults are really useful, and can be more than enough for most users, experienced professionals can be hindered by sticking to the basics. Templates aren’t just for protecting your workbooks from pesky changes — they also save you the energy it takes to reset your spreadsheet from scratch every single time.
It’s very simple: open a new workbook, amend it according to your needs (for the last time!), and save it as a template. Now, you’ll just need to open this instead of a fresh new spreadsheet with the default settings. This is so easy it may seem silly, but it can save you hours on hours overall.
Before you do this, though, it may be a good idea to refresh your memory on the plethora of features Excel allows you to adjust. From the number of sheets in a workbook to cursor control, there is a whole arsenal of alternative settings that can replace the default and help you make the most out of your documents, time and time again.