i-nth logo


Bas Jansen & Felienne Hermans


Spreadsheets are widely used in industry. Companies take important decisions based on information coming from spreadsheets. But, spreadsheets also suffer from typical software maintenance issues. Previous research shows that they contain code smells, lack documentation and testing, have a long live span and during this life span are transferred multiple times from one user to another.

To obtain a better understanding of how the maintainability of spreadsheets can be improved, we focus in this paper on spreadsheet comprehension. We analyze the relation between the organization of formulas in a spreadsheet and its maintainability.

To that end, we conduct a controlled experiment with 107 spreadsheet users, divided into two groups. One group receives a model where the formals are organized in such a way that all related components are grouped closely together, the other group receives a model where the components are spread far and wide in the spreadsheet. All subjects have to perform the same set of comprehension tasks on their spreadsheet.

The results indicate that the way formulas are organized, indeed influences the performance of the subjects in their ability to comprehend and adapt the spreadsheet. Especially for the comprehension tasks, the subjects performed better on the model were the data was grouped closely together.

For the adaptation tasks, we found that the way the formulas were constructed influenced the performance of the subjects more than the organization of the formulas itself.


Example of formula with all its precedents grouped closely together
Example of formula with all its precedents grouped closely together

To completely understand the formula in cell C32 you need to trace back not only the direct but also the indirect precedents. They are illustrated with the blue arrows.

The indirect precedents are located somewhat further away. Nevertheless, they still can be presented in a readable manner on a 13-inch monitor with the zoom factor set to 100%.


2016, Delft University of Technology, Report TUD-SERG-2016-014

Full article

The effect of delocalized plans on the maintainability of spreadsheets

Also see

The effect of delocalized plans on spreadsheet comprehension - A controlled experiment