spread sheet
   HOME

TheInfoList



OR:

A spreadsheet is a computer application for computation, organization, analysis and storage of
data In the pursuit of knowledge, data (; ) is a collection of discrete Value_(semiotics), values that convey information, describing quantity, qualitative property, quality, fact, statistics, other basic units of meaning, or simply sequences of sy ...
in
tabular Table may refer to: * Table (furniture), a piece of furniture with a flat surface and one or more legs * Table (landform), a flat area of land * Table (information), a data arrangement with rows and columns * Table (database), how the table data ...
form. Spreadsheets were developed as computerized analogs of paper accounting worksheets. The program operates on data entered in cells of a table. Each cell 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. The term ''spreadsheet'' may also refer to one such electronic document. Spreadsheet users can 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 function In mathematics, a function from a set to a set assigns to each element of exactly one element of .; the words map, mapping, transformation, correspondence, and operator are often used synonymously. The set is called the domain of the functi ...
s, modern spreadsheets provide built-in functions for common
financial accountancy Financial accounting is the field of accounting concerned with the summary, analysis and reporting of financial transactions related to a business. This involves the preparation of financial statements available for public use. Stockholders, s ...
and statistical operations. Such calculations as
net present value The net present value (NPV) or net present worth (NPW) applies to a series of cash flows occurring at different times. The present value of a cash flow depends on the interval of time between now and the cash flow. It also depends on the discount ...
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.


Basics

LANPAR, available in 1969, was the first electronic spreadsheet on mainframe and time sharing computers. LANPAR was an acronym: LANguage for Programming Arrays at Random.
VisiCalc VisiCalc (for "visible calculator") is the first spreadsheet computer program for personal computers, originally released for Apple II by VisiCorp on 17 October 1979. It is often considered the application that turned the microcomputer from a hob ...
(1979) 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 Lotus 1-2-3 is a discontinued spreadsheet program from Lotus Software (later part of IBM). It was the first killer application of the IBM PC, was hugely popular in the 1980s, and significantly contributed to the success of IBM PC-compatibles i ...
was the leading spreadsheet when
DOS DOS is shorthand for the MS-DOS and IBM PC DOS family of operating systems. DOS may also refer to: Computing * Data over signalling (DoS), multiplexing data onto a signalling channel * Denial-of-service attack (DoS), an attack on a communicat ...
was the dominant operating system.
Microsoft Excel Microsoft Excel is a spreadsheet developed by Microsoft for Windows, macOS, Android and iOS. It features calculation or computation capabilities, graphing tools, pivot tables, and a macro programming language called Visual Basic for App ...
now has the largest market share on the
Windows Windows is a group of several proprietary graphical operating system families developed and marketed by Microsoft. Each family caters to a certain sector of the computing industry. For example, Windows NT for consumers, Windows Server for ser ...
and
Macintosh The Mac (known as Macintosh until 1999) is a family of personal computers designed and marketed by Apple Inc. Macs are known for their ease of use and minimalist designs, and are popular among students, creative professionals, and software en ...
platforms. A spreadsheet program is a standard feature of an
office productivity suite An office is a space where an organization's employees perform administrative work in order to support and realize objects and goals of the organization. The word "office" may also denote a position within an organization with specific dut ...
; since the advent of
web apps A web application (or web app) is application software that is accessed using a web browser. Web applications are delivered on the World Wide Web to users with an active network connection. History In earlier computing models like client-serve ...
, office suites now also exist in web app form. 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". This electronic concept of cell references was first introduced in LANPAR (Language for Programming Arrays at Random) (co-invented by Rene Pardo and Remy Landau) and a variant used in VisiCalc and known as "A1 notation". 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". LANPAR innovated forward referencing/natural order calculation which didn't re-appear until Lotus 123 and Microsoft's MultiPlan Version 2. In modern spreadsheet applications, several spreadsheets, often known as ''worksheets'' or simply ''sheets'', 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
Numbers A number is a mathematical object used to count, measure, and label. The original examples are the natural numbers 1, 2, 3, 4, and so forth. Numbers can be represented in language with number words. More universally, individual numbers can ...
changes 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 number 5 or the date 16-Dec-91. 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 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 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 steps, 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
database In computing, a database is an organized collection of data stored and accessed electronically. Small databases can be stored on a file system, while large databases are hosted on computer clusters or cloud storage. The design of databases s ...
s, but spreadsheets and databases are not the same things. A spreadsheet is essentially just one table, whereas a database is a collection of many tables with machine-readable semantic relationships. 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 An import is the receiving country in an export from the sending country. Importation and exportation are the defining financial transactions of international trade. In international trade, the importation and exportation of goods are limited ...
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 An office is a space where an organization's employees perform administrative work in order to support and realize objects and goals of the organization. The word "office" may also denote a position within an organization with specific dut ...
, which usually also contains a
word processor A word processor (WP) is a device or computer program that provides for input, editing, formatting, and output of text, often with some additional features. Early word processors were stand-alone devices dedicated to the function, but current ...
, a
presentation program A computing, a presentation program (also called presentation software) is a software package used to display information in the form of a slide show. It has three major functions: * an editor that allows text to be inserted and formatted * a ...
, and a
database In computing, a database is an organized collection of data stored and accessed electronically. Small databases can be stored on a file system, while large databases are hosted on computer clusters or cloud storage. The design of databases s ...
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 The GUI ( "UI" by itself is still usually pronounced . or ), graphical user interface, is a form of user interface that allows users to interact with electronic devices through graphical icons and audio indicator such as primary notation, inst ...
.


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 centerfold and treating the two pages as one large page. 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 BSRG

A
batch Batch may refer to: Food and drink * Batch (alcohol), an alcoholic fruit beverage * Batch loaf, a type of bread popular in Ireland * A dialect term for a bread roll used in North Warwickshire, Nuneaton and Coventry, as well as on the Wirra ...
"spreadsheet" is indistinguishable from a batch compiler with added input data, producing an output report, ''i.e.'', a 4GL 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 Richard Victor Alvarus Mattessich (August 9, 1922 – September 30, 2019) was an Austrian-Canadian business economist and Emeritus Professor of Accounting at the University of British Columbia, known for introducing the concept of electronic spread ...
. 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 1130 The IBM 1130 Computing System, introduced in 1965, was IBM's least expensive computer at that time. A binary 16-bit machine, it was marketed to price-sensitive, computing-intensive technical markets, like education and engineering, succeeding th ...
and in 1963 was
ported In software engineering, porting is the process of adapting software for the purpose of achieving some form of execution in a computing environment that is different from the one that a given program (meant for such execution) was originally desi ...
to an IBM 7040 by R. Brian Walsh at
Marquette University Marquette University () is a private Jesuit research university in Milwaukee, Wisconsin. Established by the Society of Jesus as Marquette College on August 28, 1881, it was founded by John Martin Henni, the first Bishop of the diocese of M ...
,
Wisconsin Wisconsin () is a state in the upper Midwestern United States. Wisconsin is the 25th-largest state by total area and the 20th-most populous. It is bordered by Minnesota to the west, Iowa to the southwest, Illinois to the south, Lake M ...
. This program was written in Fortran. Primitive timesharing was available on those machines. In 1968 BCL was ported by Walsh to the
IBM 360 The IBM System/360 (S/360) is a family of mainframe computer systems that was announced by IBM on April 7, 1964, and delivered between 1965 and 1978. It was the first family of computers designed to cover both commercial and scientific applica ...
/67 timesharing machine at
Washington State University Washington State University (Washington State, WSU, or informally Wazzu) is a public land-grant research university with its flagship, and oldest, campus in Pullman, Washington. Founded in 1890, WSU is also one of the oldest land-grant uni ...
. It was used to assist in the teaching of finance to business students. Students were able to take information prepared by the
professor Professor (commonly abbreviated as Prof.) is an academic rank at universities and other post-secondary education and research institutions in most countries. Literally, ''professor'' derives from Latin as a "person who professes". Professors ...
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 A copyright is a type of intellectual property that gives its owner the exclusive right to copy, distribute, adapt, display, and perform a creative work, usually for a limited time. The creative work may be in a literary, artistic, educatio ...
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 on a spreadsheet automatic natural order calculation
algorithm In mathematics and computer science, an algorithm () is a finite sequence of rigorous instructions, typically used to solve a class of specific problems or to perform a computation. Algorithms are used as specifications for performing ...
. 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 Predecessor Court of the Federal Circuit (CCPA), 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 The United States Court of Appeals for the Federal Circuit (in case citations, Fed. Cir. or C.A.F.C.) is a United States court of appeals that has special appellate jurisdiction over certain types of specialized cases in the U.S. federal court ...
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 an electronic computer calculate 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 telephone companies 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 VisiCalc (for "visible calculator") is the first spreadsheet computer program for personal computers, originally released for Apple II by VisiCorp on 17 October 1979. It is often considered the application that turned the microcomputer from a hob ...
,
SuperCalc SuperCalc is a CP/M-80 spreadsheet application published by Sorcim in 1980. History VisiCalc was the first spreadsheet program but its release for the CP/M operating system ran only on the HP-125, Sharp MZ80, and the Sony SMC-70. SuperCalc w ...
, and the first version of
MultiPlan Multiplan is spreadsheet program developed by Microsoft and introduced in 1982 as a competitor to VisiCalc. Multiplan was released first for computers running CP/M; it was developed using a Microsoft proprietary p-code C compiler as part of ...
. Without forward referencing/natural order calculation, the user had to refresh the spreadsheet until the values in all cells remained unchanged. Once the cell values stayed constant, the user was assured that there were no remaining forward references within the spreadsheet.


Autoplan/Autotab spreadsheet programming language

In 1968, three former employees from the
General Electric General Electric Company (GE) is an American multinational conglomerate founded in 1892, and incorporated in New York state and headquartered in Boston. The company operated in sectors including healthcare, aviation, power, renewable en ...
computer company headquartered in
Phoenix, Arizona Phoenix ( ; nv, Hoozdo; es, Fénix or , yuf-x-wal, Banyà:nyuwá) is the capital and most populous city of the U.S. state of Arizona, with 1,608,139 residents as of 2020. It is the fifth-most populous city in the United States, and the on ...
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 Capex Corporation was an American computer software company in existence from 1969 through 1982 and based in Phoenix, Arizona. It made a variety of software products, mostly system utilities for the IBM mainframe platform, and was known for its Op ...
. "AutoPlan" ran on GE's
Time-sharing In computing, time-sharing is the sharing of a computing resource among many users at the same time by means of multiprogramming and multi-tasking.DEC Timesharing (1965), by Peter Clark, The DEC Professional, Volume 1, Number 1 Its emergence ...
service; afterward, a version that ran on
IBM mainframe IBM mainframes are large computer systems produced by IBM since 1952. During the 1960s and 1970s, IBM dominated the large computer market. Current mainframe computers in IBM's line of business computers are developments of the basic design of th ...
s was introduced under the name ''AutoTab''. (
National CSS National CSS, Inc. (NCSS) was a time-sharing firm in the 1960–80s, until its acquisition by Dun & Bradstreet in 1979. NCSS was originally headquartered in Norwalk, Connecticut, but relocated to Wilton in 1978. Sales offices, data centers, and ...
offered 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 In computing, WYSIWYG ( ), an acronym for What You See Is What You Get, is a system in which editing software allows content to be edited in a form that resembles its appearance when printed or displayed as a finished product, such as a printed d ...
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. In 1975, Autotab-II was advertised as extending the original to a maximum of "''1,500 rows and columns, combined in any proportion the user requires...''" GE Information Services, which operated the time-sharing service, also launched its own spreadsheet system, Financial Analysis Language (FAL), circa 1974. It was later supplemented by an additional spreadsheet language, TABOL, which was developed by an independent author, Oliver Vellacott in the UK. Both FAL and TABOL were integrated with GEIS's database system, DMS.


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 IBM mainframes are large computer systems produced by IBM since 1952. During the 1960s and 1970s, IBM dominated the large computer market. Current mainframe computers in IBM's line of business computers are developments of the basic design of th ...
and was among the first applications for
financial planning In general usage, a financial plan is a comprehensive evaluation of an individual's current pay and future financial state by using current known variables to predict future income, asset values and withdrawal plans. This often includes a bu ...
developed with APL that completely hid the programming language from the end-user. Through IBM's
VM operating system VM (often: VM/CMS) is a family of IBM virtual machine operating systems used on IBM mainframes System/370, System/390, zSeries, System z and compatible systems, including the Hercules emulator for personal computers. The following versi ...
, 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 planning In general usage, a financial plan is a comprehensive evaluation of an individual's current pay and future financial state by using current known variables to predict future income, asset values and withdrawal plans. This often includes a bu ...
data In the pursuit of knowledge, data (; ) is a collection of discrete Value_(semiotics), values that convey information, describing quantity, qualitative property, quality, fact, statistics, other basic units of meaning, or simply sequences of sy ...
drawn from the legacy batch system into each user's spreadsheet monthly. 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 The United States Railway Association (USRA) was a government-owned corporation created by United States federal law that oversaw the creation of Conrail, a railroad corporation that would acquire and operate bankrupt and other failing freight rai ...
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
Dan Bricklin Daniel Singer Bricklin (born July 16, 1951) is an American businessman and engineer who is the co-creator, with Bob Frankston, of the VisiCalc spreadsheet program. He also founded Software Garden, Inc., of which he is currently president, and T ...
and
Bob Frankston Robert M. Frankston (born June 14, 1949) is an American software engineer and businessman who co-created, with Dan Bricklin, the VisiCalc spreadsheet program. Frankston is also the co-founder of Software Arts. Early life and education Franksto ...
implemented
VisiCalc VisiCalc (for "visible calculator") is the first spreadsheet computer program for personal computers, originally released for Apple II by VisiCorp on 17 October 1979. It is often considered the application that turned the microcomputer from a hob ...
on the Apple II in 1979 and the IBM PC in 1981, the spreadsheet concept became widely known in the 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 In computing, WYSIWYG ( ), an acronym for What You See Is What You Get, is a system in which editing software allows content to be edited in a form that resembles its appearance when printed or displayed as a finished product, such as a printed d ...
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 ''PC World'' (stylized as PCWorld) is a global computer magazine published monthly by IDG. Since 2013, it has been an online only publication. It offers advice on various aspects of PCs and related items, the Internet, and other personal tech ...
'' 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 several 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 A personal computer (PC) is a multi-purpose microcomputer whose size, capabilities, and price make it feasible for individual use. Personal computers are intended to be operated directly by an end user, rather than by a computer expert or tec ...
from a hobby for computer enthusiasts into a business tool. VisiCalc went on to become the first "
killer application In marketing terminology, a killer application (commonly shortened to killer app) is any computer program or software that is so necessary or desirable that it proves the core value of some larger technology, such as computer hardware, a video game ...
","Killer Applications" (overview), Partha gawaargupta. Arizona State University in Tempe, Arizona, May 2002, Web page
ASU-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 In software engineering, porting is the process of adapting software for the purpose of achieving some form of execution in a computing environment that is different from the one that a given program (meant for such execution) was originally desi ...
to a number of other early computers, notably CP/M machines, the Atari 8-bit family and various
Commodore Commodore may refer to: Ranks * Commodore (rank), a naval rank ** Commodore (Royal Navy), in the United Kingdom ** Commodore (United States) ** Commodore (Canada) ** Commodore (Finland) ** Commodore (Germany) or ''Kommodore'' * Air commodore ...
platforms. Nevertheless, VisiCalc remains best known as an Apple II program.


SuperCalc

SuperCalc SuperCalc is a CP/M-80 spreadsheet application published by Sorcim in 1980. History VisiCalc was the first spreadsheet program but its release for the CP/M operating system ran only on the HP-125, Sharp MZ80, and the Sony SMC-70. SuperCalc w ...
was a spreadsheet application published by Sorcim in 1980, and originally bundled (along with WordStar) as part of the CP/M software package included with the Osborne 1 portable computer. It quickly became the de facto standard spreadsheet for CP/M and was ported to MS-DOS in 1982.


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 Lotus 1-2-3 is a discontinued spreadsheet program from Lotus Software (later part of IBM). It was the first killer application of the IBM PC, was hugely popular in the 1980s, and significantly contributed to the success of IBM PC-compatibles i ...
in November 1982, and release for sale in January 1983. Since it was written especially for the IBM PC, it had a 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 VisiCalc (for "visible calculator") is the first spreadsheet computer program for personal computers, originally released for Apple II by VisiCorp on 17 October 1979. It is often considered the application that turned the microcomputer from a hob ...
the very same year, and for several years was the leading spreadsheet for
DOS DOS is shorthand for the MS-DOS and IBM PC DOS family of operating systems. DOS may also refer to: Computing * Data over signalling (DoS), multiplexing data onto a signalling channel * Denial-of-service attack (DoS), an attack on a communicat ...
.


Microsoft Excel

Microsoft Microsoft Corporation is an American multinational technology corporation producing computer software, consumer electronics, personal computers, and related services headquartered at the Microsoft Redmond campus located in Redmond, Washin ...
released the first version of
Excel ExCeL London (an abbreviation for Exhibition Centre London) is an exhibition centre, international convention centre and former hospital in the Custom House area of Newham, East London. It is situated on a site on the northern quay of the ...
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 An office is a space where an organization's employees perform administrative work in order to support and realize objects and goals of the organization. The word "office" may also denote a position within an organization with specific du ...
suite. By 1995, Excel was the market leader, edging out Lotus 1-2-3, and in 2013, IBM discontinued Lotus 1-2-3 altogether.


Web-based spreadsheets

Notable current web-based spreadsheet software: * Collabora Online Calc is a free, open-source and
cross-platform In computing, cross-platform software (also called multi-platform software, platform-agnostic software, or platform-independent software) is computer software that is designed to work in several computing platforms. Some cross-platform software ...
enterprise-ready edition of LibreOffice.
Ethercalc
is a simple open-source collaborative spreadsheet that runs in browsers. *
Google Sheets Google Sheets is a spreadsheet program included as part of the free, web-based Google Docs Editors suite offered by Google. The service also includes: Google Docs, Google Slides, Google Drawings, Google Forms, Google Sites and Google Keep. G ...
* Microsoft Excel Online


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 a free and open-source. *
Calligra Sheets Calligra Sheets (formerly ''KSpread'' and ''Calligra Tables'') is a free software spreadsheet application that is part of Calligra Suite, an integrated graphic art and office suite developed by KDE. Among Sheets’ features are multiple sheets ...
(formerly KCalc) * Collabora Online Calc for mobile and desktop apps are free, open-source,
cross-platform In computing, cross-platform software (also called multi-platform software, platform-agnostic software, or platform-independent software) is computer software that is designed to work in several computing platforms. Some cross-platform software ...
enterprise-ready editions of LibreOffice. *
Corel Cascade Parent Limited, doing business as Alludo (pronounced like "all you do"), is a Canadian software company headquartered in Ottawa, Ontario, specializing in graphics processing. Formerly called the Corel Corporation ( ; from the abbreviatio ...
Quattro Pro Quattro Pro is a spreadsheet program developed by Borland and now sold by Corel, most often as part of Corel's WordPerfect Office suite. Characteristics Historically, Quattro Pro used keyboard commands close to those of Lotus 1-2-3. While i ...
( WordPerfect Office) *
Gnumeric Gnumeric is a spreadsheet program that is part of the GNOME Free Software Desktop Project. Gnumeric version 1.0 was released on 31 December 2001. Gnumeric is distributed as free software under the GNU General Public License; it is intended to r ...
is free and cross-platform, it is part of the GNOME Free Software Desktop Project. * Kingsoft Spreadsheets *
LibreOffice Calc LibreOffice Calc is the spreadsheet component of the LibreOffice software package. After forking from OpenOffice.org in 2010, LibreOffice Calc underwent a massive re-work of external reference handling to fix many defects in formula calculation ...
is free, open-source and cross platform. *
NeoOffice NeoOffice is an office suite for the macOS operating system developed by Planamesa Inc. It is a commercial fork of the free and open source OpenOffice.org that implements most of the features of OpenOffice.org, including a word processor, spreads ...
*
Numbers A number is a mathematical object used to count, measure, and label. The original examples are the natural numbers 1, 2, 3, 4, and so forth. Numbers can be represented in language with number words. More universally, individual numbers can ...
is
Apple Inc. Apple Inc. is an American multinational technology company headquartered in Cupertino, California, United States. Apple is the largest technology company by revenue (totaling in 2021) and, as of June 2022, is the world's biggest company ...
's spreadsheet software, part of
iWork iWork is an office suite of applications created by Apple Inc. for its macOS and iOS operating systems, and also available cross-platform through the iCloud website. It includes the presentation application Keynote, the word processing and ...
. *
PlanMaker PlanMaker is a spreadsheet program that is part of the SoftMaker Office suite. Versions are available on Microsoft Windows, MacOS, Linux and Apps for Android and iOS. PlanMaker is largely similar to Microsoft Excel in function and workflow ...
(
SoftMaker Office SoftMaker Office is an office suite developed since 1987 by the German company SoftMaker Software GmbH based in Nuremberg. A freeware version is released as well, under the name of SoftMaker FreeOffice. Components SoftMaker Office includ ...
) *
Pyspread Pyspread is a non-traditional spreadsheet. Cells in pyspread's grid accept expressions in the Python (programming language), Python programming language. A cell can return any Python object, which allows calculations with Vector (mathematics and p ...
Discontinued spreadsheet software: *
20/20 Visual acuity (VA) commonly refers to the clarity of vision, but technically rates an examinee's ability to recognize small details with precision. Visual acuity is dependent on optical and neural factors, i.e. (1) the sharpness of the retinal ...
*
3D-Calc 3D-Calc is a 3-dimensional spreadsheet program for the Atari ST computer. The first version of the program was released in April 1989 and was distributed by ISTARI bvba, Ghent, Belgium. History Starting May 1991, the English version was distribu ...
for Atari ST computers * Framework by Forefront Corporation/ Ashton-Tate (1983–84) *
GNU Oleo GNU Oleo is a discontinued lightweight free software spreadsheet originally designed as a text-based spreadsheet using the curses library. The last development version of Oleo, 1.99.16, was released in 2001. History The project was started in 19 ...
 – A traditional terminal mode spreadsheet for UNIX/UNIX-like systems *
IBM Lotus Symphony IBM Lotus Symphony is a discontinued suite of applications for creating, editing, and sharing text, spreadsheet, presentations, and other documents and browsing the World Wide Web. It was first distributed as commercial proprietary software, t ...
(2007) *
Javelin Software Javelin Software Corporation (1984–1988) was a company in Cambridge, Massachusetts, USA, which developed an innovative modeling and data analysis product, also called ''Javelin'' (versions 1.0 in 1984 to 1.1), and later ''Javelin Plus'' (vers ...
* KCells * Lucid 3-D *
Lotus Improv Lotus Improv is a discontinued spreadsheet program from Lotus Development released in 1991 for the NeXTSTEP platform and then for Windows 3.1 in 1993. Development was put on hiatus in 1994 after slow sales on the Windows platform, and officially ...
*
Lotus Jazz Lotus Jazz is an integrated suite of word processor, spreadsheet, database, graphics, and communication software designed for the Macintosh 512K. The name evokes a group of musicians who together create something larger than each of the individua ...
for Macintosh * Lotus Symphony (1984) *
MultiPlan Multiplan is spreadsheet program developed by Microsoft and introduced in 1982 as a competitor to VisiCalc. Multiplan was released first for computers running CP/M; it was developed using a Microsoft proprietary p-code C compiler as part of ...
*Claris'
Resolve Resolve may refer to: * ''Resolve'' (Lagwagon album) * ''Resolve'' (Last Tuesday album) * "Resolve" (song), by the Foo Fighters *'' The Resolve'', a 1915 American silent short drama film * "Resolve" (''One Tree Hill'' episode) *''Resolve'', a Brit ...
(Macintosh) *
Resolver One Spreadsheet is a class of application software design to analyze tabular data called "worksheets". A collection of worksheets is called a "workbook". Online spreadsheets do not depend on a particular operating system but require a standards-compli ...
*Borland's
Quattro Pro Quattro Pro is a spreadsheet program developed by Borland and now sold by Corel, most often as part of Corel's WordPerfect Office suite. Characteristics Historically, Quattro Pro used keyboard commands close to those of Lotus 1-2-3. While i ...
* SIAG *
SuperCalc SuperCalc is a CP/M-80 spreadsheet application published by Sorcim in 1980. History VisiCalc was the first spreadsheet program but its release for the CP/M operating system ran only on the HP-125, Sharp MZ80, and the Sony SMC-70. SuperCalc w ...
*
T/Maker T/Maker (Table Maker) was one of the first spreadsheet programs designed for the personal computer user and released by Peter Roizen in 1979. The application ran on CP/M, TRSDOS, and later on MS-DOS computers. T/Maker was originally distributed ...
*Target Planner Calc for CP/M and TRS-DOS *Trapeze for Macintosh * Wingz for Macintosh


Other products

Several 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 Lotus Improv is a discontinued spreadsheet program from Lotus Development released in 1991 for the NeXTSTEP platform and then for Windows 3.1 in 1993. Development was put on hiatus in 1994 after slow sales on the Windows platform, and officially ...
, which saw some commercial success, notably in the financial world where its powerful data mining capabilities remain well respected to this day. Spreadsheet 2000 attempted to dramatically simplify formula construction, but was generally not successful.


Concepts

The main concepts are those of a grid of cells, called a sheet, with either raw data, called values, or formulas in the cells. Formulas say how to mechanically compute new values from existing values. Values are general numbers, but can also be pure text, dates, months, etc. Extensions of these concepts include logical spreadsheets. Various tools for programming sheets, visualizing data, remotely connecting sheets, displaying cells' dependencies, etc. are commonly provided.


Cells

A "cell" can be thought of as a box for holding
data In the pursuit of knowledge, data (; ) is a collection of discrete Value_(semiotics), values that convey information, describing quantity, qualitative property, quality, fact, statistics, other basic units of meaning, or simply sequences of sy ...
. 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, representing the
dependent variables Dependent and independent variables are variables in mathematical modeling, statistical modeling and experimental sciences. Dependent variables receive this name because, in an experiment, their values are studied under the supposition or demand ...
, are referenced in
decimal notation The decimal numeral system (also called the base-ten positional numeral system and denary or decanary) is the standard system for denoting integer and non-integer numbers. It is the extension to non-integer numbers of the Hindu–Arabic numer ...
starting from 1, while columns representing the
independent variables Dependent and independent variables are variables in mathematical modeling, statistical modeling and experimental sciences. Dependent variables receive this name because, in an experiment, their values are studied under the supposition or deman ...
use 26-adic
bijective numeration Bijective numeration is any numeral system in which every non-negative integer can be represented in exactly one way using a finite string of digits. The name refers to the bijection (i.e. one-to-one correspondence) that exists in this case betw ...
using the letters A-Z as numerals. Its physical size can usually be tailored to its content by dragging its height or width at box intersections (or for entire columns or rows by dragging the column- or row-headers). An array of cells is called a ''sheet'' or ''worksheet''. It is analogous to an array of variables in a conventional
computer program A computer program is a sequence or set of instructions in a programming language for a computer to execute. Computer programs are one component of software, which also includes documentation and other intangible components. A 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 variable In computer programming, a global variable is a variable with global scope, meaning that it is visible (hence accessible) throughout the program, unless shadowed. The set of all global variables is known as the ''global environment'' or ''global s ...
s within the spreadsheet (each variable having 'read' access only except its containing cell). A cell may contain a
value Value or values may refer to: Ethics and social * Value (ethics) wherein said concept may be construed as treating actions themselves as abstract objects, associating value to them ** Values (Western philosophy) expands the notion of value beyo ...
or a formula, or it may simply be left empty. By convention, formulas usually begin with = sign.


Values

A value can be entered from the computer keyboard by directly typing into the cell itself. Alternatively, a value can be based on a formula (see below), which might perform a calculation, display the current date or time, or retrieve external data such as a stock quote or a database value.
The Spreadsheet ''Value Rule'' Computer scientist
Alan Kay Alan Curtis Kay (born May 17, 1940) published by the Association for Computing Machinery 2012 is an American computer scientist best known for his pioneering work on object-oriented programming and windowing graphical user interface (GUI) d ...
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 In computer science, functional programming is a programming paradigm where programs are constructed by applying and composing functions. It is a declarative programming paradigm in which function definitions are trees of expressions that ...
.


Automatic recalculation

A standard of spreadsheets since the 1980s, this optional feature eliminates the need to manually request the spreadsheet program to recalculate values (nowadays typically the default option unless specifically 'switched off' for large spreadsheets, usually to improve performance). Some earlier spreadsheets required a manual request to recalculate since the recalculation of large or complex spreadsheets often reduced data entry speed. Many modern spreadsheets still retain this option. Recalculation generally requires that there are no
circular dependencies In software engineering, a circular dependency is a relation between two or more modules which either directly or indirectly depend on each other to function properly. Such modules are also known as mutually recursive. Overview Circular depen ...
in a spreadsheet. A
dependency graph In mathematics, computer science and digital electronics, a dependency graph is a directed graph representing dependencies of several objects towards each other. It is possible to derive an evaluation order or the absence of an evaluation order t ...
is a graph that has a vertex for each object to be updated, and an edge connecting two objects whenever one of them needs to be updated earlier than the other. Dependency graphs without circular dependencies form
directed acyclic graph In mathematics, particularly graph theory, and computer science, a directed acyclic graph (DAG) is a directed graph with no directed cycles. That is, it consists of vertices and edges (also called ''arcs''), with each edge directed from one v ...
s, representations of partial orderings (in this case, across a spreadsheet) that can be relied upon to give a definite result.


Real-time update

This feature refers to updating a cell's contents periodically with a value from an external source—such as a cell in a "remote" spreadsheet. For shared, Web-based spreadsheets, it applies to "immediately" updating cells another user has updated. All dependent cells must be updated also.


Locked cell

Once entered, selected cells (or the entire spreadsheet) can optionally be "locked" to prevent accidental overwriting. Typically this would apply to cells containing formulas but might apply to cells containing "constants" such as a kilogram/pounds conversion factor (2.20462262 to eight decimal places). Even though individual cells are marked as locked, the spreadsheet data are not protected until the feature is activated in the file preferences.


Data format

A cell or range can optionally be defined to specify how the value is displayed. The default display format is usually set by its initial content if not specifically previously set, so that for example "31/12/2007" or "31 Dec 2007" would default to the cell format of ''date''. Similarly adding a % sign after a numeric value would tag the cell as a percentage cell format. The cell contents are not changed by this format, only the displayed value. Some cell formats such as "numeric" or "currency" can also specify the number of
decimal place Significant figures (also known as the significant digits, ''precision'' or ''resolution'') of a number in positional notation are digits in the number that are reliable and necessary to indicate the quantity of something. If a number expres ...
s. This can allow invalid operations (such as doing multiplication on a cell containing a date), resulting in illogical results without an appropriate warning.


Cell formatting

Depending on the capability of the spreadsheet application, each cell (like its counterpart the "style" in a
word processor A word processor (WP) is a device or computer program that provides for input, editing, formatting, and output of text, often with some additional features. Early word processors were stand-alone devices dedicated to the function, but current ...
) can be separately formatted using the
attributes Attribute may refer to: * Attribute (philosophy), an extrinsic property of an object * Attribute (research), a characteristic of an object * Grammatical modifier, in natural languages * Attribute (computing), a specification that defines a prope ...
of either the content (point size, color, bold or italic) or the cell (border thickness, background shading, color). To aid the readability of a spreadsheet, cell formatting may be conditionally applied to data; for example, a negative number may be displayed in red. A cell's formatting does not typically affect its content and depending on how cells are referenced or copied to other worksheets or applications, the formatting may not be carried with the content.


Named cells

In most implementations, a cell, or group of cells in a column or row, can be "named" enabling the user to refer to those cells by a name rather than by a grid reference. Names must be unique within the spreadsheet, but when using multiple sheets in a spreadsheet file, an identically named cell range on each sheet can be used if it is distinguished by adding the sheet name. One reason for this usage is for creating or running macros that repeat a command across many sheets. Another reason is that formulas with named variables are readily checked against the algebra they are intended to implement (they resemble Fortran expressions). The use of named variables and named functions also makes the spreadsheet structure more transparent.


= Cell reference

= In place of a named cell, an alternative approach is to use a cell (or grid) reference. Most cell references indicate another cell in the same spreadsheet, but a cell reference can also refer to a cell in a different sheet within the same spreadsheet, or (depending on the implementation) to a cell in another spreadsheet entirely, or a value from a remote application. 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 alternative "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), causes 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 the same spreadsheet). Some spreadsheet implementations in Excel allow cell references to another spreadsheet (not the currently 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 Settings\Username\My spreadsheets\ ain sheetheet1!A1 In a spreadsheet, references to cells automatically update 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 values of the additional rows—which they often do not. A
circular reference A circular reference is a series of references where the last object references the first, resulting in a closed loop. In language A circular reference is not to be confused with the logical fallacy of a circular argument. Although a circula ...
occurs when the formula in one cell refers—directly, or indirectly through a chain of cell references—to another cell that refers back to the first cell. Many common errors cause circular references. However, some valid techniques use circular references. These techniques, after many spreadsheet recalculations, (usually) converge on the correct values for those cells.


= Cell ranges

= Likewise, instead of using a named range of cells, a range reference can be used. Reference to a range of cells is typical of the form (A1:A6), which specifies all the cells in the range A1 through to A6. A formula such as "=SUM(A1:A6)" would add all the cells specified and put the result in the cell containing the formula itself.


Sheets

In the earliest spreadsheets, cells were a simple two-dimensional grid. Over time, the model has expanded to include a third dimension, and in some cases a series of named grids, called sheets. The most advanced examples allow inversion and rotation operations which can slice and project the data set in various ways.


Formulas

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: where the
expression Expression may refer to: Linguistics * Expression (linguistics), a word, phrase, or sentence * Fixed expression, a form of words with a specific meaning * Idiom, a type of fixed expression * Metaphorical expression, a particular word, phrase, o ...
consists of: *
values In ethics and social sciences, value denotes the degree of importance of something or action, with the aim of determining which actions are best to do or what way is best to live (normative ethics in ethics), or to describe the significance of di ...
, such as 2, 9.14 or 6.67E-11; *Reference (computer science), references to other cells, such as, e.g., A1 for a single cell or B1:B3 for a range; *Operator (programming), arithmetic operators, such as +, -, *, /, and others; *relational operators, such as >=, <, and others; and, *Function (programming), 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– 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 depend 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)) Further examples: :=IF(AND(A1<>"",B1<>""),A1/B1,"") means that if both cells A1 and B1 are not <> empty "", then divide A1 by B1 and display, other do not display anything. :=IF(AND(A1<>"",B1<>""),IF(B1<>0,A1/B1,"Division by zero"),"") means that if cells A1 and B1 are not empty, and B1 is not zero, then divide A1 by B1, if B1 is zero, then display "Division by zero", and do not display anything if either A1 and B1 are empty. :=IF(OR(A1<>"",B1<>""),"Either A1 or B1 show text","") means to display the text if either cells A1 or B1 are not empty. The best way to build up conditional statements is step by step composing followed by trial and error testing and refining code. A spreadsheet does not 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 In computing, a database is an organized collection of data stored and accessed electronically. Small databases can be stored on a file system, while large databases are hosted on computer clusters or cloud storage. The design of databases s ...
) 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.


Functions

Spreadsheets usually contain several supplied Function (mathematics), functions, such as arithmetic operations (for example, summations, averages, and so forth), trigonometric functions, statistical functions, and so forth. In addition there is often a provision for ''user-defined functions''. In Microsoft Excel, these functions are defined using Visual Basic for Applications in the supplied Visual Basic editor, and such functions are automatically accessible on the worksheet. Also, programs can be written that pull information from the worksheet, perform some calculations, and report the results back to the worksheet. In the figure, the name ''sq'' is user-assigned, and the function ''sq'' is introduced using the Visual Basic for Applications, ''Visual Basic'' editor supplied with Excel. ''Name Manager'' displays the spreadsheet definitions of named variables ''x'' & ''y''.


Subroutines

Functions themselves cannot write into the worksheet but simply return their evaluation. However, in Microsoft Excel, subroutines can write values or text found within the subroutine directly to the spreadsheet. The figure shows the Visual Basic code for a subroutine that reads each member of the named column variable ''x'', calculates its square, and writes this value into the corresponding element of named column variable ''y''. The ''y'' column contains no formula because its values are calculated in the subroutine, not on the spreadsheet, and simply are written in.


Remote spreadsheet

Whenever a reference is made to a cell or group of cells that are not located within the current physical spreadsheet file, it is considered as accessing a "remote" spreadsheet. The contents of the referenced cell may be accessed either on the first reference with a manual update or more recently in the case of web-based spreadsheets, as a near real-time value with a specified automatic refresh interval.


Charts

Many spreadsheet applications permit charts and Graph (data structure), graphs (e.g., histograms, pie charts) to be generated from specified groups of cells that 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. To create an Excel histogram, a formula based on the REPT function can be used.


Multi-dimensional spreadsheets

In the late 1980s and early 1990s, first
Javelin Software Javelin Software Corporation (1984–1988) was a company in Cambridge, Massachusetts, USA, which developed an innovative modeling and data analysis product, also called ''Javelin'' (versions 1.0 in 1984 to 1.1), and later ''Javelin Plus'' (vers ...
and
Lotus Improv Lotus Improv is a discontinued spreadsheet program from Lotus Development released in 1991 for the NeXTSTEP platform and then for Windows 3.1 in 1993. Development was put on hiatus in 1994 after slow sales on the Windows platform, and officially ...
appeared. Unlike models in a conventional spreadsheet, they utilized models built on objects called variables, not on data in cells of a report. These multi-dimensional spreadsheets enabled viewing data and
algorithm In mathematics and computer science, an algorithm () is a finite sequence of rigorous instructions, typically used to solve a class of specific problems or to perform a computation. Algorithms are used as specifications for performing ...
s in various self-documenting ways, including simultaneous multiple synchronized views. For example, users of Javelin could move through the connections between variables on a diagram while seeing the logical roots and branches of each variable. This is an example of what is perhaps its primary contribution of the earlier Javelin—the concept of traceability of a user's logic or model structure through its twelve views. A complex model can be dissected and understood by others who had no role in its creation. In these programs, a time series, or any variable, was an object in itself, not a collection of cells that happen to appear in a row or column. Variables could have many attributes, including complete awareness of their connections to all other variables, data references, and text and image notes. Calculations were performed on these objects, as opposed to a range of cells, so adding two-time series automatically aligns them in calendar time, or in a user-defined time frame. Data were independent of worksheets—variables, and therefore data, could not be destroyed by deleting a row, column, or entire worksheet. For instance, January's costs are subtracted from January's revenues, regardless of where or whether either appears in a worksheet. This permits actions later used in pivot tables, except that flexible manipulation of report tables, was but one of many capabilities supported by variables. Moreover, if costs were entered by week and revenues by month, the program could allocate or interpolate as appropriate. This object design enabled variables and whole models to reference each other with user-defined variable names and to perform multidimensional analysis and massive, but easily editable consolidations. Trapeze, a spreadsheet on the Mac, went further and explicitly supported not just table columns, but also matrix operators.


Logical spreadsheets

Spreadsheets that have a formula language based upon logical expressions, rather than arithmetic expressions are known as logical spreadsheets. Such spreadsheets can be used to reason deductive reasoning, deductively about their cell values.


Programming issues

Just as the early programming languages were designed to generate spreadsheet printouts, programming techniques themselves have evolved to process tables (also known as spreadsheets or matrix (mathematics), matrices) of data more efficiently in the computer itself.


End-user development

Spreadsheets are a popular end-user development tool. EUD denotes activities or techniques in which people who are not professional developers create automated behavior and complex data objects without significant knowledge of a programming language. Many people find it easier to perform calculations in spreadsheets than by writing the equivalent sequential program. This is due to several traits of spreadsheets. *They use Space, spatial relationships to define program relationships. Humans have highly developed intuition (knowledge), 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 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. Though this user-friendliness is benefit of spreadsheet development, it often comes with increased risk of errors. *Modern spreadsheets allow for secondary notation. The program can be annotated with colors, typefaces, lines, etc. to provide visual cues about the meaning of elements in the program. *Extensions that allow users to create new functions can provide the capabilities of a functional language. *Extensions that allow users to build and apply models from the domain of machine learning. *Spreadsheets are versatile. With their boolean logic and graphics capabilities, even electronic circuit design is possible. * Spreadsheets can store relational model, relational data and spreadsheet formulas can express all queries of SQL. There exists a query translator, which automatically generates the spreadsheet implementation from the SQL code.


Spreadsheet programs

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 theory, graph, where the node (graph theory), 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 depend on change. The earliest spreadsheets used simple tactics like evaluating cells in a particular order, but modern spreadsheets calculate following 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 cell formulas are not generally invertible, though, this technique is of somewhat limited value. Many of the concepts common to sequential programming models have analogs in the spreadsheet world. For example, the sequential model of the For loop, indexed loop is usually represented as a table of cells, with similar formulas (normally differing only in which cells they reference). Spreadsheets have evolved to use Scripting language, scripting programming languages like Visual Basic for Applications, VBA as a tool for extensibility beyond what the spreadsheet language makes easy.


Shortcomings

While spreadsheets represented a major step forward in quantitative modeling, they have deficiencies. Their shortcomings include the perceived unfriendliness of alpha-numeric cell addresses. * Research by ClusterSeven has shown huge discrepancies in the way financial institutions and corporate entities understand, manage and police their often vast estates of spreadsheets and unstructured financial data (including comma-separated values (CSV) files and Microsoft Access databases). One study in early 2011 of nearly 1,500 people in the UK found that 57% of spreadsheet users have never received formal training on the spreadsheet package they use. 72% said that no internal department checks their spreadsheets for accuracy. Only 13% said that Internal Audit reviews their spreadsheets, while a mere 1% receive checks from their risk department. * Spreadsheets can have reliability problems. Research studies estimate that around 1% of all formulas in operational spreadsheets are in error. ::Despite the high error risks often associated with spreadsheet authorship and use, specific steps can be taken to significantly enhance control and reliability by structurally reducing the likelihood of error occurrence at their source.unabridged version
* The practical expressiveness of spreadsheets can be limited unless their modern features are used. Several factors contribute to this limitation. Implementing a complex model on a cell-at-a-time basis requires tedious attention to detail. Authors have difficulty remembering the meanings of hundreds or thousands of cell addresses that appear in formulas. ::These drawbacks are mitigated by the use of named variables for cell designations, and employing variables in formulas rather than cell locations and cell-by-cell manipulations. Graphs can be used to show instantly how results are changed by changes in parameter values. The spreadsheet can be made invisible except for a transparent user interface that requests pertinent input from the user, displays results requested by the user, creates reports, and has built-in error traps to prompt correct input. * Similarly, 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. That is another reason to use named variables and formulas employing named variables. ::Specifically, spreadsheets typically contain many copies of the same formula. When the formula is modified, the user has to change every cell containing that formula. In contrast, most computer languages allow a formula to appear only once in the code and achieve repetition using loops: making them much easier to implement and audit. * The alteration of a dimension demands major surgery. When rows (or columns) are added to or deleted from a table, one has to adjust the size of many downstream tables that depend on the table being changed. In the process, it is often necessary to move other cells around to make room for the new columns or rows and to adjust graph data sources. In large spreadsheets, this can be extremely time-consuming. * Adding or removing a dimension is so difficult, one generally has to start over. The spreadsheet as a paradigm forces one to decide on dimensionality right of the beginning of one's spreadsheet creation, even though it is often most natural to make these choices after one's spreadsheet model has matured. The desire to add and remove dimensions also arises in parametric and sensitivity analyses. * Collaboration in authoring spreadsheet formulas can be difficult when such collaboration occurs at the level of cells and cell addresses. 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
Microsoft Excel Microsoft Excel is a spreadsheet developed by Microsoft for Windows, macOS, Android and iOS. It features calculation or computation capabilities, graphing tools, pivot tables, and a macro programming language called Visual Basic for App ...
(versions prior to 2007) and OpenOffice.org Calc (versions prior to 2008), have a capacity limit of 65,536 rows by 256 columns (216 and 28 respectively). This can present a problem for people using very large datasets, and may result in data loss. In spite of the time passed, a recent example is the loss of COVID-19 in the United Kingdom, COVID-19 positives in the British statistics for September and October 2020. * 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 to the inability to track, isolate and test changes made to a document. * Lack of Computer security, security. Spreadsheets lack controls on who can see and modify particular data. 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 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 typically makes one calculation that it applies to all 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 that may exist in 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. A good example of this is that 55% of Capital market professionals "don't know" how their spreadsheets are audited; only 6% invest in a third-party solution


Spreadsheet risk

Spreadsheet risk is the risk associated with deriving a materially incorrect value from a spreadsheet application that will be utilized in making a related (usually numerically based) decision. Examples include the valuation of an asset, the determination of Financial accountancy, financial accounts, the calculation of medicinal doses, or the size of a load-bearing beam for structural engineering. The risk may arise from inputting erroneous or fraudulent data values, from mistakes (or incorrect changes) within the logic of the spreadsheet or the omission of relevant updates (e.g., out of date exchange rates). Some single-instance errors have exceeded US$1 billion. Because spreadsheet risk is principally linked to the actions (or inaction) of individuals it is defined as a sub-category of operational risk. Despite this, research carried out by ClusterSeven revealed that around half (48%) of c-level executives and senior managers at firms reporting annual revenues over £50m said there were either no usage controls at all or poorly applied manual processes over the use of spreadsheets at the firms. In 2013 Thomas Herndon, a graduate student of economics at the University of Massachusetts Amherst found major coding flaws in the spreadsheet used by the economists Carmen Reinhart and Kenneth Rogoff in ''Growth in a Time of Debt'', a very influential 2010 journal article. The Reinhart and Rogoff article was widely used as justification to drive 2010–2013 European austerity programs.


See also

*Attribute-value system *Comparison of spreadsheet software *Cut, copy and paste#Additional differences between moving and copying, Moving and copying in spreadsheets *List of spreadsheet software *Model audit


Notes


References


External links


comp.apps.spreadsheets FAQ
by Russell Schulz

by Jocelyn Paine *

by Richard Mattessich
CICS history and introduction of IBM 3270
by Bob Yelavich
Autoplan & Autotab article
by Creative Karma
Spreadsheets in Science
{{Spreadsheets American inventions Spreadsheet software, *