A sidebar to the article “Ten Years of Rows and Columns” published in Byte, issue 13/1989, pp. 330-331.

Beyond “What-if?”

When Dan Bricklin and Bob Frankston built the original VisiCalc, the spreadsheet metaphor was essentially an electronic worksheet that remembered what you did and allowed you to make small alterations to your calculations to see how they affected the results. But the basic spreadsheet metaphor grew rapidly to encompass a much broader range of functions. The parallel evolutions of spreadsheets and personal computer hardware were tightly coupled. Spreadsheets are perhaps unique among major kinds of software in that they push the limits of personal computer technology while letting the user take advantage of all its aspects.

The core of spreadsheet technology is instant response to user input as models and display presentations get more complex. The demand for more spreadsheet calculation cycles has grown faster than the boost in microprocessor speed. Over time, this demand has led to smarter recalculation techniques.

In the past three years, most spreadsheets have come to provide some form of minimal recalc to calculate only those formulas that might be affected by the change. Recent innovations include background recalc, which lets you move around, print, and so forth while the spreadsheet is calculating, and restartable recalc, which lets you enter new data before recalculation is finished. The next few years will bring techniques that get ever closer to “optimal recalc” – recalc before you perform it.

To provide ever-greater responsiveness, the newest spreadsheets are built around internal multitasking designs that allow instantaneous live display of data as you work, while prioritizing less important activities to background recalculation, print formatting, chart layout, and so forth. Beyond this improvement, we may see further exploitation of concurrency to anticipate what the users will ask next and to provide them with suggestions and other help.

As users tackled more complex problems, spreadsheets supplied more powerful analysis tools. Innovations included tools for repetitive evaluation, such as macros, and two-dimensional and three-dimensional table generation for case analysis. Other aids now include high-powered techniques such as regression and goal seeking. In the next generation, we’ll see linear programming and mathematical optimization.

The framework of spreadsheet technology is the rows-and-columns metaphor, a powerful work organizer. As your other needs grow, so does your need to organize your work better. The desire for a better structure for large models has led to three-dimensional worksheets that allow the user flexibility to put different parts of a calculation on different pages of the worksheet.

Many users need to consolidate information from multiple worksheets. This led to support for multiple active files in memory. To link the worksheets, designers invented various features that transfer values among worksheets and worksheet files on disk. As file sharing on networks becomes possible, modular systems of worksheets, including live sharing of files on LANs among multiple users, becomes possible.

Ever-larger and more-complex worksheet structures bring about the need for better memory management. The LIM/EMS, DOS Extender technology, and OS/2’s use of the 80286/80386 protected mode have fed the spreadsheet user’s memory appetite. At the same time, spreadsheet programs use evermore-sophisticated sparse-matrix data structures. In the newest spreadsheets, data compression techniques like Huffman codes and run-length codes pack more data into fewer bytes internally.

Over the last few years, spreadsheet presentation abilities have expanded dramatically. High-resolution color graphics on printers and personal computer display screens have driven the demand for more effective charting and printing. Live charts provide another way to do “what-if?” Colored cells, grid lines, fonts, and shading in the worksheet are now possible. They have led to a new application area – spreadsheet publishing – producing the best possible hard-copy output to present the results of spreadsheet work to others. As CPU power increases, spreadsheets could drive animated graphics for yet another alternative presentation mode.

Spreadsheets are beginning to provide direct access to external data. As personal computers are hooked up to shared databases, the row/column metaphor has evolved to provide a new kind of viewport on relational tables. Developers have invented database access standards that allow spreadsheets to deal with the diversity of types of external data sources.

In the future, the spreadsheet’s ease of use may make it the primary tool for analyzing large amounts of shared data. Spreadsheet responsiveness also is a powerful capability that can allow the same type of user-programmable analysis of live data feeds as might come from scientific instruments and stock tickers.

Spreadsheets must remain intuitive. Recent innovations include new command modes, such as pull-down menus, setting sheets/dialog boxes, and direct manipulation of spreadsheet layout using a mouse. Natural language and speech input may also be coming. Perhaps more important directions are suggested by AI-based techniques, such as the “graph this” command, which guesses the best way to present a table as a chart. The next generation might see commands like “make this nicer.”

The basic spreadsheet metaphor is the epitome of “programming by example.” This concept has also been used in newer features, such as the database criterion range, the macro learn mode, and the newer, always-on, macro recording features. A key to ease of use are features that provide a “safety net.” The first of these were warnings about destructive commands, such as large erases and automatic file backup. More recently, we have seen undo for single commands, undo for whole macros, and (in the next generation) multilevel undo features that will reduce the risk of user mistakes and misunderstandings.

A growing area of interest is the use of the spreadsheet as a programming tool to create specialized applications. Macro languages continue to be enhanced to support this area. Two new technologies, spreadsheet compilers and add-ins (and associated add-in toolkits), create the opportunity for developers to base substantial applications on the spreadsheet as a programming tool.

Finally, spreadsheets have begun to incorporate new kinds of debugging aids, such as single-step mode, dependency analysis, and spreadsheet auditing tools. Ultimately, you will use AI techniques to ask why something surprising happened in your spreadsheet.

There’s still lots more to do. Personal computer technology will continue to improve, providing users with new domains to explore with ever-more-sophisticated spreadsheet tools.

David P. Reed

David P. Reed is vice president and chief scientist/spreadsheets for Lotus Development Corp. (Cambridge, MA). He can be reached on BIX c/o “editors.”

Page added on 22nd September 2004.

Copyright © 2002-2005 Marcin Wichary