Enterprises have long been using Microsoft Excel to collect, store, analyse and report data, as well as run complex calculations and create models. And while overall Excel usage continues to grow, says Mike Nieburg, VP, business development at EASA, there has been a shift away from Excel to purpose-made applications for situations where the program’s more pressing problems limit its usefulness.
However, Excel remains one of the most powerful, flexible and agile tools, not to mention one that is immensely familiar to so many users. Getting rid of it, especially when your company has been using it for years, is not only daunting but often near impossible. Businesses can still benefit from using Excel as it is highly flexible and agile compared with other apps as long as they know how to do so effectively.
Most Excel users know how to work it in rudimentary terms, like inserting data and using basic formulae. But it’s important to understand that this is a powerful tool that can do more than calculate sums, with tons of complex features that can transform the way businesses use it.
Take PivotTables, for example, which allows you to summarise abnormally large datasets simply and visually. Assuming you’re utilising Excel for sales, you may have a long list of transactions, ranging in size, date, and companies. At some point, this list can get a bit overwhelming.
A PivotTable will simplify the process by breaking down this data based on any category for example, you can organise your data to show you invoices by due dates, quickly showing you which ones are late and enhancing your ability to produce reports without even using any formulas.
Another vital functionality is VBA, which stands for Visual Basic for Applications, a programming language that is task-driven, easy to learn and immensely worthwhile for businesses. VBA essentially lets you write instructions to create mini-applications that will be used within your spreadsheet, making it more automated and easier to work with. You can implement the many features of Excel in your application, and of course, use the data and calculations you’ve already set up.
The pitfalls when using excel and how to avoid them
Although Excel can be a fantastic tool, it has some inherent issues. Usually, these are completely manageable by applying best practices if you’re a private user, but on a business level, you’re going to need multiple people to access your spreadsheets, and that’s where the chaos begins.
Say you start with one Excel file, but three other colleagues then have to use the same spreadsheet. The program doesn’t have the capability to share documents simultaneously, which means you must send your file to your collaborators, with each of them then working on a separate spreadsheet.
You now have four different files, with no idea which one is most updated. It is possible to overcome this by building your spreadsheets on Google Sheets to begin with, however, their abilities are just not as good as the Microsoft alternative. It also doesn’t solve the issue if you already have Excel sheets set up.
Every user of your Excel file must have the program installed on their computer, but it has many different releases. If you created your spreadsheet in Excel 2019, it might not work exactly how you expect it on your colleague’s 2016 version. On an enterprise level, this can be avoided by ensuring everyone in the company uses the same updated software. However, if you need to share the file with external clients, for example, it’s going to be impossible to circumvent.
Sharing Excel files via unsecure methods like emails can expose you to cybercriminals. However, even if you keep everything offline, there’s still another problem. You’ve worked really hard on these formulae, so much so that they can be defined as your intellectual property. But by distributing the documents, the formulas become visible for everyone to see (and even edit, which can cause mayhem in your spreadsheet). There are some methods of hiding them, but unfortunately none are bulletproof.
Low-code platforms are the way forward
The only way to fully avoid all of these issues while still enabling end-users or non-software specialists to create their own applications is to invest in a low-code platform, as this lets you create purpose-made applications with little to no coding required. Such applications can eliminate the pitfalls of Excel, considering you can implement version control and have no need for spreadsheet software at all.
However, this in turn means you lose all the great advantages of Excel, and requires rebuilding all of your logic and formulae. Not only does this take time, it may not even be possible for every spreadsheet. Another alternative is giving EASA a go a usable web app that feeds from your original master Excel spreadsheets, enabling you to continue using them but with all the benefits of low code platforms just listed.
The author is Mike Nieburg, VP, business development at EASA.
Follow us and Comment on Twitter @TheEE_io