Comparing Users’ Understanding of Formulas, Static and Dynamic Sketches as Representations of Spreadsheet Operations

Spreadsheets are widely used, but people still struggle to understand them, especially when they contain formulas with long, nested and conditional operations. This thesis explores how well people understand spreadsheet operations represented as classic formulas, as static sketches, and as videos of sketches being drawn, and how their understanding differs for operations exhibiting different levels of structural complexity. We conducted an exploratory study with 21 participants, divided into three groups that saw the same spreadsheet operations represented as formulas, static sketches or dynamic sketch videos. Participants verbally described how they understood the given command representations, allowing us to determine their degree of understanding and time to understanding. Our results show that all groups understood short, not too deeply nested computations best and fastest. Conditions, lookup operations and long, nested operations resulted in lower understanding and took longer. Formula representations work well for short computations on mostly visible cells that keep references within a single sheet and return a single value. Static sketch representations help when the computation is spread out, for example in cross-sheet references or batch results. Dynamic sketch representations are most helpful for some of the structurally most complex tasks, although sometimes at the cost of time.