Tell your spreadsheet to get with the program

As has been remarked in Adventures in Data, spreadsheets have made it possible for anyone to be a programmer. A spreadsheet may not look like computer code, but it certainly fits the definition of a program, as it contains a set of instructions for manipulating data. Some researchers have proposed that spreadsheets are functional programs, which perform computation primarily by evaluating functions. Others, like Dr. Martin Erwig of Oregon State have shown that spreadsheets can be interpreted as object oriented programs. I, however, think that in the simplest terms spreadsheets are programs which are procedural and sequential in nature.

In a computer program, action takes place inside the bodies of methods, functions or scripts. Typically, values are first assigned to variables, and then those values are manipulated by other statements of code. In an object-oriented programming language like C#, an assignment of a value to a variable could look like this:

string errorMessage = “Check your credentials”;

var startingValue = Math.Sin(0.7432) + 0.57243;

An assignment can involve placing a simple value into a variable (as in the first case), or the evaluation of an expression which contains functions and possibly other variables (as in the second). When we write programs, we do ourselves great service by giving variables meaningful names. Variable names should be meaningful because they serve as the annotations for the logic of the program.

In C#, we can assign single value or collections of values to a variable. A collection of values can be organized as a simple list or one-dimensional array, or into arrays of higher dimensions::

string[] names = new string[] {“Tom”, “Mike”, “Jim”};

double[,] y = new double[2, 3] { { 10, 20, 30 }, { 15, 25, 35 } };

This brings us to the parallel between programming languages like C# or Python and a spreadsheet. When we place a value, or a formula into a cell, we are in effect creating an assignment statement. The difference is that in a spreadsheet, the variable is a cell, and there is no requirement to give this variable a name. It is possible to name a variable by using a named range, but in the vast majority of cases this is not done. We could also put a meaningful label in a nearby cell, but that label has no meaning in the context of the program, because it can’t be used to retrieve the variable’s value. Not having to name variables seems to be one of the appeals of spreadsheets as programming environments, as it reduces the amount of work needed to write a program.

As we write formula after formula in our workbook, we are creating assignment statements that are linked to each other in sequence. Cells that contain hard-coded values are the simple assignment statements, while cells that contain formulas are assignments with expressions. Inevitably we will write a formula on which no other formula depends, and these represent the “end” of the program. Every time we change a value in a cell, we are assigning a new value to a variable and then running the program all over again.

When we view a program written in a traditional programming language, the order in which statements appear is the order in which they are executed. In a spreadsheet, however, the physical placement of cells is disconnected from their order in the sequence of calculations. It is this very nature of spreadsheets that prevents us from seeing the sequential programs within them. And not seeing calculations exposed in step-by-step linear sequences makes it harder for us to comprehend the logic that was programmed into a spreadsheet.

It is this challenge that appeared as an opportunity for a new Excel extension. This add-in, CellNexus, was developed to allow a spreadsheet to be viewed more like a sequential program, and thus elucidate its logic.

CellNexus consolidates formulas, and then presents them visually in a sequential manner:

In this sequential view we can easily see the representation of a spreadsheet as a traditional computer program. If a consolidated output range of a formula is a single cell, it is analogous to the assignment of a single value to a variable. If the consolidated output range is one dimensional (i.e. one row or one column), it corresponds to the assignment of an array to a variable, and a two-dimensional range corresponds to a two dimensional array. CellNexus also allows us to see the spreadsheet as a program that flows as a sequence from beginning to end, and as something that is linear in nature.

Our hope is that seeing spreadsheets this way will help you understand the logic of the program you created, without actually thinking that you were writing a program. So try CellNexus today, and see if this approach makes it easier for you to understand the flow and sequence in your own spreadsheets.

Tinkerer, thinker, lifelong student. See my projects at www.informationfluidity.com