Spreadsheets are everywhere in the corporate world. They have become a standard tool for a variety of professions for tasks like calculating a budget or predicting risks. Hence, multi-million decisions rely on the correctness of these spreadsheets.

Yet, a spreadsheet is not just an innocent document as a Word document. It is essentially a program. And as such, when defects can have big consequences, we need to treat their quality as we would with any other software. The problem is: Spreadsheets are usually built by end-users not professional software engineers. They don't know and probably don't really care about software quality assurance. So how can we approach this problem?

My doctoral student Daniel Kulesz is working in his doctoral project towards an approach and tool that can help spreadsheet users in protecting the semantic correctness of their spreadsheets. He called it Spreadsheet Guardian. The main ideas are the following:

Let's bring over the ideas of static analysis and testing from conventional software development to spreadsheets.
To not overwhelm the normal spreadsheet user, distinguish more advanced users that specify test rules and common users that just run the tests to see if they broke something.

In Spreadsheet Guardian, as an advanced user, you specify a test rule. This can be, for example, if in cell A3 is the number 213, then in A4, there should be the number 426. If other people use and start changing the spreadsheet, as soon as they break this rule, they are notified by Spreadsheet Guardian. This ensures that they do not inadvertently break the spreadsheet.

There is an open-source implementation of Spreadsheet Guardian in the form of a plugin for Microsoft Excel called Spreadsheet Inspection Framework. It allows you to specify and execute test rules.

To evaluate whether this approach is really helpful for spreadsheet users, we conducted a control experiment with 48 participants. We found that the vast majority of the participants were able to specify test rules correctly after two short tutorials. We did not see a clear improvement of correctness in our example when using the Spreadsheet Guardian approach. We noticed however, that users were a lot less suffering from overconfidence in the correctness of their spreadsheet. It seems it allows spreadsheet users to reasonably judge the actual correctness of their spreadsheets.

In case you are interested in more details, the whole approach together with the study is available as an open-access article in the Journal of Software: Evolution and Process directly or on arXiv. Besides Daniel Kulesz, Verena Käfer did a lot of work on the experiment.