When Excel is the perfect tool for the job and when it is not

Excel is a fantastic tool for a wide variety of tasks but, like all tools, it has its limitations. In this blog, we’ll cover those areas where Excel works well and those areas where there are better and more appropriate tools for the job.

To some extent, Excel has been the victim of its own success. It is estimated that over 750 million people use it worldwide and that popularity is down to the fact that it is both powerful and relatively easy to use.

It’s also broadly welcomed by IT departments who are generally happy to approve its use. However, this level of adoption and familiarity means that people are often tempted to use it for purposes for which it was not designed and for which better tools are available.

Much of our work with clients involves helping them to use Excel to maximum effect whilst also advising them when alternative tools may suit their needs better. We’ve compiled some of the common scenarios we encounter and what factors influence the choice of application.

Excelling at business, financial and data modelling

This is an area where Excel really does live up to its name. It is the perfect tool to model your business data structures and work processes. Excel allows you to create complex analytical tools more quickly and efficiently than anything comparable.

The beauty of using Excel for this purpose, is its transparency. It allows you to see the workings from the outset so you can use this information as a reference point before creating a system that offers less visibility of the calculations.

The speed and ease with which you can change formulas make it a great vehicle to explore different options allowing you to create and refine optimal models.

We often model systems for our clients in Excel. Then, once the interim system has been tested, proven and perfected, we can transfer it to a more secure cloud-based application.

Why using spreadsheets can pose problems for data security and integrity.

Excel is not ideal if multiple users require access simultaneously. Especially if you want to grant different levels of permission. While access to Excel worksheets and workbooks can be configured using controls and user permissions, these can be somewhat cumbersome to apply. Excel does have “shared workbook” functions, but these can often be buggy, difficult to manage and can actually restrict Excel’s functions.

Accidental edits and formula overwrites are a constant risk with multiple users. While native controls such as cell, worksheet and workbook protection will provide some safeguards, they cannot be entirely relied upon. You can configure your own permission system but, ultimately, anyone who knows what they are doing, and has the password, will be able to access all of the protected content of a workbook within a few seconds.

That’s why it is vital that you never share a workbook containing material you don’t want the recipient to see. Applying Murphy’s Law, you should assume that it’s not a matter of if the workbook falls into the wrong hands but when. And, of course, the more people share the workbook, the greater the risk of it going astray.

For real control, it is far safer to use specialised database software applications designed allow you to manage user permissions precisely and protect sensitive data in a fully secure location.

Singing off the same worksheet

Spreadsheets are just like hymn sheets. You want everyone working off the same one to ensure they perform in unison. However, the ease of copying, adapting and creating new Excel worksheets and workbooks makes it difficult to definitively control which version different people are using. This can be especially problematic as the functionality of your systems evolves and you need to be certain that everyone is using the most recent version.

Centrally controlled, cloud-based software is the obvious way to avoid the risk this poses to your business. It will guarantee that everyone in the organisation is consistently working with the same data and deploying the same functionality

A powerful reporting tool

While Excel has always been a useful tool to create spreadsheets for simple business records, the development of highly effective cloud-based accounting and book-keeping solutions, from the likes of Xero and Quickbooks , means that it simply doesn’t make sense to use it for this purpose anymore. Most of these applications can be integrated with bank accounts and receipt scanning (OCR) technology to automate data entry. They will also compile your basic accounting reports.

However, where they are limited, and where Excel comes back into its own, is delivering more specialised financial reporting. By extracting the data from these accounting systems, you can use Excel to undertake more detailed analyses and create bespoke reports and visual representations of the data. This is vital for companies with very specific business models and niche reporting requirements.

Financial reports aren’t the only ones that benefit from the sophisticated analysis and outputs that Excel can deliver. We have also worked with clients to create SCADA reports using Excel to generate engaging feedback formats.

Working in the field

More and more businesses are discovering the potential of handheld portable devices for their teams working off site and in the field. For remote operations such as these, Excel is far from the ideal tool. Your teams will find apps that have been designed specifically to work on mobile formats significantly easier to use. Having the data they generate held in the Cloud will also provide greater security and ensure that information can be updated in real time, giving you greater control and visibility.

Where Excel can be helpful on the ground, is in providing simple, adaptable tools such as ROI and quotation calculators for your sales teams.

Making data work harder

We’ve established that issues with security and integrity make Excel an unsuitable home for your accounting, CRM or other databases. Its strength lies in its unrivalled ability to analyse, model and present your data in sophisticated, thought-provoking and compelling ways. Add-ons developed using the power and versatility of Excel can transform your capabilities and help your business achieve its potential. To talk to us about how we can help you improve the systems driving your business, why not get in touch for a chat.

Recommended Posts