Spreadsheet
A spreadsheet is an interactive computer application program for organization, analysis and storage of data in tabularform. Spreadsheets developed as computerized simulations of paper accounting worksheets. The program operates on data represented as cells of an array, organized in rows and columns. Each cell of the array is a model–view–controllerelement that may contain either numeric or text data, or the results of formulas that automatically calculate and display a value based on the contents of other cells.
Spreadsheet users may adjust any stored value and observe the effects on calculated values. This makes the spreadsheet useful for "what-if" analysis since many cases can be rapidly investigated without manual recalculation. Modern spreadsheet software can have multiple interacting sheets, and can display data either as text and numerals, or in graphical form.
Besides performing basic arithmetic and mathematical functions, modern spreadsheets provide built-in functions for common financial and statistical operations. Such calculations as net present value or standard deviation can be applied to tabular data with a pre-programmed function in a formula. Spreadsheet programs also provide conditional expressions, functions to convert between text and numbers, and functions that operate on strings of text.
Spreadsheets have replaced paper-based systems throughout the business world. Although they were first developed for accounting or bookkeeping tasks, they now are used extensively in any context where tabular lists are built, sorted, and shared.
LANPAR was the first electronic spreadsheet on mainframe and time sharing computers. VisiCalc was the first electronic spreadsheet on a microcomputer and it helped turn the Apple II computer into a popular and widely used system. Lotus 1-2-3 was the leading spreadsheet when DOS was the dominant operating system. Excel now has the largest market share on the Windows and Macintosh platforms. A spreadsheet program is a standard feature of an office productivity suite; since the advent of web apps, office suites now also exist in web app form.
Usage
A spreadsheet consists of a table of cells arranged into rows and columns and referred to by the X and Y locations. X locations, the columns, are normally represented by letters, "A", "B", "C", etc., while rows are normally represented by numbers, 1, 2, 3, etc. A single cell can be referred to by addressing its row and column, "C10" for instance. Additionally, spreadsheets have the concept of a range, a group of cells, normally contiguous. For instance, one can refer to the first ten cells in the first column with the range "A1:A10". This system of cell references was introduced in VisiCalc, and known as "A1 notation".
In modern spreadsheet applications, several spreadsheets, often known as worksheets or simplysheets, are gathered together to form a workbook. A workbook is physically represented by a file, containing all the data for the book, the sheets and the cells with the sheets. Worksheets are normally represented by tabs that flip between pages, each one containing one of the sheets, although Numberschanges this model significantly. Cells in a multi-sheet book add the sheet name to their reference, for instance, "Sheet 1!C10". Some systems extend this syntax to allow cell references to different workbooks.
Users interact with sheets primarily through the cells. A given cell can hold data by simply entering it in, or a formula, which is normally created by preceding the text with an equals sign. Data might include the string of text
hello world, the number5 or the date 08-Dec-66. A formula would begin with the equals sign, =5*3, but this would normally be invisible because the display shows the result of the calculation,15 in this case, not the formula itself. This may lead to confusion in some cases.
The key feature of spreadsheets is the ability for a formula to refer to the contents of other cells, which may in turn be the result of a formula. To make such a formula, one simply replaces a number with a cell reference. For instance, the formula
=5*C10 would produce the result of multiplying the value in cell C10 by the number 5. If C10 holds the value 3 the result will be 15. But C10 might also hold its own formula referring to other cells, and so on.
The ability to chain formulas together is what gives a spreadsheet its power. Many problems can be broken down into a series of individual mathematical step, and these can be assigned to individual formulas in cells. Some of these formulas can apply to ranges as well, like the
SUM function that adds up all the numbers within a range.
Spreadsheets share many principles and traits of databases, but spreadsheets and databases are not the same thing. A spreadsheet is essentially just one table, whereas a database is a collection of many tables with machine-readablesemantic relationships between them. While it is true that a workbook that contains three sheets is indeed a file containing multiple tables that can interact with each other, it lacks the relational structure of a database. Spreadsheets and databases are interoperable—sheets can be imported into databases to become tables within them, and database queries can be exported into spreadsheets for further analysis.
A spreadsheet program is one of the main components of an office productivity suite, which usually also contains a word processor, a presentation program, and a database management system. Programs within a suite use similar commands for similar functions. Usually sharing data between the components is easier than with a non-integrated collection of functionally equivalent programs. This was particularly an advantage at a time when many personal computer systems used text-mode displays and commands, instead of a graphical user interface.
History
Paper spreadsheets
The word "spreadsheet" came from "spread" in its sense of a newspaper or magazine item (text or graphics) that covers two facing pages, extending across the center fold and treating the two pages as one large one. The compound word "spread-sheet" came to mean the format used to present book-keeping ledgers—with columns for categories of expenditures across the top, invoices listed down the left margin, and the amount of each payment in the cell where its row and column intersect—which were, traditionally, a "spread" across facing pages of a bound ledger (book for keeping accounting records) or on oversized sheets of paper (termed "analysis paper") ruled into rows and columns in that format and approximately twice as wide as ordinary paper.
Early implementations
Batch spreadsheet report generator
A batch "spreadsheet" is indistinguishable from a batch compiler with added input data, producing an output report, i.e., a4GL or conventional, non-interactive, batch computer program. However, this concept of an electronic spreadsheet was outlined in the 1961 paper "Budgeting Models and System Simulation" by Richard Mattessich. The subsequent work by Mattessich (1964a, Chpt. 9, Accounting and Analytical Methods) and its companion volume, Mattessich (1964b, Simulation of the Firm through a Budget Computer Program) applied computerized spreadsheets to accounting and budgeting systems (on mainframe computers programmed in FORTRAN IV). These batch Spreadsheets dealt primarily with the addition or subtraction of entire columns or rows (of input variables), rather than individual cells.
In 1962 this concept of the spreadsheet, called BCL for Business Computer Language, was implemented on an IBM 1130and in 1963 was ported to an IBM 7040 by R. Brian Walsh at Marquette University, Wisconsin. This program was written inFortran. Primitive timesharing was available on those machines. In 1968 BCL was ported by Walsh to the IBM 360/67 timesharing machine at Washington State University. It was used to assist in the teaching of finance to business students. Students were able to take information prepared by the professor and manipulate it to represent it and show ratios etc. In 1964, a book entitled Business Computer Language was written by Kimball, Stoffells and Walsh and both the book and program were copyrighted in 1966 and years later that copyright was renewed
Applied Data Resources had a FORTRAN preprocessor called Empires.
In the late 1960s Xerox used BCL to develop a more sophisticated version for their timesharing system.
LANPAR spreadsheet compiler
A key invention in the development of electronic spreadsheets was made by Rene K. Pardo and Remy Landau, who filed in 1970 U.S. Patent 4,398,249 on spreadsheet automatic natural order recalculation algorithm. While the patent was initially rejected by the patent office as being a purely mathematical invention, following 12 years of appeals, Pardo and Landau won a landmark court case at the CCPA (Predecessor Court of the Federal Circuit) overturning the Patent Office in 1983—establishing that "something does not cease to become patentable merely because the point of novelty is in an algorithm." However, in 1995 the United States Court of Appeals for the Federal Circuit ruled the patent unenforceable.
The actual software was called LANPAR—LANguage for Programming Arrays at Random. This was conceived and entirely developed in the summer of 1969 following Pardo and Landau's recent graduation from Harvard University. Co-inventor Rene Pardo recalls that he felt that one manager at Bell Canada should not have to depend on programmers to program and modify budgeting forms, and he thought of letting users type out forms in any order and having computer calculating results in the right order ("Forward Referencing/Natural Order Calculation"). Pardo and Landau developed and implemented the software in 1969.
LANPAR was used by Bell Canada, AT&T and the 18 operating telcos nationwide for their local and national budgeting operations. LANPAR was also used by General Motors. Its uniqueness was Pardo's co-invention incorporating forward referencing/natural order calculation (one of the first "non-procedural" computer languages) as opposed to left-to-right, top to bottom sequence for calculating the results in each cell that was used by VisiCalc, Supercalc, and the first version of Multiplan. Without forward referencing/natural order calculation, the user had to manually recalculate the spreadsheet as many times as necessary until the values in all the cells had stopped changing. Forward Referencing/Natural Order Calculation by a compiler was the cornerstone functionality required for any spreadsheet to be practical and successful.
The LANPAR system was implemented on GE400 and Honeywell 6000 online timesharing systems enabling users to program remotely via computer terminals and modems. Data could be entered dynamically either by paper tape, specific file access, on line, or even external data bases. Sophisticated mathematical expressions including logical comparisons and "if/then" statements could be used in any cell, and cells could be presented in any order.
Autoplan/Autotab spreadsheet programming language
In 1968, three former employees from the General Electric computer company headquartered in Phoenix, Arizona set out to start their own software development house. A. Leroy Ellison, Harry N. Cantrell, and Russell E. Edwards found themselves doing a large number of calculations when making tables for the business plans that they were presenting to venture capitalists. They decided to save themselves a lot of effort and wrote a computer program that produced their tables for them. This program, originally conceived as a simple utility for their personal use, would turn out to be the first software product offered by the company that would become known as Capex Corporation. "AutoPlan" ran on GE’s Time-sharing service; afterward, a version that ran on IBM mainframes was introduced under the name AutoTab. (National CSSoffered a similar product, CSSTAB, which had a moderate timesharing user base by the early 1970s. A major application was opinion research tabulation.) AutoPlan/AutoTab was not a WYSIWYG interactive spreadsheet program, it was a simple scripting language for spreadsheets. The user defined the names and labels for the rows and columns, then the formulas that defined each row or column.
Works Records System
Main article: Works Records System
The Works Records System was a spreadsheet system designed in 1974 at ICI in the UK. It was a company-internal system that ran on IBM mainframes, and was in use essentially unchanged for 27 years. It was intended for use by non-programmers and had a WYSIWIG interface.
IBM Financial Planning and Control System
The IBM Financial Planning and Control System was developed in 1976, by Brian Ingham at IBM Canada. It was implemented by IBM in at least 30 countries. It ran on an IBM mainframe and was among the first applications for financialplanning developed with APL that completely hid the programming language from the end-user. Through IBM's VM operating system, it was among the first programs to auto-update each copy of the application as new versions were released. Users could specify simple mathematical relationships between rows and between columns. Compared to any contemporary alternatives, it could support very large spreadsheets. It loaded actual financial data drawn from the legacy batch system into each user's spreadsheet on a monthly basis. It was designed to optimize the power of APL through object kernels, increasing program efficiency by as much as 50 fold over traditional programming approaches.
APLDOT modeling language
An example of an early "industrial weight" spreadsheet was APLDOT, developed in 1976 at the United States Railway Association on an IBM 360/91, running at The Johns Hopkins University Applied Physics Laboratory in Laurel, MD. The application was used successfully for many years in developing such applications as financial and costing models for the US Congress and for Conrail. APLDOT was dubbed a "spreadsheet" because financial analysts and strategic planners used it to solve the same problems they addressed with paper spreadsheet pads.
VisiCalc
Because of Dan Bricklin and Bob Frankston's implementation of VisiCalc on theApple II in 1979 and the IBM PC in 1981, the spreadsheet concept became widely known in the late 1970s and early 1980s. VisiCalc was the first spreadsheet that combined all essential features of modern spreadsheet applications (except for forward referencing/natural order recalculation), such as WYSIWYG interactive user interface, automatic recalculation, status and formula lines, range copying with relative and absolute references, formula building by selecting referenced cells. Unaware of LANPAR at the time PC World magazine called VisiCalc the first electronic spreadsheet.
Bricklin has spoken of watching his university professor create a table of calculation results on a blackboard. When the professor found an error, he had to tediously erase and rewrite a number of sequential entries in the table, triggering Bricklin to think that he could replicate the process on a computer, using the blackboard as the model to view results of underlying formulas. His idea became VisiCalc, the first application that turned the personal computer from a hobby for computer enthusiasts into a business tool.
VisiCalc went on to become the first killer app, an application that was so compelling, people would buy a particular computer just to use it. VisiCalc was in no small part responsible for the Apple II's success. The program was later ported to a number of other early computers, notably CP/M machines, the Atari 8-bit family and various Commodore platforms. Nevertheless, VisiCalc remains best known as an Apple II program.
Lotus 1-2-3 and other MS-DOS spreadsheets
The acceptance of the IBM PC following its introduction in August, 1981, began slowly, because most of the programs available for it were translations from other computer models. Things changed dramatically with the introduction of Lotus 1-2-3 in November, 1982, and release for sale in January, 1983. Since it was written especially for the IBM PC, it had good performance and became the killer app for this PC. Lotus 1-2-3 drove sales of the PC due to the improvements in speed and graphics compared to VisiCalc on the Apple II.
Lotus 1-2-3, along with its competitor Borland Quattro, soon displaced VisiCalc. Lotus 1-2-3 was released on January 26, 1983, started outselling then-most-popular VisiCalc the very same year, and for a number of years was the leading spreadsheet for DOS.
Microsoft Excel
Microsoft released the first version of Excel for the Macintosh on September 30, 1985, and then ported it to Windows, with the first version being numbered 2.05 (to synchronize with the Macintosh version 2.2) and released in November 1987. The Windows 3.x platforms of the early 1990s made it possible for Excel to take market share from Lotus. By the time Lotus responded with usable Windows products, Microsoft had begun to assemble their Office suite. By 1995, Excel was the market leader, edging out Lotus 1-2-3 and in 2013, IBM discontinued Lotus-1-2-3 altogether.
Open source software
Gnumeric is a free, cross-platform spreadsheet program that is part of the GNOME Free Software Desktop Project.OpenOffice.org Calc and the very closely related LibreOffice Calc (using the LGPL license) are free and open-source spreadsheets.
Web based spreadsheets
Main article: List of online spreadsheets
With the advent of advanced web technologies such as Ajax circa 2005, a new generation of online spreadsheets has emerged. Equipped with a rich Internet application user experience, the best web based online spreadsheets have many of the features seen in desktop spreadsheet applications. Some of them such as Office Online, ZOHO, Google Spreadsheets,EditGrid or ZK Spreadsheet also have strong multi-user collaboration features and / or offer real time updates from remote sources such as stock prices and currency exchange rates.
Other spreadsheets
- A list of current spreadsheet software
- Accel Spreadsheet from SSuite Office
- Calligra Sheets (formerly KCalc)
- Corel Quattro Pro (WordPerfect Office)
- GS-Calc
- GridCraft collaborative cloud spreadsheet for web and iPad
- iSpread for iPad, iPhone and iPod Touch
- Kingsoft Spreadsheets
- Mariner Calc is Mariner Software's spreadsheet software for Mac OS.
- Calc XLS is Mariner Software's spreadsheet software for iOS.
- Numbers is Apple Inc.'s spreadsheet software, part of iWork.
- Quantrix Modeler, Multi-Dimensional Spreadsheet Software.
- ZCubes-Calc
- Discontinued spreadsheet software
- Advantage
- Borland's Quattro Pro
- Compucalc
- Enable
- Framework by Forefront Corporation/Ashton-Tate (1983/84)
- GNU Oleo – A traditional terminal mode spreadsheet for UNIX/UNIX-like systems
- IBM Lotus Symphony (2007)
- Javelin Software
- Lotus Improv
- Lotus Jazz for Macintosh
- Lotus Symphony (1984)
- MultiPlan
- Resolver One
- SuperCalc
- T/Maker
- Target Planner Calc for CP/M and TRS-DOS
- Trapeze for Macintosh
- Wingz for Macintosh
Other products
A number of companies have attempted to break into the spreadsheet market with programs based on very different paradigms. Lotus introduced what is likely the most successful example, Lotus Improv, which saw some commercial success, notably in the financial world where its powerful data mining capabilities remain well respected to this day.
Quantrix is built on the same paradigm as the discontinued Lotus Improv, except has many powerful new features, making it the application of choice for many financial professionals worldwide.
Spreadsheet 2000 attempted to dramatically simplify formula construction, but was generally not successful.
Great article.
Reply