How to quickly identify and prevent spreadsheet errors

Excel is a powerful tool, the ubiquitous Swiss Army knife of everyday data analysis and transformation. Although limited user skills may result in doing only basic work, it can be used to drive entire business applications. Excel spreadsheet formulas are programming logic, and models often contain Visual Basic for Applications code which automates their operation further. They are commonly created by people who are not software professionals, and so typically do not follow safe development practices. Yet the users trust their spreadsheets enough to use them to inform business-critical decisions and reporting. This session provides both anecdotal horror stories and formal metrics to illustrate the real extent of errors in untamed spreadsheet, such as the mathematical error in a Value-at-Risk model that masked a JP Morgan $2 Bn loss; or the automation control failure in Public Health England that prevented a timely recognition of 16,000 Covid test results and arguably contributed to hospitalisations and deaths. The session shares how professional spreadsheet auditors and testers find defects. It covers how to assess risk and complexity in the context of spreadsheet criticality, and a process of high level and detailed reviews. It gives tips on how to notice the code smell of the most common defects, before the user has become aware of the defect materializing in a failure. A free add-in will be provided for attendees to do what is probably the most useful first-responder test for a spreadsheet - formula consistency highlighting. A 30 day evaluation is also available of the XLTest spreadsheet audit add-in.