To Prevent Spreadsheet Mistakes and Misinterpretations

Sheffield (UK), February 2019 - While the use - and significance - of spreadsheets is growing, mistakes over their construction and interpretation are proving costly to industry on an almost daily basis. According to a recent report from Salesforce, customer relationship management (CRM) specialists, some 88% of spreadsheets contain errors. These are costing organisations billions of dollars due to miscalculations and misinterpretations of data.

So to help combat these mistakes and ensure that spreadsheets provide the value for which they’re intended, Sheffield Hallam University has developed visualization plug-in software called "Ensuring the Quality and Usability of Spreadsheets" (EQUS).

EQUS, which aims to enhance the accessibility and understanding of almost all spreadsheet formulae and the data with which they work, is distributed by VeryViz, a partnership between Sheffield Hallam University and the online learning and training consultancy Learning Light.

"EQUS enables improved user understanding as well as greater formula integrity of calculations made in spreadsheets," said Dr Chris Roast, of Sheffield Hallam University and EQUS’s technical lead. "We call it a 'Formulae Visualizer' because EQUS dynamically illustrates what’s happening with spreadsheet formulae."

Robustly tested by hundreds of users, EQUS works with equations of any size and complexity, as well as with referenced cells between different worksheets. Moreover, there’s no specified limit on the size of the equations that EQUS can visualise.

Contained in a downloadable file of less than 1mb, EQUS explains visually, via an on-screen diagram, how a formula in a spreadsheet is calculated. It presents a calculation’s result, along with all intermediate results, allowing anyone to check the logic behind each formula in a spreadsheet and to see if the correct operations are being used.

According to Learning Light director, David Patterson, "EQUS is a useful spreadsheet add-in, currently available for Microsoft Excel, which helps to ensure correct use of formulae by flagging potential issues and errors. Moreover, it shows this visually – making it easier to solve any errors that might occur."

Marc Zao-Sanders, CEO of adult education specialists Filtered commented, "Spreadsheeting errors are a problem and can cost companies huge sums - as well as present smaller issues that frustrate the world's one billion or more spreadsheet users every single day.

"For beginners, EQUS explains how formulae are put together and work. For advanced users, it can help visualise and debug complex formulae. In particular, EQUS could benefit teachers and trainers to demonstrate what spreadsheets can do," he added.

"As someone who’s worked extensively with Excel, EQUS is the sort of tool you wish had been developed a long time ago," said Andy Cottier, of Day One Technologies. "Faults and anomalies in Excel can sap productivity and confidence in business data, so EQUS should save users and consumers of Excel masses of time and money.

"And, in schools, Excel is a low-cost solution for modelling and learning subjects such as Maths and Physics. EQUS can help with that experience, particularly in helping students understand the concepts in a visual, accessible way."

Initially, EQUS is expected to prove popular in three main areas among the estimated 750 million spreadsheet users throughout the world:

  • educators using spreadsheets in Science, Technology, Engineering and Mathematics (STEM) and other numeracy-related subjects
  • trainers helping develop workforce numeracy skills
  • professional users of spreadsheets in businesses of any size

David Patterson commented, "VeryViz intends to create further useful tools for managing data and ensuring its integrity. We’ll also be signposting more tools and useful tips for using data, analytics, and spreadsheets.

"We welcome opportunities to collaborate on projects, as well as suggestions for additions and improvements to our website. So please contact us to start a discussion," he added.

Learning Light, VeryViz, and Sheffield Hallam University have collaborated on a further new product called SQLView. This project uses a similar approach to that of the EQUS project but in respect of the common database language SQL.