The Effect of a Block-based Language on Formula Comprehension in Spreadsheets

The use of spreadsheets in industry is widespread. It is known that spreadsheets have an average life span of five years, and during this life span, they are used on average by thirteen different persons. Consequently, spreadsheets need maintenance, and knowledge about the spreadsheet needs to be transferred from one user to another. To minimize the risk of introducing new errors, a thorough understanding of the spreadsheet's formulas is needed during maintenance and knowledge transfer tasks.

Research on the use of block-based languages has shown that they positively affect the comprehension of program code. We hypothesize that using a block-based representation of a spreadsheet formula will positively affect formula comprehension.

Hence, we extended XLBlocks, a block-based formula editor for spreadsheets, with the functionality to generate a block-based representation of an existing formula. We conduct a think-aloud study with twenty-one experienced spreadsheet users from industry and ask them to perform a set of spreadsheet comprehension tasks using XLBlocks. During an interview, we ask them, using the Cognitive Dimensions of Notations framework, to reflect on the use of XLBlocks.

We found that participants preferred to use the block-based representation of formulas when analyzing or explaining formulas or to implement non-trivial changes. Named function parameters and the absence of parentheses and commas make functions easier to understand. Furthermore, the visualization enables the user to separate smaller parts in the formula, which improves comprehension. Finally, the possibility to navigate from formula to formula makes it clear how formulas work together and improve the understanding of the spreadsheet as a whole.