The

Whole Code

Catalog

Spreadsheets

Reviewed Aug 12, 2019

Spreadsheets are the most widely used "programming" system in the world. While early spreadsheet pioneers, such as VisiCalc and Lotus 1-2-3, no longer exist, many of their early innovations still do in Microsoft Excel, Google Sheets, and Airtable. We review many of the powerful programming-like features of spreadsheets, and point out a few interesting spreadsheet-inspired experiments.

Product Feel

Referencing

Spreadsheets offer a variety of ways to reference cells from afar, such as by column and row, via labels, and via lookups. Cell references even work between sheets, and are always kept in sync and reactive to changes. In the following video, I demonstrate a variety of less-well-known referencing techniques, including labels and array formulas. At the end of the video, I show how errors are localized to cells (allowing non-erroneous cells to still work) and propagate those errors to any cells that reference erroneous cells downstream.

Live data intermediates

Where traditional coding tools show the formulas and hide the data, spreadsheets show the data and hide the formulas. This makes it much easier for people to follow the flow of a program. The following photo demonstrates a string-vectorizing algorithm (borrowed from here) where each step of the algorithm is visualized with real data. (For clarity, the formulas for each step are pasted above it.) Note the use of a SQL-like query expression at the bottom-left.

Trace Precedents

Many spreadsheets, including Microsoft Excel shown below, have a "trace precedents" feature that visualizes how a given cell is related to other cells.

Forms/3

Forms/3 is a visual programming environment built to explore the boundaries of the spreadsheet paradigm.

Interactive Graphics

In Forms/3, graphics can be made interactive to mouse events and their values can be referenced and via other cells, such as in the temperature converter application below:

Testing built-in

There is a specific interface in Forms/3 for "unit tests" that turn red when failing. It also tracks the percentage of test coverage for spreadsheet cells; below it shows 37% coverage.

Recursion

Forms/3 offers scaffolding for creating recursive programs. When it detects a loop in a formula, it infers your intent to create a recursive structure. For example, below the Fibonacci sequence is constructed in this way:

Lotus Improv

A blast from the past, Lotus Improv was Lotus's attempt to reimagine the spreadsheet to be much more structured and powerful. Despite failing in the marketplace, it had many features that are still not found in any modern spreadsheet.

Apparatus

Apparatus is a spreadsheet-like graphics programming environment for creating interactive diagrams. Shapes can be added and edited directly via a WYSIWYG editor, and their properties can be computed as reactive formulas, a la a spreadsheet. It is an example demonstration of how spreadsheet-style reactivity can be applied in a visual domain. One of its killer features it its intuitive recursion: simply drag the current element from the left elements palette inside itself.

Flowsheets

Flowsheets is a compelling exploration of extending the spreadsheet interface to handle scripting tasks. It allows for importing Python libraries, shows both code and data, references data via labels instead of locations, has better abstractions for dealing with collections of data, and renders HTML.

Pane

Pane is a live functional programming environment that is optimized to show data and all intermediate values. It plays on the common node-and-wire environment, but switches the paradigm around with the nodes as data and the wires as functions. Like in spreadsheets, it is reactive, priorities showing concrete data, and makes higher-level abstractions intuitive through mouse-based operations.

Further Reading