1.1. How Spreadsheets Came to Us

Based on the history of personal computers, spreadsheets may fairly readily be argued to be the most important application area on personal computers.

Something that isn't questionable is that the entry of personal computers into business began as a specific result of the advent of spreadsheet software.

Businesses bought Apple II computers because they wanted to use VisiCalc. (Which, interestingly to operating system aficionados, was originally developed on the Multics platform, according to VisiCalc coauthor Bob Frankston. Yet another argument for the notion that virtually all important modern computing innovations took place 20 years ago on the Multics platform...) More details may be found at VisiCalc: Information from its creators, Dan Bricklin and Bob Frankston

Thanks are in order to Bob Frankston for some corrections he has provided to this history.

When Lotus 123 became available for IBM PCs, the cycle continued, and IBM PC sales took off. Then Microsoft got into the picture, and things have unfortunately degraded considerably...

Here is another Brief History of Spreadsheets. The straight facts are pretty accurate; I'll just take a bit of issue with a couple points that represent "editorial opinion" that we can probably agree to disagree over...

• The authors suggested that Lotus 123's "A1" referencing system was "more intuitive" than the "R1C1" system used by various other spreadsheets notably including Microsoft's MultiPlan.

  I suggest instead that neither is particularly more intuitive than the other; "A1" simply happens to be more compact.

  	The A1 notation originated in VisiCalc , not 1-2-3. The reasoning for it was simple: it was simple to type. Reversing standard notation, we used the letters for the columns because there were fewer columns than rows.
  -- Bob Frankston

• They stated that: The spreadsheet instantly became easier to use than the archaic interface of PC-DOS products...

  The text-based user interfaces were hardly "archaic" at the time; they were as up to date at the time as anything was. It is indeed fairly convenient to select "blocks" using a mouse, and it is fair to claim this is easier to learn than the (quite functional) keyboard-based ways for doing this that were and likely still are faster. (Except with Excel , where the keyboard interface appears to have been made deliberately arcane, but I digress...)

Spreadsheets have provided for (without most of the users having any conscious awareness of this), the large scale deployment of Cellular Automata. One [FAQ on Cellular Automata] defined Cellular Automata thus:

A cellular automaton is a discrete dynamical system. Space, time, and the states of the system are discrete. Each point in a regular spatial lattice, called a cell, can have any one of a finite number of states. The states of the cells in the lattice are updated according to a local rule. That is, the state of a cell at a given time depends only on its own state one time step previously, and the states of its nearby neighbors at the previous time step. All cells on the lattice are updated synchronously. Thus the state of the entire lattice advances in discrete time steps.

Spreadsheets satisfy these requirements, with a few "bits of fuzziness," notably that:

• "Nearby neighbors" don't have to be terribly nearby with a spreadsheet.

• There are some questions of synchronicity.

  Spreadsheet packages have often provided some configurability to indicate update policies that are not synchronous, but which rather involve some ordering of updates.

• Pure CA systems tend to apply a single update rule to many cells in the lattice; in contrast, the spreadsheet software in common use attach individual formulae to each and every cell.

  Improv and related packages would apply rules to different regions of cells; it seems to me that a more intelligent use of spreadsheets should involve some sort of "pattern matching" to help discover such rules for the user.

  ZigZag seeks to transform things from the "globally Euclidean" space used by spreadsheets to that of "locally euclidean" directions that more resemble the hyperlinking of Nelson's Xanadu.

• Cellular Automata FAQ

I've been using spreadsheets of various sorts on a wide variety of platforms since the mid-'80s. The first one which I used any substantial amount was SuperCalc, running under ZR-DOS (an enhanced CP/M "clone" that grew into ZCPR). Others have included (in rough chronological order):

• Lotus 123

• Lotus Symphony, which integrated a wordprocessor of dubious functionality, a simple "form" processor, and a telecom module into the mix;

• A 6502 Atari 8-bit based spreadsheet called SpeedCalc that was published in Compute! Magazine;

• LDW Power, a Lotus 123 "clone" for the Atari ST;

• Borland's Quattro Pro, used in grad school primarily for its linear programming capabilities;

• The spreadsheet built into the TRS-80 Model 100 portable computer (a very small, early version of Microsoft Multiplan that eventually grew into the monster now known as Excel)

• As Easy As (guess who they were cloning?);

  MS-DOS-based and available as shareware, this is still a featureful spreadsheet. It doesn't allow heavy-duty GUI formatting of spreadsheets, but only people with far too much time on their hands do so...

• Lucid 3D;

  The first version of Lucid 3D ran on TRS-80 Model 100 laptop computers, and the necessity for frugality on that platform resulted in a design that was sparse, frugal, and indeed, extremely lucid.

  The MS-DOS version was my favorite MS-DOS program of any sort; its user interface is a wonderful model of integration of powerful use of both keyboard and mouse; the program was still frugal in its use of disk/RAM and yet provided excellent overall functionality.

• SC ("Spreadsheet Calculator");

• VC (Enhanced, "more visual" version of SC);

• The much despised memory hog, Microsoft Excel ;

• The spreadsheet built into the Psion 3 handheld computer;

• SmartWare

• Xess;

• Wingz

• Teapot!

with bits of playing around with others such as Lotus Improv , the Microsoft Works spreadsheet for MS-DOS, the FSF's Oleo, an entirely-custom one that I wrote in LISP (mostly just as a programming exercise), and sundry fiddling around with a pretty wide variety of MS-DOS, MS-Windows, and Unix-based "integrated packages." The most notable spreadsheet of which I have never made significant use is VisiCalc, the program that popularized the whole idea of software that that allow interactive entry of numbers, text, and formulae arranged in rows and columns.

Tasks I've done with spreadsheets have included:

• Preparation of accounting working papers

• Preparation of financial statements

• Economic simulations via difference equations/"cellular automata"

• Mathematical modelling, solving linear and nonlinear programs

• Loan modelling

• Database conversions

• Statistical analysis

In short, I've done enough work of enough various types with a large enough variety of spreadsheet packages that I figure I'm entitled to rant a little bit about their proper use.

Here are some further useful resources on the ancient and more modern history of spreadsheets:

• The newsgroup comp.apps.spreadsheets has a FAQ.

1.2. Problems with Modern Spreadsheet Developments

"Enhancements" of spreadsheets over the last few years have not involved substantive improvements in functionality, but have primarily just involved enhancing their "typesetting" capabilities, that is, the ability to change fonts, insert special formatting, and to otherwise make tables look "pretty."

I put "enhancements" in quotes because I am skeptical that this actually represents a true improvement of either the quality of the information or user efficiency in finding and using information.

These so-called improvements gloss over the continuing problems that plague spreadsheet users:

• Spreadsheet models encourage the use of "spaghetti" logic, where cells point to cells that point to cells, and can grow into random networks of calculation logic;

• They permit lots of easy off-by-one errors;

• They generally are difficult to verify/audit;

• They do not provide good tools for managing data either in terms of consolidation or searching for specific detail;

• Perhaps most importantly, despite their convenience, spreadsheets are not a robust repository for information.

I have seen one multinational enterprise that (believe it or not) built a budgeting system atop sets of dozens of departmental spreadsheets that they would roll up into a master budget; while it's a neat extension of the technology, only a fool would try to use this to run a large enterprise. One bad link in one subsheet, and the whole house of cards could fall down. (And the "top" vendor these days, Microsoft, isn't noted for building products that are of industrial grade robustness.)

The last few points point towards where I would like to see spreadsheets go. They have been, and are very good at producing ad-hoc, one-off reports. This is a proper use of spreadsheets.

They are often being used instead as repositories for information that really ought to be managed by a database management system of some sort.

What spreadsheets should do is to allow, nay encourage, the use of data extracts from external sources, notably relational databases. The use of named ranges (which are a venerable feature from at least as early as Lotus 123 v2.01) is of assistance; Lotus Improv was a rather complex-to-use test platform for improved "modelling" whose functionality included database extraction.

Using external repositories permits the benefits of:

• A single repository that can be kept correct, rather than a multitude of mutually incompatible data stores;

• Data synchronization (a restatement of the last);

• All the good RDBMS "stuff" like:

  • Field validation,

  • Maintaining field relationships,

  • Transaction logging,

  • Centralized backups,

  and perhaps even more sophisticated things such as

  • Data modelling and

  • Stored Procedures/Triggers

In effect, the real point I would propose is that the task of building a spreadsheet should involve some data modelling, with thought not just about the report at hand, but also about where the data comes from and perhaps should go to.

Microsoft Excel does have a capability to connect to databases as does LibreOffice ; the support is yet a bit on the primitive side.

1.2.4. Idle Thoughts About Fundamental Improvements

A number of "dead ends" have been encountered in the ongoing development of spreadsheets.

• The "traditional" spreadsheet systems went through a process of "racing for successive refinements" through the late 1980s and early 1990s, largely seeking to add "feature points" to win the contests for "most features counted in the reviews."

  Unfortunately, this means that the current code bases are not terribly amenable to more radical evolution, and many of the "improvements" are merely cosmetic, in providing more and more sophisticated tools for managing the appearance of the data.

  The major problem with the "traditional" spreadsheet system is that it does not provide much in the way of "structuring tools" to recognize and enforce the structure of the data model, as described in Problems with Modern Spreadsheet Developments.

• Improv proposed better ways of building models, and essentially mandated constructing spreadsheets as a process of constructing a system model.

  This had the unfortunate, and, as it turns out, unacceptable effect of preventing the "free form" spreadsheet construction that traditional spreadsheets encouraged.

  In a traditional spreadsheet, you have a set of rows and columns in which you are free to put anything. It is easy to prototype something up, throwing values here and there wherever it seems convenient to throw them.

  In Improv, elements could not be added to the matrix without defining the nature of the row and column. This eliminates "doodling around." On the one hand, there is good to it, in that spreadsheets commonly turn into undefined spaghetti. On the other hand, if you have to be able to "theoretically" justify every change you make to the shape of the spreadsheet, then the only people that will be using the tool will be "theorists," and to a great extent, that's what happened with Improv.

• Model Master provides considerable power in defining models, provides all sorts of "strong typing" options, and provides the logical extension of having the language specify access to robust data sources like relational databases, but has two substantial demerits:

  • It altogether rules out "free form" construction of data into sheets

    Although this is changing, as construction of a "decompiler" is underway.

  • It mandates using a declarative programming language to describe the model.

    The implicit "programmability" of the cellular automata means that the average user doesn't need to know about programming; unfortunately, Model Manager pushes programming in their face.

It seems to me that a "step forward" is to try to take the merits of each of these approaches, whilst seeking to avoid the demerits.

Robert Monfera <monfera@fisec.com> pointed out to me what he described as "uniform structural unification;" basically the notion of taking spreadsheets in the traditional "free form" pioneered with VisiCalc, and then, rather than starting by trying to enforce structure (as was the case with Improv), instead searching for structure. It's not a mechanism of infinite analytical power; it likely will only be helpful to find some limited bits of structure. Of course, "limited" may still be sufficient to actually provide some useful added functionality to relatively unsophisticated users, and forcing people to start from a data modelling perspective, as with Improv , hasn't proven terribly popular.

1.2.4.1. A Possible Architecture

1.2.4.1.1. Start Out With a Free-Form Sheet

The strength of the traditional spreadsheet is in providing a "free form" medium where users may construct models without directly having to program.

So, we start with a front end that is a very "traditional" sort of spreadsheet. Rows, columns, cells, formulae.

1.2.4.1.2. Attaching "Rules" Via Pattern Wizards

It would be nice to get the benefits of Model Master, in providing the ability to attach fairly strong "rules" to portions of the spreadsheet, whether to enforce the use of common formulae or to enforce "strong typing" of the data types used in those regions. For instance, a region that represents "dates" should contain nothing other than legitimate dates.

The route to this is to use some "artificial intelligence-like" techniques to search for patterns in the data, and to write up rules to propose to the user. I will call these "Pattern Wizards."

This could include patterns such as the following:

• Detecting sequences of cell contents that look, for instance, like dates.

  The proposal would then offer to:

  • Give the region a name indicating that it is a "date" region;

  • Attach "type" information to the region, requiring that all cells contain dates;

  • If a clear sequence is indicated, offer a formula that would compute the contents of the cells.

• Detecting that a region contains a Price/Quantity formula.

  For instance, there may be a column that multiplies the contents of a cell in another column of the current row (indicating quantity?) by either a specific value, or by the contents of one cell somewhere in the sheet (perhaps containing a constant price?), or by the contents of a cell in another column (indicating a price for the current row?).

  Such a pattern is suggestive of a price/quantity relationship, and the system could offer to:

  • To establish all three regions (price, quantity, total cost) as having names;

  • To attach "type" information to all three regions;

  • To name and attach the single formula to all of the formulae cells.

• Detecting a "running balance" formula.

  If there is commonality that a sequence of cells add together the "cell above" with "values to the left," this looks like a running balance.

  A similar set of proposals could be generated, to attach "type" information, to attach names, and to attach the single formula across all the "balance" cells.

• In order to provide goodly flexibility in offering "abstractions" that make for convenient formulae, the system would need to allow construction of "user-defined" functions.

  My preference would be for this to be a dynamic language such as Lisp; a critical factor is for the language to be quite functional, where cells receive one value that is solely based on the input parameters. Other interesting alternatives would include:

  • ML, which makes major use of the notion of strong static typing; this would address the issue of the way cells can contain different "types" of data, and prevent many classes of runtime errors.

  • Haskell, characterized by being purely functional, and providing lazy evaluation.

    Lazy evaluation is a particularly useful notion for efficient implementation of spreadsheet calculation, as it allows deferring calculations until they are actually needed. Mind you, this only particularly applies to order of calculations, which isn't really a good enough for picking an implementation language.

  • It would be valuable for the "extension language" to be the language in which the "pattern wizards" described above are constructed so that it is possible to augment the patterns without a need to deploy a whole new system.

    The point here is not to expect users to write their own patterns, as most won't be able to cope with this. The "average user" is not going to be writing "Pattern Wizards," but will rather use "pre-canned" ones.

    However, an organization might hire a programmer that looks around to find the organization's "favorite patterns," and create Wizards to detect them.

1.2.4.1.3. Attaching Additional Attributes to Regions

Note that the "pattern wizards" are given the ability to decide that certain regions of the spreadsheet are to have certain names, and are to use common types/formats/formulae.

A logical extension to this would be to allow attaching database tables to regions, so that you might have columns that look up database information based on either static SQL SELECTs, or look up data based on other cells.

Indeed, if a good database schema can be arrived at for the data that does not represent computed information, it would be very nice to offer the ability to push the data conformant to the schema into a relational database. That provides the merits of:

• Eliminating arbitrary size limits, as databases are commonly able to quite readily cope with managing tables containing millions of tuples.

• Improving robustness, as good database systems support transactional updates, and generally don't crash very much.

• Having the database manage data validation.

  For instance, a relational database will reject attempts to add data with invalid dates, where, for instance, an attempt to do INSERT INTO TRANS (ACCT, AMOUNT, DATE) VALUES (101, 255.71, '2002-02-29') will elicit an error message such as ERROR: Bad date - external representation '2002-02-29'.

1.2.4.2. ZigZag: Another Possible Approach

ZigZag, an invention of Ted Nelson, is a new type of data structure. For mathematicians, the key words would be discrete, multidimensional, locally euclidean, with global directions (coordinate axes). Nonmathematicians can find an explanation in the FAQ at the GZigZag project website, but as a short and very inadequate summary, it is a spreadsheet on steroids. Or a database on acid. Or a filesystem on ... whatever.

There was a free implementation, GZigZag, written in Java using Swing.

Major properties of the system are thus:

Discrete

the information is stored in cells, kind of like a spreadsheet.

Multidimensional

instead of two dimensions, X and Y, that a spreadsheet has, a ZigZag space can have any number of dimensions which are distinguished by strings.

Locally Euclidean

a spreadsheet is globally euclidean, i.e. it is a lattice. ZigZag is only locally euclidean, so the neighborhood of (i.e. the cells next to) a given cell looks euclidean: if you go up and come down, you are back where you were before.

But if you start from location 1, and go up, left, down and right, you might not get back to where you were, let's say you are at location 2. But if you then go left, up, down and right from location 2, you get back to location 1.

The connections in ZigZag are user-alterable so you can connect any two cells along any given dimension, but because of the local euclidean constraint, each cell can be connected to only one cell in the positive and one cell in the negative direction on each dimension.

Computer scientists might note that ZigZag is an interesting special case of graphs.

• ZigZag Structurre

• Gzz

  Replacing GZigZag, since ZigZag was a registered trademark, this is a " free software " implementation of the ZigZag Zzstructure cell-oriented interconnection scheme.

  Cancelled because of patents on underlying technology...

1.3. Crossreferences

• Scott Nealy on "From Spreadsheet to Websheet"

• Information on Excel's Pivot Table facility.

  Pivot tables are roughly analogous to database views, and are doubtless essentially copied from the functionality of Lotus Improv.

  • Using a Pivot Table as Source for Chart

  • Pivot Tables Tutorial

• SequelBasis/SequelPivot

  This contains a tool to generate PostgreSQL database views that correspond to Pivot Tables.

• spreadsheetdetective.com

  Here's an auditing tool for Excel spreadsheets...

• Buggy spreadsheets: Russian roulette for the corporation