Added to Favorites

Related Searches

Definitions

A spreadsheet is a computer application that simulates a paper worksheet. It displays multiple cells that together make up a grid consisting of rows and columns, each cell containing either alphanumeric text or numeric values. A spreadsheet cell may alternatively contain a formula that defines how the contents of that cell is to be calculated from the contents of any other cell (or combination of cells) each time any cell is updated. Spreadsheets are frequently used for financial information because of their ability to re-calculate the entire sheet automatically after a change to a single cell is made.

Visicalc is usually considered the first electronic spreadsheet (although this has been challenged), and it helped turn the Apple II computer into a success and greatly assisted in their widespread application. Lotus 1-2-3 was the leading spreadsheet of DOS era. Excel is now generally considered to have the largest market share.

The 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 main-frame computers in FORTRAN IV). Batch Spreadsheets dealt primarily with the addition or subtraction of entire columns or rows - rather than individual cells.

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. The software was developed 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 the incorporation of natural order recalculation, 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 natural order recalculation the users had to manually recalculate the spreadsheet as many times as necessary until the values in all the cells had stopped changing.

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.

The spreadsheet concept became widely known in the late 1970s and early 1980s because of Dan Bricklin's implementation of VisiCalc. VisiCalc was the first spreadsheet that combined all essential features of modern spreadsheet applications, 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. PC World magazine has 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 own it. In this case the computer was the Apple II, and VisiCalc was no small part in that machine'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, 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.

- A list of current spreadsheet software
- Gnumeric
- KSpread
- ZCubes-Calci
- Resolver One - a business application development tool that represents spreadsheets as IronPython programs, created and executed in real time and allowing the spreadsheet flow to be fully programmed
- A list of old spreadsheet software
- Advantage
- Boeing Calc 3D
- Lotus Improv
- Javelin Software
- Lotus Jazz for Macintosh
- Lucid 3D
- MultiPlan
- PowerStep for NeXT Step
- ProCalc
- Borland's Quattro Pro
- Silk
- SuperCalc
- Surpass
- Lotus Symphony
- TWIN
- TurboCalc
- VP Planner
- Wingz for Macintosh
- Target Planner Calc for CP/M and TRS-DOS

A | B | C | D | |
---|---|---|---|---|

01 | value1 | value2 | added | multiplied |

02 | 10 | 20 | 30 | 200 |

An array of cells is called a "sheet" or "worksheet". It is analogous to an array of variables in a conventional computer program (although certain unchanging values, once entered, could be considered, by the same analogy, constants). In most implementations, many worksheets may be located within a single spreadsheet. A worksheet is simply a subset of the spreadsheet divided for the sake of clarity. Functionally, the spreadsheet operates as a whole and all cells operate as global variables within the spreadsheet ('read' access only except its own containing cell).

A cell may contain a value or a formula, or it may simply be left empty. By convention, formulas usually begin with = sign.

The Spreadsheet Value RuleComputer scientist Alan Kay used the term value rule to summarize a spreadsheet's operation: a cell's value relies solely on the formula the user has typed into the cell. The formula may rely on the value of other cells, but those cells are likewise restricted to user-entered data or formulas. There are no 'side effects' to calculating a formula: the only output is to display the calculated result inside its occupying cell. There is no natural mechanism for permanently modifying the contents of a cell unless the user manually modifies the cell's contents. In the context of programming languages, this yields a limited form of first-order functional programming.

A formula identifies the calculation needed to place the result in the cell it is contained within. A cell containing a formula therefore has two display components; the formula itself and the resulting value. The formula is normally only shown when the cell is selected by "clicking" the mouse over a particular cell; otherwise it contains the result of the calculation.

A formula assigns values to a cell or range of cells, and typically has the format:

`=expression` |

- values, such as
`2`

,`9.14`

or`6.67E-11`

; - references to other cells, such as, e.g.,
`A1`

for a single cell or`B1:B3`

for a range; - arithmetic operators, such as
`+`

,`-`

,`*`

,`/`

, and others; - relational operators, such as
`>=`

,`<`

, and others; and, - functions, such as
`SUM()`

,`TAN()`

, and many others.

When a cell contains a formula, it often contains references to other cells. Such a cell reference is a type of variable. Its value is the value of the referenced cell or some derivation of it. If that cell in turn references other cells, the value depends on the values of those. References can be relative (e.g., `A1`

, or `B1:B3`

), absolute (e.g., `$A$1`

, or `$B$1:$B$3`

) or mixed row-wise or column-wise absolute/relative (e.g., `$A1`

is column-wise absolute and `A$1`

is row-wise absolute).

The available options for valid formulas depends on the particular spreadsheet implementation but, in general, most arithmetic operations and quite complex nested conditional operations can be performed by most of today's commercial spreadsheets. Modern implementations also offer functions to access custom-build functions, remote data, and applications.

A formula may contain a condition (or nested conditions) - with or without an actual calculation - and is sometimes used purely to identify and highlight errors. In the example below, it is assumed the sum of a column of percentages (A1 through A6) is tested for validity and an explicit message put into the adjacent right-hand cell.

=IF(SUM(A1:A6) > 100, "More than 100%", SUM(A1:A6))

A spreadsheet does not, in fact, have to contain any formulas at all, in which case it could be considered merely a collection of data arranged in rows and columns (a database) like a calendar, timetable or simple list. Because of its ease of use, formatting and hyperlinking capabilities, many spreadsheets are used solely for this purpose.

Some cell formats such as "numeric" or "currency" can also specify the number of decimal places.

This can allow invalid operations (such as doing multiplication on a cell containing a date), resulting in illogical results without an appropriate warning.

These attributes typically do not alter the data content in any way and some formatting may be lost or altered when copying spreadsheet data between different implementations or software versions. In some implementations, the format may be conditional upon the data within the cell - for example, a value may be displayed red if it is negative.

A typical cell reference in "A1" style consists of one or two case-insensitive letters to identify the column (if there are up to 256 columns: A-Z and AA-IV) followed by a row number (e.g. in the range 1-65536). Either part can be relative (it changes when the formula it is in is moved or copied), or absolute (indicated with $ in front of the part concerned of the cell reference). The older "R1C1" reference style consists of the letter R, the row number, the letter C, and the column number; relative row or column numbers are indicated by enclosing the number in square brackets. Most current spreadsheets use the A1 style, some providing the R1C1 style as a compatibility option.

When the computer calculates a formula in one cell to update the displayed value of that cell, cell reference(s) in that cell, naming some other cell(s), cause the computer to fetch the value of the named cell(s).

A cell on the same "sheet" is usually addressed as:-

=A1

A cell on a different sheet of the same spreadsheet is usually addressed as:-

=SHEET2!A1 (that is; the first cell in sheet 2 of same spreadsheet).

Some spreadsheet implementations allow a cell references to another spreadsheet (not the current open and active file) on the same computer or a local network. It may also refer to a cell in another open and active spreadsheet on the same computer or network that is defined as shareable. These references contain the complete filename, such as:-

='C:Documents and SettingsUsernameMy spreadsheets[main sheet]Sheet1!A1

In a spreadsheet, references to cells are automatically updated when new rows or columns are inserted or deleted. Care must be taken however when adding a row immediately before a set of column totals to ensure that the totals reflect the additional rows values - which often they do not!

A circular reference occurs when the formula in one cell has a reference that directly -- or indirectly, through a chain of references, each one pointing to another cell that has another reference to the next cell on the chain -- points to the one cell. Many common kinds of errors cause such circular references. However, there are some valid techniques that use such circular references. Such techniques, after many recalculations of the spreadsheet, (usually) converge on the correct values for those cells.

Many spreadsheet applications permit charts, graphs or histograms to be generated from specified groups of cells which are dynamically re-built as cell contents change. The generated graphic component can either be embedded within the current sheet or added as a separate object.

Spreadsheets have evolved into powerful programming languages; specifically, they are functional, visual, and multiparadigm languages.

Many people find it easier to perform calculations in spreadsheets than by writing the equivalent sequential program. This is due to two traits of spreadsheets.

- They use spatial relationships to define program relationships. Like all animals, humans have highly developed intuitions about spaces, and of dependencies between items. Sequential programming usually requires typing line after line of text, which must be read slowly and carefully to be understood and changed.
- They are forgiving, allowing partial results and functions to work. One or more parts of a program can work correctly, even if other parts are unfinished or broken. This makes writing and debugging programs much easier, and faster . Sequential programming usually needs every program line and character to be correct for a program to run. One error usually stops the whole program and prevents any result.

A spreadsheet program is designed to perform general computation tasks using spatial relationships rather than time as the primary organizing principle .

It is often convenient to think of a spreadsheet as a mathematical graph, where the nodes are spreadsheet cells, and the edges are references to other cells specified in formulas. This is often called the dependency graph of the spreadsheet. References between cells can take advantage of spatial concepts such as relative position and absolute position, as well as named locations, to make the spreadsheet formulas easier to understand and manage.

Spreadsheets usually attempt to automatically update cells when the cells on which they depend have been changed. The earliest spreadsheets used simple tactics like evaluating cells in a particular order, but modern spreadsheets compute a minimal recomputation order from the dependency graph. Later spreadsheets also include a limited ability to propagate values in reverse, altering source values so that a particular answer is reached in a certain cell. Since spreadsheet cells formulas are not generally invertible, though, this technique is of somewhat limited value.

Many of the concepts common to sequential programming models have analogues in the spreadsheet world. For example, the sequential model of the indexed loop is usually represented as a table of cells, with similar formulas (normally differing only in which cells they reference).

While spreadsheets are a great step forward in quantitative modeling, they have deficiencies. At the level of overall user benefits, spreadsheets have four main shortcomings.

- Spreadsheets have significant reliability problems. Research studies estimate that roughly 94% of spreadsheets deployed in the field contain errors, and 5.2% of cells in unaudited spreadsheets contain errors.
- The practical expressiveness of spreadsheets is limited. Several factors contribute to this limitation. Implementing a complex model requires implementing detailed layouts, cell-at-a-time. Authors have difficulty remembering the meanings of hundreds or thousands of cell addresses that appear in formulas.
- Collaboration in authoring spreadsheet formulas is difficult because such collaboration must occur at the level of cells and cell addresses. By comparison, programming languages aggregate cells with similar meaning into indexed variables with names that indicate meaning. Although some spreadsheets have good collaboration features, authoring at the level of cells and cell formulas remains a significant obstacle to collaboration in authoring spreadsheet models. On the other hand, many people collaborate on entering numerical data and many people can use the same spreadsheet.
- Productivity of spreadsheet modelers is reduced by the cell-level focus of spreadsheets. Even conceptually simple changes in spreadsheets (such as changing starting or ending time or time grain, adding new members or a level of hierarchy to a dimension, or changing one conceptual formula that is represented as hundreds of cell formulas) often require large numbers of manual cell-level operations (such as inserting or deleting cells/rows/columns, editing and copying formulas, re-laying out worksheets). Each of these manual corrections increases the risk of introducing further mistakes.

These four deficiencies in high-level benefits have deeper causes that, ironically, flow directly from the signature strength of spreadsheets (that they capture the structure of models in terms of WYSIWYG sheet layout for authors and report users).

- Spreadsheets capture model logic in terms of sheet layout, especially contiguous layout of cells in a table. Spreadsheets have weak or nonexistent methods to capture higher level structures such as named variables, segmentation dimensions, and time series.
- Formulas are subordinated to the cell layout. This forces the sheet layout to carry the structure of the model, not variables and formulas that relate variables. This also causes a large proliferation of cells, formulas and cell-level tasks even when only a few basic concepts are involved in a model. This forces authors to think and work at the level of cells instead of at the level of the natural concepts and structures of the model.
- Formulas expressed in terms of cell addresses are hard to keep straight and hard to audit. Research shows that spreadsheet auditors who check numerical results and cell formulas find no more errors than auditors who only check numerical results .
- Proliferation of error-prone manual cell-level operations contributes to all four of the high-level problems listed above.

Other problems associated with spreadsheets include:

- Some sources advocate the use of specialized software instead of spreadsheets for some applications (budgeting, statistics)
- Many spreadsheet software products, such as Excel (versions prior to 2007) and OpenOffice.org Calc, have a capacity limit of about 65,000 rows. This can present a problem for people using very large datasets, and may result in lost data.
- Lack of auditing and revision control. This makes it difficult to determine who changed what and when. This can cause problems with regulatory compliance. Lack of revision control greatly increases the risk of errors due the inability to track, isolate and test changes made to a document.
- Lack of security. Generally, if one has permission to open a spreadsheet, one has permission to modify any part of it. This, combined with the lack of auditing above, can make it easy for someone to commit fraud.
- Because they are loosely structured, it is easy for someone to introduce an error, either accidentally or intentionally, by entering information in the wrong place or expressing dependencies among cells (such as in a formula) incorrectly.
- The results of a formula (example "=A1*B1") applies only to a single cell (that is, the cell the formula is actually located in - in this case perhaps C1), even though it can "extract" data from many other cells, and even real time dates and actual times. This means that to cause a similar calculation on an array of cells, an almost identical formula (but residing in its own "output" cell) must be repeated for each row of the "input" array. This differs from a "formula" in a conventional computer program which would typically have one calculation which would then apply to all of the input in turn. With current spreadsheets, this forced repetition of near identical formulas can have detrimental consequences from a quality assurance standpoint and is often the cause of many spreadsheet errors. Some spreadsheets have array formulas to address this issue.
- Trying to manage the sheer volume of spreadsheets which sometimes exists within an organization without proper security, audit trails, the unintentional introduction of errors and other items listed above can become overwhelming.

While there are built-in and third-party tools for desktop spreadsheet applications that address some of these shortcomings, awareness and use of these is generally low.

- List of spreadsheets
- List of online spreadsheets
- Comparison of spreadsheet software
- Summation in spreadsheets
- Moving and copying in spreadsheets
- Attribute-value system
- Model Audit

- A Brief History of Spreadsheets by D.J. Power
- A History of Spreadsheets at ICI in 1974 by Ken Dakin
- The History of Mathematical Tables: From Sumer to Spreadsheets by Martin Campbell-Kelly, Mary Croarken, Raymond Flood, Eleanor Robson (Editors). (notice amazon.com)

- A Spreadsheet Programming article on DevX
- comp.apps.spreadsheets FAQ by Russell Schulz
- Develop Training Simulations with Excel
- Extending the Concept of Spreadsheet by Jocelyn Paine
- Linux Spreadsheets by Christopher Browne; much general information on spreadsheets, and some on related Linux issues
- Spreadsheets category on the Open Directory Project
- Spreadsheet - Its First Computerization (1961-1964) by Richard Mattessich
- "Spreadsheet Wars" - A classic video showing spreadsheet vendors going head-to-head in the late 80's .
- CICS history and introduction of IBM 3270 by Bob Yelavich
- Autoplan & Autotab article by Creative Karma
- A Wikibooks tutorial on [[Microsoft] EXCEL]
- Video tutorials to help beginners learn Microsoft Excel

Wikipedia, the free encyclopedia © 2001-2006 Wikipedia contributors (Disclaimer)

This article is licensed under the GNU Free Documentation License.

Last updated on Tuesday October 07, 2008 at 09:16:54 PDT (GMT -0700)

View this article at Wikipedia.org - Edit this article at Wikipedia.org - Donate to the Wikimedia Foundation

This article is licensed under the GNU Free Documentation License.

Last updated on Tuesday October 07, 2008 at 09:16:54 PDT (GMT -0700)

View this article at Wikipedia.org - Edit this article at Wikipedia.org - Donate to the Wikimedia Foundation

Copyright © 2014 Dictionary.com, LLC. All rights reserved.