Spreadsheets are well-known to be frequently-used but error-prone communication devices. They are useful since they are active (e.g., automatic computation), provide a cognitive notation system drawing on visualizing values, meanings and relations at the same time (enabled by labeled, color-coded grids), and provide easy-to-use domain-specific operations (e.g., computational functions). The latter, in particular, is enabled by the text-style formula format in spreadsheets, in which variables are replaced by cell references. For simplystructured formulae this works very well. To keep the formulae simple, computations are modularized into subformulae and as such distributed over and beyond the spreadsheet. This makes the provenance (tree) of spreadsheet values difficult to understand - a probable cause for the high error rate in spreadsheets. To explore and navigate the subformulae involved in the computation of a cell value we present the subformula explorer “FEncy”, a tree-based, explorative interface: Whenever a user clicks on a cell its formula becomes the root of a celldependency graph. Each child node displays the formula of a cell (or range) reference used in the parent formula either in the original text-style or potentially in math notation. Moreover, each node represents a direct link to the respective cell (or range), so that it can be used for formula navigation as well.
What is a mathematical formula? According to Wikipedia, in mathematics it is “an entity constructed using the symbols and formation rules of a given logical language”. Even though there are multiple mathematical communities of practice which use a partly different set of symbols and slightly varying formation rules, there is a common understanding how to encode several information levels into formulae by extending the linear form of text.
On the one hand, this construction of a formula, O’HALLORAN calls a “grammatical strategy for encoding meaning efficiently [. . . which is achieved . . . ] through spatial and positional notation in a form that is not found in language.” [O’H05, p. 112]. In Fig. 1 we can see some common typographical line elements. The spatial information needed to characterize the form of a typical English text can be characterized via these line elements. But very often formulae need more space.
Accommodating our running example in Fig. 3, the equation 4 = with variables 4 and 4j represents the simple formula used in cell [B4].
Here, if we take a closer look (Fig. 2), we realize that the equation transcends the ascender and descender height with respect to the typographical baseline of the used font quite a bit. If we look closely, we also realize right away that not only specific spatial and positional notation is used, the common font type is also broken, there are, for example, greek letters. For mathematicians these are not unexpected and hardly something to think about since they have internalized the notational naming convention within formulae, that is the relation between fonts and functional status of objects. This common mathematical practice of authoring and interpreting formulae evolved over centuries and proved to be effective and efficient for mathematicians.
On the other hand, in a spreadsheet there are also mathematical formulae. We can, for instance, reformulate Equation( 1) as a computational formula in a spreadsheet like this: [B4] = 1=3 SUMSQ(D4 : G4) (2)
The differences between the different representations is obviously vast. In this paper we use the example given in Sect. 2 as a running example. In particular we discuss the differences in notation in Sect. 3 to motivate the design in general and the suggested formula notation options in particular of our (sub)formula explorer “FEncy” described in Sect. 4. We consider related work in Sect. 5 and conclude in Sect. 6 with an outlook on further work.
In the spreadsheet seen in Fig. 3, observed half-an-hour periods of full sunshine resp. rain in Bremen, i.e., the measurements, on four days in June are noted in ranges [D3:G3] resp. [D5:G5]. The difference xk x is called the mean deviation of xk. The mean deviation of those measurements can be found in ranges [D4:G4] resp. [D6:G6]. The sample variance for sunshine in Bremen, for example, in cell [B4] is calculated from the mean deviation according to Equation (3) with the spreadsheet formula in Equation (2). Finally, the arithmetic mean of the sample variances is presented in cell [B7].
We use this example throughout the paper as running example. 3
In general, the set of symbols used in spreadsheet formulae consists of given functions like SUM, individual macro extensions, numbers, and cell references like [B4] (in A1 referencing style referring to the cell in column B and row 4) or [R4C2] (in R1C1 referencing style pointing to the same cell). In MS Excel’10, for example, the set of symbols enlists 339 functions and 220 256 cell references per worksheet. An essential component of spreadsheet players is their computational foundation: they can compute values from formulae, that is, they can simplify formulae to values. It is important to note that – even though it acts like a programming language – “the formula language itself is entirely textual” [Nar93, p. 49].
The formation rules are rather simple: concatenate the ingredients into a string of ASCII characters. From the user perspective NARDI points out that authoring and understanding formulae “the user must master only two concepts: cells as variables and functions as relations between variables” [Nar93, p. 42]. This is suspected to be the underlying reason for spreadsheets being the world’s most used programming environment: the task of writing formulae (program scripts) is transformed into the task of writing text in a well-understood domain language consisting of typically 3-5 [SP88], at most 10 [Nar93, p. 43] and potentially – in MS Excel e.g. – 339 functions. It is rather interesting that the formula language hasn’t changed at all since the very first appearance of spreadsheet applications, therefore we can call it a successful formula language.
Note that the ease of writing down spreadsheet formulae comes at the cost of reading them. For a simple formula, there is no problem in interpreting this linear notation of a formula – if the reader is very familiar with the used naming convention for cells in spreadsheets.
The confusion begins if the spreadsheet author used the rather uncommon R1C1 referencing style, e.g., for Equation (2): [R4C2] = 1=3
SUMSQ(RC(2) : RC(5)) (4) Here, the cell referencing is relative to the cell that will contain the calculated value, e.g. RC(2) = R(0)C(2) refers to [D4] (with D=B+2,4=4+0).
It gets even more confusing if the spreadsheet author used e.g. the German MS Excel version with R1C1 referencing style (where “Z(eile)” stands for “R(ow)”, “S(palte)” replaces “C(olumn)”, and ”SUMSQ” translates to “QUADRATESUMME”) as in Fig. 4.
Fig. 4. German R1C1 Notation of Equation (4) Besides this specific representation format knowledge, the reader might also get easily overwhelmed if the formula is complex. As readers are typically experts in their specific fields, but laymen in spreadsheet technology, this is in analogy to command line interfaces which work very well for simple commands used by laymen or for complex commands used by power users. Therefore, one explicit aim of a spreadsheet author has to be the optimal reduction of complex formulae.
This can be done via modularization, in particular by collapsing parts of formulae into variables by using these parts as autonomous formulae to calculate different cell values. In Fig. 5 we can see a version of Fig. 4, this time in the more common A1 referencing style. The mean of all cell values in range [B3:B6] is calculated in [B7].
Moreover, the cells in the ranges [D4:G4] and [D6:G6] con
Thus, in [B7] as seen in Fig. 5 we have the recursively resolved equations as shown in Fig. 7.
Note that even though the underlying formulae are one of the most simple ones,
already the concatenated formula turns out to look rather complex to grasp. The reason
consists of the fact that the cell references in Equation (
As it is well-known that human short-term memory is rather limited (7 +/- 2 items can be kept in short term memory at any given time), the modularization of formulae is not an option, but rather a requirement for authoring readable spreadsheets. It is obvious that this modularization enables at the same time a high error rate with errors that are hard to debug.
The formula explorer FEncy is based on the idea that the cell references can be
automatically resolved into a cell-independent format e.g. presentation MathML [Aus+10]
with variables that have mnemonic names, that is, names that hint at their meaning.
For example, it is a quasi-standard to index a set of data points by a counter variable
in fi; j; k; l; m; ng, to assign the name y to the mean of data points yk, to name
variances , and to name differences . Now look at Equations (
j=4 Note that typically a reader familiar with math notation will have noticed at the latest in Equation (7), that there is something strange going on with the mean being a sum of 4 numbers divided by the normalizing term 2. Looking at Fig. 5 we notice why the effect is correct, but the formula isn’t. Therefore, math notation might also help to discover semantic errors in formulae.
The modularization can be kept, if we visualize the formula dependencies in form of a graph, where every node contains information about a formula. 4
To keep spreadsheet formulae simple, computations are modularized into subformulae and as such distributed over and beyond the spreadsheet. Even though the modularization simplifies the formula itself, it resolves in a very complex provenance (tree) of spreadsheet values. The basic idea of FEncy consists in an interactive visualization of the modularization of a formula. To explore and navigate the subformulae involved in the computation of a cell value we developed a semantically supported, tree-based, explorative interface: Whenever a user clicks on a cell its formula becomes the root of a “formula graph”, i.e., a graph with cell/range nodes and cell/range-dependency edges. Each child node displays the formula of a cell (or range) reference used in the parent formula.
For example, in Fig. 7(a) we can see an entire formula graph developed after the
user clicked cell [B7]. This cell contains the formula 1=2 SUM(B3 : B6), that is
Equation (
On a more technical note, the formula explorer FEncy is a semantic service integrated into the open source Semantic Alliance Framework [Dav+12]. This framework allows to superimpose semantic services over an existing (and possibly proprietary) application provided that it gives open-API access to user events. Elements in the application are connected to according concepts in structured background ontologies, which, for instance, contain a representation of the respective domain and some instance specific information. Semantic services can draw on the ontology information to offer intelligent services, which are offered to the user via the Semantic Alliance framework in 1 Ranges used as cell references in formulae are typically functional blocks, i.e., cell ranges that have the same functional content, see [KK13] for more details.
(a) The Expanded Formula Tree in Cell [B7] (with Spreadsheet Formulae) (b) The Expanded Formula Tree in Cell [B7] (with Math Formulae) local, but application independent windows. For the most common spreadsheet applications MS Excel and LibreOffice there are already existing Semantic Alliance APIs. = 1=2 SUM(1=3 SUMSQ(D4; G4) : 1=3 SUMSQ(D6; G6)) = 1=2 SUM(1=3 SUMSQ(SUM(D3; $H$3); SUM(G3; $H$3))
: 1=3 SUMSQ(SUM(D5; $H$5); SUM(G5; $H$5)))
FEncy offers more than a tree-based visualization of the (sub-)formulae in a spreadsheet. In a nutshell, every node of the formula graph consists of a list of elements: – The title expressing the underlying meaning of a cell value or a range of values, – a link to the corresponding cell/range in the spreadsheet, – the dependencies this cell/range depends on, – its data value, – an explanation of its meaning, – the spreadsheet formula (or its equivalent math formula), and – iterators to move through the cells with their resp. values of a range.
Let us have a closer look, for example, at a node like the left one in Fig. 8.
The cell [B7] is associated with the ontology concept “mean variance”. The title of this concept followed by the cell reference “B7” itself is used as a title for the node.
The underline of the cell reference indicates that it represents a link to this cell.
On the upper right-hand side we can see a collapse and an expand button, which collapses or expands the formula graph respectively if clicked. The cell value of Fig. 8. Node Variants in Cell [B7] cell [B7] is 0; 333333 and is shown in the node as well. In the grey box the beginning of the explanation of the concept “mean variance” given in the ontology is visible. Hovering over the grey box will trigger the expansion of it, so that the entire definition will be visible (see an example in Fig. 9). By using the JOBAD framework[JOBAD], the user can even interact with the information items within this explanation: If other concepts are referenced in this definition (indicated by blue font usage), a click will open another window with the according concept definition. This way, a user can explore the background ontology and comprehend the meaning of the formula much deeper. The lower part of the node contains the formula, here the formula for [B7], if existent; see an empty formula example in Fig. 9. The hovering effect kicks in here as well, in particular, if the formula exceeds a certain size, the entire formula will only be visible while hovering over the formula box.
Cell [B7] itself is not part of a functional block, but e.g. cell [D5] is. As the value in [B7] depends down in the formula tree on the value in this cell, we can find the node for [D5] as the last one in the formula graph in Fig. 7(a) or more conveniently in Fig. 9. This functional block covers the observed and summarized data. Each measurement depends on which day it was taken and what weather condition is reported, in other words the measurement functional block depends on the day functional block [D3:G3] and the weather functional block [[A3], [A5]]. This dependency is noted in the node directly under the title (in grey font). Moreover, we can see that cell [D5] contains the value for “Day 1” and “Rain”. The triangular buttons allow a user to skim through the values in the respective functional blocks, and navigate to the respective spreadsheet cells via the link “D5” right after the title. This feature allows the user to easily navigate through related information items while abstracting away from the concrete structure. If any of the information items presented above are missing, the UI of the node adapts.
In a future prototype, if the user double clicks on the formula in a node, then the spreadsheet formula is converted into a math formula using MathML (see right node in Fig. 8). The option of presenting both variants seems sensible as a switch of formats should always be easily reversible to avoid confusion. The ontology concept “mean variance” includes knowledge about the symbol notation . Moreover, as the range [B3:B6] is associated with the concept “sample variance” with its symbol notation , a parser should be able to figure the math formula as seen in the right node in Fig. 8. To give a taste of the potential of this conversion, we include Fig. 7(b). Another idea, we want to pursue shortly is that the user can even edit the formula and push the changes back to the spreadsheet. 5
The visualization of data-flows within spreadsheets is not a new idea. In MS Excel itself there is a tracing tool that visualises precedents and dependents of a selected cell. The visualization breaks if the dependencies are beyond the worksheet or even more so beyond the workbook.
In [CKR01] the authors studied the comprehension factor of formulae visualized in distinct ways. They frame formula understanding in terms of the reader’s cognitive load and thus as a visual memory problem. They find that the “ideal organization is the simple tree. It is the easiest to chunk. In the simple tree the surface organization of the formula tree is in harmony with its deep structure.” [CKR01, p. 487].
KANKUZI and AYALEW presented in [KA08] a graph-based visualization of spreadsheets. Based on a Markov Clustering algorithm they generate a data-flow graph which visualizes cell cluster dependencies in an extra window aside the spreadsheet application window and provides semantic navigation similar to the one presented in FEncy. Instead of using functional blocks, i.e., sets of cells that belong together semantically, these authors use statistical clustering. Even though this probably provides a similar grouping effect, the spreadsheet reader won’t know why the cells are grouped. With FEncy we cannot only offer the reader this reason, i.e., the semantic relating concept, we also allow the reader to dig into the definition of this concept.
In [Raj+00] a tree representation for formulae is suggested according to predominant Software Engineering techniques. In particular, a formula is divided into a structure tree containing operators and functions and an arguments tree containing cell addresses and constants. This tree visualization of a formula is suggested to be done when authoring a spreadsheet, whereas FEncy is a tool that supports reading a spreadsheet. In [JMS06] a tool for generating formulae in several formats (possibly spreadsheet format) is presented. Again, the sole focus is given to the developer or author of formulae, nothing is said about the enhanced readability or comprehensibility of a formula.
www.spreadsheetstudio.com offers another type of formula explorer. The modularity of MS Excel formulae is made use of as is in FEncy. This formula explorer offers a modal pop-up window that presents the formula of the selected cell. The formula is automatically segmented into sensible parts like cells, ranges, function plus function parameters, constants etc. If the user hovers over the formula shown then the corresponding value is presented. If a segment corresponding to a cell or range is left-clicked, then the formula of that MS Excel object is shown as before. Thus, this formula explorer allows a similar navigation thru a formula via its subformulae. Moreover, the MS Excel cursor also moves to the MS Excel object selected in the formula window. There are two main differences between FEncy and SpreadsheetStudio. First, the latter can only show formulae in one cell at any given time, whereas the former can present all formulae depending on one cell. Secondly, SpreadsheetStudio exclusively uses the MS Excel notation of formulae, whereas FEncy offers their mathematical notation. This is not a question of ’font choice, but of cognitive adaptation to the task at hand, that is, formula understanding.
ASUNCION suggests in [Asu11] to capture the provenance of cell values by unobtrusively document their history and to make this set of data available for later querying. This kind of provenance capture certainly is appealing because of its automation facility, but the provenance is not stored on a semantic level. Thus, the author has to recognize data to be able to interpret the provenance correctly. Otherwise this kind of data handling seems to be very tedious. 6
In this paper we have presented FEncy, a (sub)formula explorer for spreadsheets, that allows readers to deeper understand what formulae, which concrete calculated values, what underlying concepts are spread how and where over the document.
We hope that FEncy will prove to be a useful service, especially as we are planning to extend its capability towards a light formula resp. concept editor, that allows to update existing formulae resp. ontology items. Even though the cell values are shown in the resp. formula nodes, we believe that the provenance of cell values is still not enough covered. The graph structure gives a hint where the data originally come from, but very often outside data bases are used for data input of spreadsheets. In particular, the spreadsheet author is typically a data architect. For him the primitives are data resources. Therefore, a set of new information objects could be introduced to spreadsheets. If they were present, then FEncy could visualize it as well, to obtain a formula visualization that not only keeps all relevant information in one place, it also uses the notation that is most efficient.
Acknowledgements We are grateful for the constructive reviews and suggestions in the review process. This work has been funded by the German Research Council under grant KO-2484-12-1.