David Parmenter urges finance teams to replace large error-prone spreadsheets.

Spreadsheets have no place in forecasting, budgeting and reporting routines. I believe it is unprofessional and career limiting to be overseeing a finance team that relies on large error-prone spreadsheets.

A spreadsheet is a great tool for creating static graphs for a report or designing and testing a reporting template. It is not, and should never be, a building block for your organisation’s reporting, forecasting or planning systems. The high level of errors in spreadsheets is the main reason why. A major accounting firm pointed out that there is a 90% chance of a logic error for every 150 rows in a workbook.

If you can build a forecasting, reporting or planning model in a spreadsheet application and keep it within 100 rows, you can do so without much risk. Pass this threshold and you expose yourself, your finance team and the organisation to unnecessary risks.

Forecasting requires a robust tool; not a spreadsheet built by an innovative accountant that now no one can understand. I always ask in workshops: “Who has a massive spreadsheet written by someone else that makes you pray before you use it?” You can see the pain in the instant response. Most people know that the person who built the spreadsheet certainly was not trained in operational systems design. The workbook will be a collage of evolving logic that only the originator has a chance to understand.

The finance team’s reluctance to divorce itself from spreadsheets is often a hindrance. It has been a long and comfortable marriage, albeit one that has limited the finance team’s performance.

Senior management is often unaware of the risks associated with relying on information from large spreadsheets. There are some common problems. An individual might alter a row or column, so that, when a group of spreadsheets is rolled up, the master spreadsheet is taking the wrong number from the one that was modified. Spreadsheets may lock up or show a screen full of “REF” errors, because Excel was not designed to be a tool for handling a roll-up of dozens of different worksheets.

The input of an incorrect number can happen in any process, but spreadsheet-based systems often require the rekeying of information, which can produce data inconsistencies. Spreadsheets might also use an out of date look-up table or an entry might have been inadvertently or mistakenly overwritten.

Incorrect formulas could lead to subtotals omitting one or more rows, columns or both. Individuals may overwrite a formula because they believe theirs is more accurate. What’s more, the absence of version control can lead to use of outdated spreadsheets – a very common mistake.

Many people in businesses can use spreadsheets to create alternative versions of the truth. I once visited a steel mill to find that every management accountant had their own cost estimate for a tonne of steel.

As a corporate accountant, being an expert at Excel will show up on your CV in neon lights that you are a technical dinosaur; one who has not embraced modern tools or does not understand the risks in running core financial systems with a high-risk tool. So what is the way forward?

First, you should log all spreadsheets used by the finance team that are over 100 rows and set a retirement date. Then, ascertain the tools that are available that work well with your general ledger and visit sites that use them. Finally, make sure all members of finance understand the weaknesses of spreadsheets.

For Leo Waldock, the complexity of Excel and the entire Office suite was too much.

I used Microsoft Office and Excel through versions 97 to 2003, and stopped when Office 2003 broke in a terminal way. I made some changes to the hardware inside my PC, which made Windows believe I had copied the software to a second PC. Windows XP reactivated without any issues, but Office 2003 refused to play ball.

Eventually, I moved from Office to OpenOffice and then LibreOffice, which included spreadsheet software called Calc. I had no problem accessing .xls and .doc files with any of these three suites, but it might have been a different story had I used .xml format.

With Open Office and LibreOffice, I avoid the native file formats of .odt and .ods, which mean nothing to the mainstream Excel user. Unfortunately, this compatibility matters for team working, so, even if Calc supported features such as pivot charts, members of your team will rely on an Excel feature that works differently. The smart move for anyone dealing with the outside world is to stick to Excel, purely to make their life easy.

This is a crying shame as LibreOffice offers the usual security features, and benefits from being immune to visual basic for applications (VBA) macros. For comparison, Excel is based on at least 10 million lines of VBA and can run macros to automate tedious tasks, and also, malicious macros designed to attack Excel.

Using Calc is a safer and more secure alternative to Excel, and the fact it is free of charge is a very handy bonus.

For Andy Cotgreave, Excel is yesterday’s news.

For data exploration and dashboards, I think Excel is dying. When creating and maintaining dashboards, or individual charts, you need to be able to explore and iterate rapidly.

As we show in The Big Book of Dashboards, modern businesses need powerful features built into simple designs. Unfortunately for Excel, other tools that enable faster, more effective dashboard creation exist.

It is possible to build great charts and dashboards in Excel but you need to know the end goal before you start; that approach is outdated and prevents any creativity coming into the design process.

I still use Excel for manipulation of small amounts of data and ad-hoc data sets. I don’t think this aspect will die. My expectation for Excel is that – despite a huge amount of functionality – the majority of users will use fewer parts of the application over time.

Excel maestro Simon Hurst says it’s the users, not the software, that are the problem

The government’s Making Tax Digital plans have provoked plenty of debate. Faintly audible alongside various howls of anguish and protest were the sounds of some accounting software suppliers rubbing their hands in glee at the prospect of the government giving them a weapon that would at long last lance the suppurating spreadsheet boil. Such hopes were largely dashed by subsequent clarifications that suggested spreadsheet use might not actually be about to be made illegal with immediate effect.

Debates around whether the nails fixing Excel to its dominant perch are about to completely rust through are not new. Many software products have promised delivery from spreadsheet hell. Policy and legislative changes, such as the Sarbanes-Oxley Act, have also threatened to provoke a major reaction against spreadsheet use. As if all of this wasn’t ominous enough, few months go by without some spreadsheet calamity making headline news and provoking calls for spreadsheets to be outlawed. In fairness, many of these errors have less to do with spreadsheets and more to do with spreadsheet competence.

Despite these pressures, spreadsheets – and Excel in particular – seem to be showing the resilience and omnipresence of the common cold. Given the importance of accuracy and productivity in accounting and finance, the dominance of a tool with a reputation for error and inefficiency seems odd.

There are several reasons why spreadsheets continue to cling to life so effectively. Like some sort of invasive weed, spreadsheet tendrils spread silently and unseen throughout a business. Those businesses that audit the spreadsheet files in their organisation are often astonished at the number of unexpected spreadsheets that they come across and the range of uses to which they are put. Trying to exterminate something that winds so tightly and deeply throughout your business processes is never going to be easy.

Spreadsheets also lack inherent structure. This, combined with the reluctance of most users to document their stream of consciousness, often leaves organisations with spreadsheets that have evolved over time like vast country houses. Extended by multiple owners over many years; no one now really understands how they were put together and where all the secret passages are. It’s very difficult to replace such spreadsheets and maintain complete confidence that the replacement does exactly what the original did. This often leads to a decision to leave something alone that appears to be working well.

When it comes to increased regulation, most of us don’t react favourably. We usually relish the opportunity to exercise our creativity and inventiveness. Immersing ourselves in the infinite pool of billions of spreadsheet cells, each with the potential of carrying out any calculation or delivering a key result with devastating impact, can seem like a wonderful release from ticking boxes and filling forms. This might explain the almost fanatical defence of spreadsheets that challenges often provoke. As other applications try to reduce the risks associated with using spreadsheets, they usually also destroy the flexibility that makes spreadsheets so appealing.

Most people think they know how to use spreadsheets. From school onwards, we use spreadsheets for keeping lists, fantasy football analysis and creating knitting patterns. Spreadsheets are perceived as being available for free – both in terms of financial cost and investment in knowledge and training. It probably isn’t surprising that finance professionals continue to reach for spreadsheets like Swiss soldiers reach for penknives.

Health professionals and the government are keen to remind us that the greatest chances of improving our own longevity come from making lifestyle changes. The same is true of Excel; if we continue to take Excel for granted and fail to look after it properly, its ability to support us in the future will be compromised.

An appropriate level of spreadsheet expertise will help to ensure that we use Excel for what it’s good at and avoid using it in areas where it becomes a liability. In whichever way that you use Excel, unless you achieve an appropriate level of Excel competence for your role, you are likely to waste a significant proportion of your working life through basic inefficiencies. You might also be exposing yourself and your organisation to significant risk of error and compromising Excel’s reputation.

To ensure a healthy future for Excel, and your own prosperity, it is vital to put in some effort and consider keeping in regular training. The ICAEW Spreadsheet Competency Framework is a good guide to the training plan you might need to set yourself.

If you are to be sure of always being able to adopt the best, and most efficient, solution for each new project, then it is also important that your training regime recognises the speed with which Excel is changing.

Being aware of the most recent changes in Excel might lead you to the conclusion that the spreadsheet is already dead. Excel is certainly no longer just a spreadsheet, but it is now some sort of spreadsheet-database-business intelligence hybrid. The BI tools that form an integral part of the latest version of Excel make the use of cell-based formulae unnecessary and allow Excel to be used for data analysis and presentation, rather than just calculation and reporting. The spreadsheet is rightly being challenged across a range of applications for which there are much more robust and efficient solutions but, far from being dead, Excel is about to embark on a whole new life. Understand what it can now do and be part of its renaissance, rather than its demise.

David Lyford-Smith on the enduring appeal of spreadsheets

It’s practically a cliché to say that Excel is obsolete, or that spreadsheets are going to die out. Almost since the electronic spreadsheet was invented, the end of its time has been foreseen; and yet, here we are, still making spreadsheets today.

Why does the tool have such enduring appeal? It certainly has an amount of inertia behind it – many critical systems were built on spreadsheets and changing them is thought to be infeasible – but really it’s about the power that spreadsheets have to get data into the hands of the user. Much more accessible than formal programming languages – and make no mistake, Excel is a programming language – spreadsheets are the great equaliser, putting the power of analysis, summary and change into the hands of millions of users. Dedicated software packages simply don’t have the widespread adoption that Excel enjoys, nor the easily understood and flexible feature set.

None of this makes the common criticisms of spreadsheets untrue. There are certainly plenty of horror stories out there, and although research shows that a small percentage of all cells are erroneous – leading to over 90% of workbooks containing at least one error – the sheer popularity and power of Excel makes getting rid of it improbable. The best path forward is to increase awareness and understanding of spreadsheet risk, introducing best-practice guidance on how to use spreadsheet packages, and ensuring that spreadsheet-reliant roles recruit and train staff with the appropriate spreadsheet skills.

The ICAEW Excel Community has produced two papers in this area – a best practice guide, Twenty principles for good spreadsheet practice and the Spreadsheet competency framework. I encourage every spreadsheet-using organisation to consider how they are tackling the problem of spreadsheet risk.

• Is Excel bad for you?
  Business & Management Magazine, Issue 260, December 2017

The ICAEW Library & Information Service provides full text access to leading business, finance and management journals and a selection of key eBooks from leading publishers. Further reading on the  pros and cons of Excel is available through the resources below.