Spreadsheet

A spreadsheet is a computer application for computation, organizing, and storing data. It works like a digital version of paper accounting worksheets. You can put numbers, words, or formulas into cells in a table. These formulas can calculate values using other cells.

In modern spreadsheets, many sheets, called "worksheets" or "sheets", are saved together in a file called a "workbook". You can switch between these sheets using tabs. When you refer to a cell in a multi-sheet book, you add the sheet name, for example, "Sheet 1!C10".

Spreadsheets are very useful for trying out different ideas, called "what-if" analysis. You can change values and see how it affects calculations quickly. They can do many kinds of math, including basic arithmetic, mathematical functions, and special financial accountancy tasks. People use them in business and many other places to organize and work with lists of information.

Basics

LANPAR, released in 1969, was the first electronic spreadsheet for big computers. VisiCalc, from 1979, was the first spreadsheet for small computers and helped make the Apple II very popular. Lotus 1-2-3 was the top spreadsheet when DOS was the main operating system. Today, Microsoft Excel is the most used spreadsheet on Windows and Macintosh computers. In 2006, Google launched Google Sheets, which is part of Google Drive.

A spreadsheet is a table with rows and columns. Each space in the table is called a cell. Columns are labeled with letters like "A" and "B," and rows are numbered like 1 and 2. You can refer to a cell by its column and row, like "C10." Cells can hold numbers, words, or dates. You can also put formulas in cells, which are special instructions that do calculations. For example, =5*3 would show the result 15.

The useful part of spreadsheets is that formulas can use values from other cells. If you write =5*C10 and cell C10 has the number 3, the result will be 15. You can even use formulas on groups of cells, like adding up a list of numbers with the SUM function.

History

Paper spreadsheets

People have been organizing information in tables for thousands of years. Ancient groups like the Babylonians used clay tablets to keep records as early as 1800 BCE. Later, people used paper ledgers and charts to track important data.

Since around 1906, the word "spreadsheet" has been used in accounting to describe these grid-like tables in ledgers. Before computers, spreadsheets were also called "spreads" because they often appeared across two pages of a book or on large sheets of paper.

Electronic spreadsheets

Batch spreadsheet report generator BSRG

A "batch" spreadsheet works like a computer program that processes data and creates reports. This idea was first described in a 1961 paper by Richard Mattessich. Later work by Mattessich used computers to handle accounting and budgeting on big machines called mainframe computers using a language named FORTRAN IV. These early spreadsheets added or subtracted entire rows or columns of numbers instead of single cells.

In 1962, a program named BCL for Business Computer Language was created for an IBM 1130 computer. In 1963, it was updated for an IBM 7040 by R. Brian Walsh at Marquette University, Wisconsin. This program was written in Fortran. A book about BCL was published in 1964.

In the late 1960s, Xerox improved BCL for their computer system.

LANPAR spreadsheet compiler

A big step in spreadsheet history was made by Rene K. Pardo and Remy Landau. In 1970, they applied for a patent for a spreadsheet system that could calculate results in the correct order. After many years, their invention was recognized in court in 1983. The software was named LANPAR — LANguage for Programming Arrays at Random. It was created in 1969 after Pardo and Landau graduated from Harvard University.

LANPAR was used by Bell Canada, AT&T, and other telephone companies for budgeting. It was also used by General Motors. Its special feature was that it could calculate results in the right order without needing the user to update the spreadsheet repeatedly.

Autoplan/Autotab spreadsheet programming language

In 1968, three former employees from General Electric in Phoenix, Arizona started their own software company. They created a program called AutoPlan to help make business tables easier. This program worked on GE's time-sharing service and later on IBM mainframes under the name AutoTab. Another similar product, CSSTAB, was offered by National CSS.

AutoPlan/AutoTab was not an interactive program with a visual interface. Instead, it was a simple scripting language where users defined names, labels, and formulas for rows and columns.

IBM Financial Planning and Control System

In 1976, Brian Ingham at IBM Canada developed the IBM Financial Planning and Control System. It ran on IBM mainframes and was one of the first programs for financial planning to hide the programming language from users. It updated automatically when new versions were released. Users could set simple math relationships between rows and columns. It could handle very large spreadsheets and loaded financial data each month.

APLDOT modeling language

An early example of a powerful spreadsheet was APLDOT, created in 1976 at the United States Railway Association on an IBM 360/91 computer. It was used for many years to help with financial and costing models for the US Congress and for Conrail. APLDOT was called a "spreadsheet" because analysts used it in similar ways to paper spreadsheets.

VisiCalc for the Apple II

The idea of spreadsheets became widely known because of VisiCalc, created for the Apple II in 1979 by Dan Bricklin and Bob Frankston. It turned personal computers into business tools.

VisiCalc was the first spreadsheet to include many key features like a visual interface, automatic updates, and easy formula creation. Dan Bricklin got the idea after watching a professor correct calculations on a blackboard. His idea became VisiCalc.

VisiCalc for the Apple II became so popular that people bought Apple II computers just to use it. It was later made to work on other computers like CP/M machines, Atari 8-bit computers, and the Commodore PET, but it is most famous as an Apple II program.

SuperCalc for CP/M

SuperCalc was a spreadsheet made by Sorcim in 1980. It was included with the CP/M software package for the Osborne 1 portable computer and quickly became the standard spreadsheet for CP/M.

Lotus 1-2-3 spreadsheet for IBM PC DOS

When Lotus 1-2-3 came out in November 1982, it helped make the IBM Personal Computer more popular. It was made for IBM PC DOS and had faster speed and better graphics than VisiCalc on the Apple II, which made it very popular.

Microsoft Excel for Apple Macintosh and Windows

Main article: Microsoft Excel

Microsoft created the first version of Excel for the Apple Macintosh on September 30, 1985. It was later made to work on Windows, with version 2.05 released in November 1987. Microsoft's Windows 3.x in the early 1990s helped Excel become more popular than Lotus 1-2-3. By 1995, Excel was the leading spreadsheet, and in 2013, IBM stopped making Lotus 1-2-3.

Google Sheets, Online, Web-based spreadsheets

See also: List of online spreadsheets

In 2006, Google launched Google Sheets, a web-based spreadsheet that multiple people could use from any device with a web browser. It could be used online or offline. Google Sheets started from a web-based spreadsheet called XL2Web, combined with DocVerse for teamwork on Office documents.

In 2016, Collabora Online Calc was released. This web-based spreadsheet could run on any system without needing third-party support. It used LibreOffice at its core, which came from StarOffice created in 1985.

Mainframe spreadsheets

• The Works Records System at ICI developed in 1974 on IBM 370/145
• ExecuCalc, from Parallax Systems, Inc.: Released in late 1982, ExecuCalc was the first mainframe "visi-clone" which duplicated the features of VisiCalc on IBM mainframes with 3270 display terminals. Over 150 copies were licensed (35 to Fortune 500 companies). DP managers were attracted to compatibility and avoiding then-expensive PC purchases (see 1983 Computerworld magazine front page article and advertisement.)

Other spreadsheets

Notable current spreadsheet software:

• Apache OpenOffice Calc is free and open-source.
• Calligra Sheets (formerly KCalc)
• Collabora Online Calc for mobile and desktop apps are free, open-source, cross-platform enterprise-ready editions of LibreOffice.
• Corel Quattro Pro (WordPerfect Office)
• Gnumeric is a free spreadsheet by the GNU project
• Kingsoft Spreadsheets
• LibreOffice Calc is free, open-source and cross platform.
• Numbers is Apple Inc.'s spreadsheet software, part of iWork.
• OnlyOffice Docs Spreadsheet editor is free and open source.
• PlanMaker (SoftMaker Office)
• Pyspread

Discontinued spreadsheet software:

• 20/20
• 3D-Calc for Atari ST computers
• As Easy As
• 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
• KCells
• Lucid 3-D
• Lotus Improv
• Lotus Jazz for Macintosh
• Lotus Symphony (1984)
• MultiPlan
• Claris' Resolve (Macintosh)
• NeoOffice
• Resolver One
• Borland's Quattro Pro
• SC IM (formerly SC - Spreadsheet Calculator)
• SIAG
• SuperCalc
• T/Maker
• Target Planner Calc for CP/M and TRS-DOS
• Wingz for Macintosh

Other products

Several companies tried to enter the spreadsheet market with new ideas. Lotus created Lotus Improv, which was successful in finance because of its strong data mining abilities.

Spreadsheet 2000 tried to make formulas much simpler but did not succeed widely.

Concepts

The main ideas are about a grid of cells, called a sheet, with either raw data, called values, or formulas in the cells. Formulas tell how to compute new values from existing ones. Values are general numbers, but can also be pure text, dates, months, etc.

A "cell" can be thought of as a box for holding data. A single cell is usually referenced by its column and row (C2 would represent the cell containing the value 30 in the example table below). Usually rows are referenced in decimal notation starting from 1, while columns use 26-adic bijective numeration using the letters A-Z as numerals. Its size can usually be changed by dragging its height or width.

An array of cells is called a sheet or worksheet. It is like an array of variables in a computer program. In most programs, many worksheets may be in one spreadsheet. A worksheet is a part of the spreadsheet divided for clarity. The spreadsheet works as a whole and all cells act like global variables.

A cell may contain a value or a formula, or it may be empty. Formulas usually start with = sign.

A value can be entered by typing into the cell. Alternatively, a value can come from a formula, which might do a calculation, show the current date or time, or get data from outside.

Computer scientist Alan Kay said a cell's value depends only on the formula the user typed. The formula may use values from other cells, but those cells also depend on user-entered data or formulas. There are no side effects—only the result is shown in the cell. There is no way to permanently change a cell unless the user does it manually.

Since the 1980s, spreadsheets usually recalculate values automatically. Some older spreadsheets needed a manual request to recalculate. Many still allow this option.

Recalculation needs no circular dependencies in a spreadsheet. A dependency graph shows what needs to be updated and when. Graphs without circular dependencies can give a definite result.

This feature updates a cell's contents with a value from an external source, such as another spreadsheet. For shared, Web-based spreadsheets, it updates cells another user has changed. All dependent cells must update too.

Selected cells or the whole spreadsheet can be "locked" to prevent accidental changes. This is often done for cells with formulas or constant values. Locking does not protect data until activated in file preferences.

A cell or range can be defined to specify how the value is displayed. The default format is set by the content, so "31/12/2007" would show as a date. Adding a % sign after a number tags it as a percentage. The cell content does not change, only the display.

Some formats like "numeric" or "currency" can set the number of decimal places.

This can cause invalid operations, like multiplying a date, leading to wrong results without a warning.

Depending on the program, each cell can be formatted separately using attributes like point size, color, bold, or italic. Cells can also have borders or background shading. Formatting may not move with the content when cells are copied.

In most programs, a cell or group of cells can be "named" so the user can refer to them by name instead of grid reference. Names must be unique. Named variables make spreadsheets easier to check and understand.

Cell reference

Instead of a named cell, you can use a cell reference. Most references are to another cell in the same sheet, but they can also refer to a cell in a different sheet or even another spreadsheet.

A typical cell reference in "A1" style uses letters for the column and numbers for the row (e.g., A1). Parts can be relative (change when moved) or absolute (start with $). The "R1C1" style uses R for row and C for column; relative parts are in square brackets. Most spreadsheets use A1 style.

When a formula updates a cell, cell references in that formula cause the computer to get 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

Some programs allow references to another spreadsheet on the same computer or network. These references include the filename, such as:

='C:\Documents and Settings\Username\My spreadsheets\[main sheet]Sheet1!A1

References update when new rows or columns are added or deleted. Care is needed when adding a row before column totals to ensure they update correctly.

A circular reference happens when a formula in one cell refers to another cell that refers back. This can cause errors, but some techniques use circular references to get correct values after many recalculations.

Cell ranges

Instead of a named range, a range reference can be used. A range reference looks like (A1:A6), which means all cells in column A from row 1 to row 6. A formula like "=SUM(A1:A6)" adds these cells and puts the result in the cell with the formula.

In early spreadsheets, cells were a simple grid. Over time, the model expanded to include more dimensions and named grids called sheets. Advanced examples allow data to be sliced and projected in various ways.

A formula identifies the calculation needed for the cell. A cell with a formula shows the formula when selected and the result otherwise. The formula has an expression with:

• values, like 2 or 9.14;
• references to other cells, like A1 or B1:B3;
• arithmetic operators, like + or -;
• relational operators, like >=.

Examples:

=IF(AND(A1<>"",B1<>""),A1/B1,"") checks if A1 and B1 are not empty, then divides A1 by B1.

=IF(AND(A1<>"",B1<>""),IF(B1<>0,A1/B1,"Division by zero"),"") checks if A1 and B1 are not empty and B1 is not zero, then divides, or shows "Division by zero".

=IF(OR(A1<>"",B1<>""),"Either A1 or B1 show text","") shows text if either A1 or B1 is not empty.

Spreadsheets do not need formulas—they can just hold data like a calendar or list. Because they are easy to use and format, many spreadsheets are used just for this.

Spreadsheets usually have several built-in functions, like arithmetic operations, trigonometric functions, and statistical functions. Some allow user-defined functions. In Microsoft Excel, these are defined using Visual Basic for Applications. Programs can pull information from the worksheet, do calculations, and report results back.

Functions return values but cannot write to the worksheet. However, in Microsoft Excel, subroutines can write values or text directly to the spreadsheet.

Whenever a reference is made to a cell outside the current spreadsheet, it accesses a "remote" spreadsheet. The contents can be updated manually or automatically in web-based spreadsheets.

Many spreadsheet programs can create charts and graphs (e.g., histograms, pie charts) from groups of cells. The graphic can be embedded in the sheet or added as a separate object.

In the late 1980s and early 1990s, Javelin Software and Lotus Improv appeared. Unlike regular spreadsheets, they used objects called variables. These multi-dimensional spreadsheets allowed viewing data and algorithms in many ways. Users could see connections between variables on a diagram.

In these programs, a time series was an object, not just cells. Variables could know their connections to other variables. Calculations were done on these objects. Data were independent of worksheets—variables could not be destroyed by deleting rows or columns.

Spreadsheets with formulas based on logical expressions, not arithmetic, are called logical spreadsheets. They can reason deductively about cell values.

Programming issues

Spreadsheets are a popular tool for people who want to create programs but aren't professional programmers. Many find spreadsheets easier for doing calculations than writing traditional programs. This is because spreadsheets show how things are connected in a space, which is simpler to understand than typing lines of code. They are also flexible, allowing parts of a program to work even if other parts aren't finished yet.

Spreadsheet programs are designed to handle many kinds of tasks using space instead of time to organize things. They can update automatically when information changes and have added tools from programming languages to do even more.

Shortcomings

Spreadsheets are very useful, but they can also have some problems. One issue is that the way cells are labeled with letters and numbers can feel confusing. Many people who use spreadsheets haven’t had special training, and often no one checks their work for mistakes.

Spreadsheets can be tricky for complex tasks because each cell must be handled one at a time. It’s easy to forget what each cell means, especially when there are many. Using named variables instead of cell addresses can help make spreadsheets easier to understand. When a formula needs to be changed, it can be difficult if it appears in many cells. Changing the size of a table by adding or removing rows and columns can take a lot of time.

Working together on spreadsheets can be challenging when everyone is editing individual cells.

There are other problems too. Some people think other types of software might be better for certain tasks like budgeting or statistics. Older versions of a popular spreadsheet program could only handle a limited number of rows and columns. Without proper checks, it’s easy for mistakes to slip into spreadsheets. Because spreadsheets don’t always track who made changes or when, it can be hard to follow rules or catch errors.

Spreadsheet risk

Further information: Financial modeling § Accounting, and List of spreadsheet mistakes

Spreadsheet risk is the chance of getting the wrong answer from a spreadsheet. People use spreadsheets to make important decisions. Mistakes can happen if someone puts in the wrong numbers, makes a calculation error, or forgets to update important information, like exchange rates. These mistakes can sometimes cost a lot of money.

Many big companies don’t have strong rules to check their spreadsheets, which makes mistakes more likely. In one well-known case, a student found big mistakes in a spreadsheet used for important economic decisions.