Software product metrics allow practitioners to improve their products and to optimize development processes based on quantifiable characteristics of source code. To facilitate similar benefits for spreadsheet programs, researchers proposed various product metrics for spreadsheets over the last decades. However, to our knowledge, no comprehensive overview of those efforts is currently available. In this paper, we close this gap by conducting a literature review of research works that either inherently or explicitly define product metrics for spreadsheets. We scanned five major digital libraries for scientific papers that define or use spreadsheet product metrics. Based on the identified 37 papers, we created a novel catalog of product metrics for spreadsheets. The catalog can be used by practitioners and researchers as a central reference for spreadsheet product metrics. In the paper, we (i) describe the proposed metrics in detail, (ii) report how often and for what purposes the metrics are used, (iii) identify significant discrepancies in the naming and definition of the metrics, and (iv) investigate how the appropriateness of the metrics was evaluated.

Electronic spreadsheets are widely used in organizations for various data analytics and decision-making tasks. Even though faults within such spreadsheets are common and can have significant negative consequences, today's tools for creating and handling spreadsheets provide limited support for fault detection, localization, and repair. Being able to predict whether a certain part of a spreadsheet is faulty or not is often central for the implementation of such supporting functionality. In this work, we propose a novel approach to fault prediction in spreadsheet formulas, which combines an extensive catalog of spreadsheet metrics with modern machine learning algorithms. An analysis of the individual metrics from our catalog reveals that they are generally suited to discover a wide range of faults. Their predictive power is, however, limited when considered in isolation. Therefore, in our approach we apply supervised learning algorithms to obtain fault predictors that utilize all data provided by multiple spreadsheet metrics from our catalog. Experiments on different datasets containing faulty spreadsheets show that particularly Random Forests classifiers are often effective. As a result, the proposed method is in many cases able to make highly accurate predictions whether a given formula of a spreadsheet is faulty.11.Results of a preliminary study were published in[1].

Faults in spreadsheets can represent a major risk for businesses. To minimize such risks, various automated testing and debugging approaches for spreadsheets were proposed. In such approaches, often one main assumption is that the spreadsheet developer is able to indicate if the outcomes of certain calculations correspond to the intended values. This, however, might require that the user performs calculations manually, a process which can easily become tedious and error-prone for more complex spreadsheets. In this work, we propose an interactive spreadsheet algorithmic debugging method, which is based on partitioning the spreadsheet into fragments. Test cases can then be automatically or manually created for each of these smaller fragments, whose correctness or faultiness can be easier assessed by users than test cases that cover the entire spreadsheet. The annotated test cases are then fed into an algorithmic debugging technique, which returns a set of formulas that could have caused any observed failures, i.e., discrepancies between the expected and computed calculation outcomes. Simulation experiments demonstrate that the suggested decomposition approach can speed up the algorithmic debugging process and significantly reduce the number of fault candidates returned by the algorithm. An additional laboratory study shows that fragmenting a spreadsheet with our method furthermore reduces the time needed by users for creating test cases for a spreadsheet.

While spreadsheets are widely used for business-related tasks, they are mostly handled by novice users instead of professional programmers. Consequently, those users often are not aware of quality issues in their spreadsheet programs that may lead to faults with significant adverse effects. In this work, we therefore present a tool, called Fritz, to support users in checking and improving the quality of their spreadsheets. The tool enriches the traditional spreadsheet visualization scheme by including visual feedback about certain structural and quality aspects. This allows for easier cognition of a spreadsheet's layout, and helps users to detect and comprehend irregularities within it. Furthermore, Fritz highlights suspicious (smelly) cells, such as complex formula cells or empty input cells, that are prone to introduce errors. In contrast to other smell detection tools, Fritz also warns against smells that point out structural irregularities.

Spreadsheets serve as a basis for decision-making processes in many companies and bugs in spreadsheets can therefore represent a considerable risk to businesses. Systematic tests can help to locate such bugs, but providing test cases can be cumbersome and complex for large real-world spreadsheets. To make the specification of test cases easier, we propose to split spreadsheets into smaller logically connected parts (called fragments) which can be individually tested for correctness. We present an algorithmic approach to compute such fragments, which we validated with a laboratory study in the form of a spreadsheet debugging exercise involving 57 subjects. The results show that the fragmentation approach can help to significantly reduce the required efforts to test a spreadsheet.