A spreadsheet is a computer application that simulates a paper accounting
worksheetA worksheet is a sheet of paper, or on a computer, on which problems are worked out or solved and answers recorded.-Education:Students in a school may have 'fill-in-the-blank' sheets of questions, diagrams, or maps to help them with their exercises...
. It displays multiple cells usually in a two-dimensional matrix or grid consisting of rows and columns. Each cell contains
alphanumericAlphanumeric is a combination of alphabetic and numeric characters, and is used to describe the collection of Latin letters and Arabic digits or a text constructed from this collection. There are either 36 or 62 alphanumeric characters. The alphanumeric character set consists of the numbers 0 to...
text, numeric values or formulas. A
formulaIn mathematics, a formula is an entity constructed using the symbols and formation rules of a given logical language....
defines how the content of that cell is to be calculated from the contents of any other cell (or combination of cells) each time any cell is updated. Spreadsheets are frequently used for
financialFINANCIAL is the weekly English-language newspaper with offices in Tbilisi, Georgia and Kiev, Ukraine. Published by Intelligence Group LLC, FINANCIAL is focused on opinion leaders and top business decision-makers; It's about world’s largest companies, investing, careers, and small business. It is...
information because of their ability to re-calculate the entire sheet automatically after a change to a single cell is made. A pseudo third dimension to the matrix is sometimes applied as another layer, or layers/sheets, of two-dimensional data.
VisicalcVisiCalc was the first spreadsheet program available for personal computers. It is often considered the application that turned the microcomputer from a hobby for computer enthusiasts into a serious business tool...
is the first electronic spreadsheet on a microcomputer, and it helped turn the Apple II computer into a success and greatly assisted in their widespread application.
Lotus 1-2-3Lotus 1-2-3 is a spreadsheet program from Lotus Software . It was the IBM PC's first "killer application"; its huge popularity in the mid-1980s contributed significantly to the success of the IBM PC in the corporate environment.-Beginnings:...
was the leading spreadsheet when
DOSDOS, short for "Disk Operating System", is an acronym for several closely related operating systems that dominated the IBM PC compatible market between 1981 and 1995, or until about 2000 if one includes the partially DOS-based Microsoft Windows versions 95, 98, and Millennium Edition.Related...
was the dominant operating system.
ExcelMicrosoft Excel is a proprietary commercial spreadsheet application written and distributed by Microsoft for Microsoft Windows and Mac OS X. It features calculation, graphing tools, pivot tables, and a macro programming language called Visual Basic for Applications...
now has the largest market share on the Windows and Macintosh platforms.
Paper spreadsheets
The word "spreadsheet" came from "spread" in its sense of a newspaper or magazine item (text and/or graphics) that covers two facing pages, extending across the center fold and treating the two pages as one large one. The compound word "spread-sheet" came to mean the format used to present book-keeping ledgers—with columns for categories of expenditures across the top, invoices listed down the left margin, and the amount of each payment in the cell where its row and column intersect—which were, traditionally, a "spread" across facing pages of a bound ledger (book for keeping accounting records) or on oversized sheets of paper ruled into rows and columns in that format and approximately twice as wide as ordinary paper....
Batch spreadsheet report generator
A
batchBatch processing is execution of a series of programs on a computer without manual intervention.Batch jobs are set up so they can be run to completion without manual intervention, so all input data is preselected through scripts or command-line parameters...
'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. 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 computerMainframes are powerful computers used primarily by corporate and governmental organizations for critical applications, bulk data processing such as census, industry and consumer statistics, enterprise resource planning, and financial transaction processing.The term originally referred to the...
s 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 1130The IBM 1130 Computing System was introduced in 1965. It was IBM's least-expensive computer to date, and was aimed at price-sensitive, computing-intensive technical markets like education and engineering. It succeeded the IBM 1620 in that market segment. The IBM 1800 was a process control variant...
and in 1963 was ported to an
IBM 7040The IBM 7040 was a historic but short-lived model of transistor computer built in the 1960s.It was announced by IBM in December 1961, but did not ship until April, 1963. A later member of the IBM 700/7000 series of scientific computers, it was a scaled down version of the IBM 7090. It was not fully...
by R. Brian Walsh at
Marquette UniversityMarquette University is a private, coeducational, Jesuit, Roman Catholic university located in Milwaukee, Wisconsin. Founded by the Society of Jesus in 1881, the school is one of 28 member institutions of the Association of Jesuit Colleges and Universities...
,
WisconsinWisconsin is a U.S. state located in the north-central United States and is part of the Midwest. It is bordered by Minnesota to the west, Iowa to the southwest, Illinois to the south, Lake Michigan to the east, Michigan to the northeast, and Lake Superior to the north. Wisconsin's capital is...
. This program was written in
FortranFortran is a general-purpose, procedural, imperative programming language that is especially suited to numeric computation and scientific computing...
. Primitive timesharing was available on those machines. In 1968 BCL was ported by Walsh to the IBM 360/67 timesharing machine at
Washington State UniversityWashington State University is a public research university based in Pullman, Washington, in the Palouse region of the Pacific Northwest. Founded in 1890, WSU is the state's original and largest land-grant university...
. It was used to assist in the teaching of
finance"Finance" is often defined simply as the management of money or “funds” management Modern finance, however, is a family of business activity that includes the origination, marketing, and management of cash and money surrogates through a variety of capital accounts, instruments, and markets created...
to business students. Students were able to take information prepared by the
professorA professor is a scholarly teacher; the precise meaning of the term varies by country. Literally, professor derives from Latin as a "person who professes" being usually an expert in arts or sciences; a teacher of high rank...
and manipulate it to represent it and show ratios etc. In 1964, a book entitled Business Computer Language written by Kimball, Stoffells and Walsh and both the book and program were copyrighted in 1966 and years later that copyright was renewed
In the late 60's Xerox used BCL to develop a more sophisticated version for their timesharing system.
LANPAR spreadsheet compiler
Key invention in the development of electronic spreadsheets was made by Rene K. Pardo and Remy Landau, who filed in 1971 on spreadsheet automatic natural order recalculation
algorithmIn mathematics and computer science, an algorithm is an effective method expressed as a finite list of well-defined instructions for calculating a function. Algorithms are used for calculation, data processing, and automated reasoning...
in 1970. While the patent was initially rejected by the patent office as being a purely mathematical invention, following 12 years of appeals, Pardo and Landau won a landmark court case at the CCPA (Predecessor Court of the Federal Circuit) overturning the Patent Office in 1983 - establishing that "something does not cease to become patentable merely because the point of novelty is in an algorithm." However, in 1995 the
United States Court of Appeals for the Federal Circuit-Vacancies and pending nominations:-List of former judges:-Chief judges:Notwithstanding the foregoing, when the court was initially created, Congress had to resolve which chief judge of the predecessor courts would become the first chief judge...
ruled the patent unenforceable.
The actual software was called LANPAR - LANguage for Programming Arrays at Random. This was conceived and entirely developed in the summer of 1969 following Pardo and Landau's recent graduation from Harvard University. Co-inventor Rene Pardo recalls that he felt that one manager at Bell Canada should not have to depend on programmers to program and modify budgeting forms, and he thought of letting users type out forms in any order and having computer calculating results in the right order. The software was developed in 1969.
LANPAR was used by Bell Canada, AT&T and the 18 operating telcos nationwide for their local and national budgeting operations. LANPAR was also used by General Motors. Its uniqueness was the incorporation of natural order recalculation, as opposed to left-to-right, top to bottom sequence for calculating the results in each cell that was used by Visicalc, Supercalc and the first version of Multiplan. Without natural order recalculation the users had to manually recalculate the spreadsheet as many times as necessary until the values in all the cells had stopped changing.
The LANPAR system was implemented on GE400 and Honeywell 6000 online timesharing systems enabling users to program remotely via computer terminals and modems. Data could be entered dynamically either by paper tape, specific file access, on line, or even external data bases. Sophisticated mathematical expressions including logical comparisons and "if/then" statements could be used in any cell, and cells could be presented in any order.
Autoplan/Autotab spreadsheet programming language
In 1968, three former employees from the
General ElectricGeneral Electric Company , or GE, is an American multinational conglomerate corporation incorporated in Schenectady, New York and headquartered in Fairfield, Connecticut, United States...
computer company headquartered in
Phoenix, ArizonaPhoenix is the capital, and largest city, of the U.S. state of Arizona, as well as the sixth most populated city in the United States. Phoenix is home to 1,445,632 people according to the official 2010 U.S. Census Bureau data...
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 CorporationCapex Corporation was a software house based in Phoenix, Arizona founded by three former employees of General Electric. It was subsequently acquired by Computer Associates.-Products:* Autotab - an early batch spreadsheet program...
. "AutoPlan" ran on GE’s
Time-sharingTime-sharing is the sharing of a computing resource among many users by means of multiprogramming and multi-tasking. Its introduction in the 1960s, and emergence as the prominent model of computing in the 1970s, represents a major technological shift in the history of computing.By allowing a large...
service; afterward, a version that ran on
IBM mainframeIBM mainframes are large computer systems produced by IBM from 1952 to the present. During the 1960s and 1970s, the term mainframe computer was almost synonymous with IBM products due to their marketshare...
s was introduced under the name "AutoTab". (
National CSSNational CSS, Inc. 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 development facilities were located at various sites...
offered a similar product, CSSTAB, which had a moderate timesharing user base by the early 70s. A major application was opinion research tabulation.) AutoPlan/AutoTab was not a
WYSIWYGWYSIWYG is an acronym for What You See Is What You Get. The term is used in computing to describe a system in which content displayed onscreen during editing appears in a form closely corresponding to its appearance when printed or displayed as a finished product...
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.
APLDOT modeling language
An example of an early "industrial weight" spreadsheet was APLDOT, developed in 1976 at the
United States Railway AssociationThe United States Railway Association 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 railroads...
on an IBM 360/91, running at The Johns Hopkins University Applied Physics Laboratory in Laurel, MD. The application was used successfully for many years in developing such applications as financial and costing models for the US Congress and for Conrail. APLDOT was dubbed a "spreadsheet" because financial analysts and strategic planners used it to solve the same problems they addressed with paper spreadsheet pads.
VisiCalc
Because of
Dan Bricklin and
Bob FrankstonRobert M. Frankston is the co-creator with Dan Bricklin of the VisiCalc spreadsheet program and the co-founder of Software Arts, the company that developed it....
's implementation of
VisiCalcVisiCalc was the first spreadsheet program available for personal computers. It is often considered the application that turned the microcomputer from a hobby for computer enthusiasts into a serious business tool...
on the
Apple IIThe Apple II is an 8-bit home computer, one of the first highly successful mass-produced microcomputer products, designed primarily by Steve Wozniak, manufactured by Apple Computer and introduced in 1977...
in 1979 and the
IBM PCThe IBM Personal Computer, commonly known as the IBM PC, is the original version and progenitor of the IBM PC compatible hardware platform. It is IBM model number 5150, and was introduced on August 12, 1981...
in 1981, the spreadsheet concept became widely known in the late 1970s and early 1980s. VisiCalc was the first spreadsheet that combined all essential features of modern spreadsheet applications, such as
WYSIWYGWYSIWYG is an acronym for What You See Is What You Get. The term is used in computing to describe a system in which content displayed onscreen during editing appears in a form closely corresponding to its appearance when printed or displayed as a finished product...
interactive user interface, automatic recalculation, status and formula lines, range copying with relative and absolute references, formula building by selecting referenced cells.
PC World magazinePC World is a global computer magazine published monthly by IDG. It offers advice on various aspects of PCs and related items, the Internet, and other personal-technology products and services...
has called VisiCalc the first electronic spreadsheet.
Bricklin has spoken of watching his university professor create a table of calculation results on a blackboard. When the professor found an error, he had to tediously erase and rewrite a number of sequential entries in the table, triggering Bricklin to think that he could replicate the process on a computer, using the blackboard as the model to view results of underlying formulas. His idea became
VisiCalcVisiCalc was the first spreadsheet program available for personal computers. It is often considered the application that turned the microcomputer from a hobby for computer enthusiasts into a serious business tool...
, the first
applicationApplication software, also known as an application or an "app", is computer software designed to help the user to perform specific tasks. Examples include enterprise software, accounting software, office suites, graphics software and media players. Many application programs deal principally with...
that turned the
personal computerA personal computer is any general-purpose computer whose size, capabilities, and original sales price make it useful for individuals, and which is intended to be operated directly by an end-user with no intervening computer operator...
from a hobby for computer enthusiasts into a business tool.
VisiCalc went on to become the first "killer app", an application that was so compelling, people would buy a particular computer just to use it. VisiCalc was in no small part responsible for the
Apple IIThe Apple II is an 8-bit home computer, one of the first highly successful mass-produced microcomputer products, designed primarily by Steve Wozniak, manufactured by Apple Computer and introduced in 1977...
's success. The program was later ported to a number of other early computers, notably
CP/MCP/M was a mass-market operating system created for Intel 8080/85 based microcomputers by Gary Kildall of Digital Research, Inc...
machines, the
Atari 8-bit familyThe Atari 8-bit family is a series of 8-bit home computers manufactured from 1979 to 1992. All are based on the MOS Technology 6502 CPU and were the first home computers designed with custom coprocessor chips...
and various
CommodoreCommodore is the commonly used name for Commodore Business Machines , the U.S.-based home computer manufacturer and electronics manufacturer headquartered in West Chester, Pennsylvania, which also housed Commodore's corporate parent company, Commodore International Limited...
platforms. Nevertheless, VisiCalc remains best known as "an Apple II program".
Lotus 1-2-3 and other MS-DOS spreadsheets
The acceptance of the
IBM PCThe IBM Personal Computer, commonly known as the IBM PC, is the original version and progenitor of the IBM PC compatible hardware platform. It is IBM model number 5150, and was introduced on August 12, 1981...
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-3Lotus 1-2-3 is a spreadsheet program from Lotus Software . It was the IBM PC's first "killer application"; its huge popularity in the mid-1980s contributed significantly to the success of the IBM PC in the corporate environment.-Beginnings:...
in November, 1982, and release for sale in January, 1983. Since it was written especially for the IBM PC, it had good performance and became the killer app for this PC. Lotus 1-2-3 drove sales of the PC due to the improvements in speed and graphics compared to VisiCalc on the Apple II.
Lotus 1-2-3, along with its competitor
BorlandBorland Software Corporation is a software company first headquartered in Scotts Valley, California, Cupertino, California and finally Austin, Texas. It is now a Micro Focus subsidiary. It was founded in 1983 by Niels Jensen, Ole Henriksen, Mogens Glad and Philippe Kahn.-The 1980s:...
QuattroQuattro Pro is a spreadsheet program developed by Borland and now sold by Corel, most often as part of Corel's WordPerfect Office.Historically, Quattro Pro used keyboard commands similar to Lotus 1-2-3. It is commonly said to have been the first program to use tabbed sheets. Actually, Boeing Calc...
, soon displaced VisiCalc. Lotus 1-2-3 was released on January 26, 1983, started outselling then-most-popular
VisiCalcVisiCalc was the first spreadsheet program available for personal computers. It is often considered the application that turned the microcomputer from a hobby for computer enthusiasts into a serious business tool...
the very same year, and for a number of years was the leading spreadsheet for
DOSDOS, short for "Disk Operating System", is an acronym for several closely related operating systems that dominated the IBM PC compatible market between 1981 and 1995, or until about 2000 if one includes the partially DOS-based Microsoft Windows versions 95, 98, and Millennium Edition.Related...
.
Microsoft Excel
MicrosoftMicrosoft Corporation is an American public multinational corporation headquartered in Redmond, Washington, USA that develops, manufactures, licenses, and supports a wide range of products and services predominantly related to computing through its various product divisions...
had been developing
ExcelMicrosoft Excel is a proprietary commercial spreadsheet application written and distributed by Microsoft for Microsoft Windows and Mac OS X. It features calculation, graphing tools, pivot tables, and a macro programming language called Visual Basic for Applications...
on the Macintosh platform for several years at this point, where it had developed into a fairly powerful system. A port of Excel to Windows 2.0 resulted in a fully functional Windows spreadsheet. The more robust 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 started compiling their
OfficeMicrosoft Office is a non-free commercial office suite of inter-related desktop applications, servers and services for the Microsoft Windows and Mac OS X operating systems, introduced by Microsoft in August 1, 1989. Initially a marketing term for a bundled set of applications, the first version of...
suite. Starting in the mid 1990s continuing through the present, Microsoft Excel has dominated the commercial electronic spreadsheet market.
Apple Numbers
NumbersNumbers is a spreadsheet application developed by Apple Inc. as part of the iWork productivity suite alongside Keynote and Pages. Numbers 1.0 was announced on August 7, 2007 and thus it is the newest application in the iWork Suite. Numbers runs on Mac OS X v10.4 "Tiger" or newer...
is Apple Inc.'s spreadsheet software, part of
iWorkiWork is an office suite of desktop applications created by Apple for the Mac OS X and iOS operating systems. The first version of iWork, iWork '05, was released in 2005. The suite originally bundled Keynote, a presentation program which had previously been sold as a standalone application, and...
. It focuses on usability and the elegance of chart presentation. Numbers completed Apple's productivity suite, making it a viable competitor to
Microsoft OfficeMicrosoft Office is a non-free commercial office suite of inter-related desktop applications, servers and services for the Microsoft Windows and Mac OS X operating systems, introduced by Microsoft in August 1, 1989. Initially a marketing term for a bundled set of applications, the first version of...
. It lacks features such as
pivot tableIn data processing, a pivot table is a data summarization tool found in data visualization programs such as spreadsheets or business intelligence software. Among other functions, pivot-table tools can automatically sort, count, total or give the average of the data stored in one table or spreadsheet...
, although providing Table Categories as a simpler alternative.
OpenOffice.org Calc
OpenOffice.org CalcOpenOffice.org Calc is the spreadsheet component of the OpenOffice.org software package.Calc is similar to Microsoft Excel, with a roughly equivalent range of features. Calc is capable of opening and saving most spreadsheets in Microsoft Excel file format...
is a free, open-source program modelled after
Microsoft ExcelMicrosoft Excel is a proprietary commercial spreadsheet application written and distributed by Microsoft for Microsoft Windows and Mac OS X. It features calculation, graphing tools, pivot tables, and a macro programming language called Visual Basic for Applications...
. Calc can both open and save in the Excel (XLS) file format. Calc can be acquired as both an installation file and a portable program, capable of being run from a device such as a USB memory drive. It can be downloaded from the OpenOffice.org website.
Gnumeric
GnumericGnumeric is a spreadsheet program that is part of the GNOME Free Software Desktop Project. Gnumeric version 1.0 was released December 31, 2001. Gnumeric is distributed as free software under the GNU GPL license; it is intended to replace proprietary and other spreadsheet programs such as Microsoft...
is a
freeFree software, software libre or libre software is software that can be used, studied, and modified without restriction, and which can be copied and redistributed in modified or unmodified form either without restriction, or with restrictions that only ensure that further recipients can also do...
cross-platformIn computing, cross-platform, or multi-platform, is an attribute conferred to computer software or computing methods and concepts that are implemented and inter-operate on multiple computer platforms...
spreadsheet program that is part of the
GNOMEGNOME is a desktop environment and graphical user interface that runs on top of a computer operating system. It is composed entirely of free and open source software...
Free Software Desktop Project. It is intended to be a free replacement for
proprietaryProprietary software is computer software licensed under exclusive legal right of the copyright holder. The licensee is given the right to use the software under certain conditions, while restricted from other uses, such as modification, further distribution, or reverse engineering.Complementary...
spreadsheet programs such as
Microsoft ExcelMicrosoft Excel is a proprietary commercial spreadsheet application written and distributed by Microsoft for Microsoft Windows and Mac OS X. It features calculation, graphing tools, pivot tables, and a macro programming language called Visual Basic for Applications...
, which it broadly and openly emulates. Gnumeric was created and developed by
Miguel de IcazaMiguel de Icaza is a Mexican free software programmer, best known for starting the GNOME and Mono projects.-Early years:Miguel de Icaza was born in Mexico City and studied at the Universidad Nacional Autonoma de México but never received a degree. He came from a family of scientists in which his...
, and the current maintainer is Jody Goldberg.
Initially it was planned to add a Visual Basic-compatible scripting language to Gnumeric through Gnome Basic project, but as Gnome Basic was closed in favor of Mono, any plans to implement Basic-like functionality in Gnumeric were cancelled.
Gnumeric has the ability to import and export data in several file formats, including
CSVA comma-separated values file stores tabular data in plain-text form. As a result, such a file is easily human-readable ....
,
Microsoft ExcelMicrosoft Excel is a proprietary commercial spreadsheet application written and distributed by Microsoft for Microsoft Windows and Mac OS X. It features calculation, graphing tools, pivot tables, and a macro programming language called Visual Basic for Applications...
,
HTMLHyperText Markup Language is the predominant markup language for web pages. HTML elements are the basic building-blocks of webpages....
,
LaTeXLaTeX is a document markup language and document preparation system for the TeX typesetting program. Within the typesetting system, its name is styled as . The term LaTeX refers only to the language in which documents are written, not to the editor used to write those documents. In order to...
,
Lotus 1-2-3Lotus 1-2-3 is a spreadsheet program from Lotus Software . It was the IBM PC's first "killer application"; its huge popularity in the mid-1980s contributed significantly to the success of the IBM PC in the corporate environment.-Beginnings:...
,
OpenDocumentThe Open Document Format for Office Applications is an XML-based file format for representing electronic documents such as spreadsheets, charts, presentations and word processing documents....
and
Quattro ProQuattro Pro is a spreadsheet program developed by Borland and now sold by Corel, most often as part of Corel's WordPerfect Office.Historically, Quattro Pro used keyboard commands similar to Lotus 1-2-3. It is commonly said to have been the first program to use tabbed sheets. Actually, Boeing Calc...
; its native format is the Gnumeric file format (.gnm or .gnumeric), an
XMLExtensible Markup Language is a set of rules for encoding documents in machine-readable form. It is defined in the XML 1.0 Specification produced by the W3C, and several other related specifications, all gratis open standards....
file compressed with
gzipGzip is any of several software applications used for file compression and decompression. The term usually refers to the GNU Project's implementation, "gzip" standing for GNU zip. It is based on the DEFLATE algorithm, which is a combination of Lempel-Ziv and Huffman coding...
. It includes all of the
spreadsheet functionIn computer science, a subroutine is a portion of code within a larger program that performs a specific task and is relatively independent of the remaining code....
s of the
North AmericaNorth America is a continent wholly within the Northern Hemisphere and almost wholly within the Western Hemisphere. It is also considered a northern subcontinent of the Americas...
n edition of
Microsoft ExcelMicrosoft Excel is a proprietary commercial spreadsheet application written and distributed by Microsoft for Microsoft Windows and Mac OS X. It features calculation, graphing tools, pivot tables, and a macro programming language called Visual Basic for Applications...
and many functions unique to Gnumeric.
Pivot tableIn data processing, a pivot table is a data summarization tool found in data visualization programs such as spreadsheets or business intelligence software. Among other functions, pivot-table tools can automatically sort, count, total or give the average of the data stored in one table or spreadsheet...
s and conditional formatting are not yet supported but are planned for future versions. Gnumeric's accuracy has helped it to establish a niche among people using it for statistical analysis and other scientific tasks. For improving the accuracy of Gnumeric, the developers are cooperating with the R Project.
Web based spreadsheets
With the advent of advanced
webThe World Wide Web is a system of interlinked hypertext documents accessed via the Internet...
technologies such as
AjaxAjax is a group of interrelated web development methods used on the client-side to create asynchronous web applications...
circa 2005, a new generation of
online spreadsheets has emerged. Equipped with a
rich Internet applicationA Rich Internet Application is a Web application that has many of the characteristics of desktop application software, typically delivered either by way of a site-specific browser, via a browser plug-in, independent sandboxes, extensive use of JavaScript, or virtual machines...
user experience, the best web based online spreadsheets have many of the features seen in desktop spreadsheet applications. Some of them such as
Office Web AppsMicrosoft Office Web Apps is a web-based version of the Microsoft Office productivity suite. It includes the web-based versions of Microsoft Word, Microsoft Excel, Microsoft PowerPoint, and Microsoft OneNote. The web applications allow users to access their documents directly from anywhere within a...
or
Google SpreadsheetsGoogle Docs is a free, Web-based office suite, and data storage service offered by Google. It allows users to create and edit documents online while collaborating in real-time with other users. Google Docs combines the features of Writely and Spreadsheets with a presentation program incorporating...
also have strong multi-user collaboration features and / or offer
real timeReal-time business intelligence is the process of delivering information about business operations as they occur.In this context, real-time means a range from milliseconds to a few seconds after the business event has occurred...
updates from remote sources such as stock prices and currency
exchange rateIn finance, an exchange rate between two currencies is the rate at which one currency will be exchanged for another. It is also regarded as the value of one country’s currency in terms of another currency...
s.
Other spreadsheets
- A list of current spreadsheet software
- IBM Lotus Symphony
IBM Lotus Symphony is a suite of applications for creating, editing, and sharing text, spreadsheet, presentations and other documents, and is currently distributed as freeware. First released in 2007, the suite has a name similar to the 1980s DOS suite Lotus Symphony, but the two programs are...
(2007)
- Corel
Corel Corporation from the abbreviation is a computer software company headquartered in Ottawa, Ontario, that specializes in graphics processing, similar to Adobe Systems...
Quattro ProQuattro Pro is a spreadsheet program developed by Borland and now sold by Corel, most often as part of Corel's WordPerfect Office.Historically, Quattro Pro used keyboard commands similar to Lotus 1-2-3. It is commonly said to have been the first program to use tabbed sheets. Actually, Boeing Calc...
(WordPerfect OfficeWordPerfect Office is an office suite developed by Corel Corporation. As of March 2010, the latest version is WordPerfect Office X5 , which is available in three editions: Home & Student, Standard and Professional....
)
- KSpread
Tables is a free software spreadsheet application that is part of Calligra Suite, an integrated graphic art and office suite by KDE.Among Tables's features are multiple sheets per document, assorted formatting possibilities, support for more than 300 built-in functions, templates, chart,...
- ZCubes
ZCubes is free web-based software that is intended for the creation of documents including web pages, web sites, spreadsheets, presentations, drawings, albums, and portals.-Overview:...
-Calci
- Resolver One
Resolver One is a Spreadsheet program created by Resolver Systems with the IronPython programming language. It is a desktop application, with a web server version, and intended as a platform for the rapid development of business applications. It runs on Microsoft Windows, and is free for use in...
- GNU Oleo
GNU Oleo is a 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....
- A traditional terminal mode spreadsheet for UNIX/UNIX-like systems
- sc - A terminal mode spreadsheet for UNIX/UNIX-like systems
- slsc
SLSC may refer to:* Surf Life Saving Club, volunteer institutions in Australia and New Zealand* Saint Louis Science Center, science museum in St. Louis, Missouri* South London Swimming Club, club in London, England...
- A terminal mode spreadsheet for UNIX/UNIX-like systems
- Discontinued spreadsheet software
- Advantage
Advantage may refer to:*In military terms, advantage is the superiority in elevation which one side enjoys over the opposing element...
- Lotus Improv
Lotus Improv was a spreadsheet program from Lotus Development that attempted to re-define the way a spreadsheet should work. Instead of treating the grid as the system for referencing data, Improv made all data exist in named ranges. Operations on the data then referred to these names, rather than...
- Javelin Software
Javelin Software Corporation was a company in Cambridge, Massachusetts which developed an innovative modeling and data analysis product, also called Javelin, and later Javelin Plus...
- Lotus Jazz
Lotus Jazz was an office suite for the Apple Macintosh, released in 1985, after the substantial success of Lotus 1-2-3 for the IBM-compatible PC...
for Macintosh
- MultiPlan
Multiplan was an early spreadsheet program developed by Microsoft. Known initially by the code name "EP" , it was introduced in 1982 as a competitor for VisiCalc....
- 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.Historically, Quattro Pro used keyboard commands similar to Lotus 1-2-3. It is commonly said to have been the first program to use tabbed sheets. Actually, Boeing Calc...
- SuperCalc
SuperCalc was a spreadsheet application published by Sorcim in 1980, and originally bundled as part of the CP/M software package included with the Osborne 1 portable computer....
- Lotus Symphony (1984)
- Wingz
Wingz was a spreadsheet program sold by Informix in the late 1980s and early 1990s. Originally developed for the Macintosh, it was later distributed on Microsoft Windows, OS/2, NextStep and several other commercial flavors of Unix...
for Macintosh
- Target Planner Calc for CP/M and TRS-DOS
Other products
A number of companies have attempted to break into the spreadsheet market with programs based on very different paradigms. Lotus introduced what is likely the most successful example,
Lotus ImprovLotus Improv was a spreadsheet program from Lotus Development that attempted to re-define the way a spreadsheet should work. Instead of treating the grid as the system for referencing data, Improv made all data exist in named ranges. Operations on the data then referred to these names, rather than...
, which saw some commercial success, notably in the financial world where its powerful
data miningData mining , a relatively young and interdisciplinary field of computer science is the process of discovering new patterns from large data sets involving methods at the intersection of artificial intelligence, machine learning, statistics and database systems...
capabilities remain well respected to this day.
Spreadsheet 2000Spreadsheet 2000 was a spreadsheet program for Apple Macintosh computers, published by Casady & Greene, a distributor of many "smaller" Mac releases. It appears to have seen little in terms of sales, and was withdrawn from the market after only a short time...
attempted to dramatically simplify formula construction, but was generally not successful.
Concepts
The main concepts are those of a grid of
cellsA table cell is one grouping within a table. Cells are grouped horizontally and vertically . Usually information on the top header of a table and side header will "meet" in the middle at a particular cell with information regarding the two headers it is collinear with.-Kinds of cells in HTML:A...
, called 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 generally numbers, but can be also 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 a
datumA geodetic datum is a reference from which measurements are made. In surveying and geodesy, a datum is a set of reference points on the Earth's surface against which position measurements are made, and an associated model of the shape of the earth to define a geographic coordinate system...
. A single cell is usually referenced by its column and row (A2 would represent the cell below containing the value 10). 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 physical size can usually be tailored for its content by dragging its height or width at box intersections (or for entire columns or rows by dragging the column or rows headers).
My Spreadsheet
| | A | B | C | D |
| 01 | value1 | value2 | added | multiplied |
| 02 | 10 |
20 |
30 |
200 |
An array of cells is called a "sheet" or "worksheet". It is analogous to an array of
variablesIn computer programming, a variable is a symbolic name given to some known or unknown quantity or information, for the purpose of allowing the name to be used independently of the information it represents...
in a conventional
computer programA computer program is a sequence of instructions written to perform a specified task with a computer. A computer requires programs to function, typically executing the program's instructions in a central processor. The program has an executable form that the computer can use directly to execute...
(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 variableIn computer programming, a global variable is a variable that is accessible in every scope . Interaction mechanisms with global variables are called global environment mechanisms...
s within the spreadsheet ('read' access only except its own containing cell).
A cell may contain a
valueIn computer science, a value is an expression which cannot be evaluated any further . The members of a type are the values of that type. For example, the expression "1 + 2" is not a value as it can be reduced to the expression "3"...
or a
formulaIn mathematics, a formula is an entity constructed using the symbols and formation rules of a given logical language....
, 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 KayAlan Curtis Kay is an American computer scientist, known for his early pioneering work on object-oriented programming and windowing graphical user interface design, and for coining the phrase, "The best way to predict the future is to invent it."He is the president of the Viewpoints Research...
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 programmingIn computer science, functional programming is a programming paradigm that treats computation as the evaluation of mathematical functions and avoids state and mutable data. It emphasizes the application of functions, in contrast to the imperative programming style, which emphasizes changes in state...
.
Automatic recalculation
A standard of spreadsheets since the mid 80s , 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 recalculation of large or complex spreadsheets often reduced data entry speed. Many modern spreadsheets still retain this option.
Real-time update
This feature refers to updating a cell's contents periodically when its value is derived from an external source - such as a cell in another "remote" spreadsheet. For shared, web-based spreadsheets, it applies to "immediately" updating cells that have been altered by another user. All dependent cells have to 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 be applicable 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
percentageIn mathematics, a percentage is a way of expressing a number as a fraction of 100 . It is often denoted using the percent sign, “%”, or the abbreviation “pct”. For example, 45% is equal to 45/100, or 0.45.Percentages are used to express how large/small one quantity is, relative to another quantity...
cell format. The cell contents are not changed by this format, only the displayed value.
Some cell formats such as "numeric" or "
currencyIn economics, currency refers to a generally accepted medium of exchange. These are usually the coins and banknotes of a particular government, which comprise the physical aspects of a nation's money supply...
" can also specify the number of decimal places.
This can allow invalid operations (such as doing multiplication on a cell containing a date), resulting in illogical results without an appropriate warning.
Cell formatting
Depending on the capability of the spreadsheet application, each cell (like its counterpart the "style" in a
word processorA word processor is a computer application used for the production of any sort of printable material....
) can be separately formatted using the
attributeIn computing, an attribute is a specification that defines a property of an object, element, or file. It may also refer to or set the specific value for a given instance of such....
s 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). 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 to 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 lsome other cell(s), cause the computer to fetch the value of the named cell(s).
A cell on the same "sheet" is usually addressed as:-
=A1
A cell on a different sheet of the same spreadsheet is usually addressed as:-
=SHEET2!A1 (that is; the first cell in sheet 2 of same spreadsheet).
Some spreadsheet implementations allow a cell references to another spreadsheet (not the current open and active file) on the same computer or a local network. It may also refer to a cell in another open and active spreadsheet on the same computer or network that is defined as shareable. These references contain the complete filename, such as:-
='C:\Documents and Settings\Username\My spreadsheets\[main sheet]Sheet1!A1
In a spreadsheet, references to cells are automatically updated when new rows or columns are inserted or deleted.
Care must be taken however when adding a row immediately before a set of column totals to ensure that the totals reflect the additional rows values - which often they do not!
A
circular referenceA 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...
occurs when the formula in one cell has a reference that directly—or indirectly, through a chain of references, each one pointing to another cell that has another reference to the next cell on the chain—points to the one cell.
Many common kinds of errors cause such circular references.
However, there are some valid techniques that use such circular references.
Such techniques, after many recalculations of the spreadsheet, (usually) converge on the correct values for those cells.
Cell ranges
Likewise, instead of using a named range of cells, a range reference can be used. Reference to a range of cells is typically 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 been 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
calculationA calculation is a deliberate process for transforming one or more inputs into one or more results, with variable change.The term is used in a variety of senses, from the very definite arithmetical calculation of using an algorithm to the vague heuristics of calculating a strategy in a competition...
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
expressionAn expression in a programming language is a combination of explicit values, constants, variables, operators, and functions that are interpreted according to the particular rules of precedence and of association for a particular programming language, which computes and then produces another value...
consists of:
- values
In computer science, a value is an expression which cannot be evaluated any further . The members of a type are the values of that type. For example, the expression "1 + 2" is not a value as it can be reduced to the expression "3"...
, such as 2, 9.14 or 6.67E-11;
- references
In computer science, a reference is a value that enables a program to indirectly access a particular data item, such as a variable or a record, in the computer's memory or in some other storage device. The reference is said to refer to the data item, and accessing those data is called...
to other cells, such as, e.g., A1 for a single cell or B1:B3 for a range;
- arithmetic operators
Programming languages typically support a set of operators: operations which differ from the language's functions in calling syntax and/or argument passing mode. Common examples that differ by syntax are mathematical arithmetic operations, e.g...
, such as +, -, *, /, and others;
- relational operator
In computer science, a relational operator is a programming language construct or operator that tests or defines some kind of relation between two entities. These include numerical equality and inequalities...
s, such as >=, <, and others; and,
- functions, such as
SUM, TAN, and many others.
When a cell contains a formula, it often contains references to other cells. Such a cell reference is a type of variable. Its value is the value of the referenced cell or some derivation of it. If that cell in turn references other cells, the value depends on the values of those. References can be relative (e.g.,
A1, or
B1:B3), absolute (e.g.,
$A$1, or
$B$1:$B$3) or mixed row-wise or column-wise absolute/relative (e.g.,
$A1 is column-wise absolute and
A$1 is row-wise absolute).
The available options for valid formulas depends on the particular spreadsheet implementation but, in general, most arithmetic operations and quite complex nested conditional operations can be performed by most of today's commercial spreadsheets. Modern implementations also offer functions to access custom-build functions, remote data, and applications.
A formula may contain a condition (or nested conditions) - with or without an actual calculation - and is sometimes used purely to identify and highlight errors. In the example below, it is assumed the sum of a column of percentages (A1 through A6) is tested for validity and an explicit message put into the adjacent right-hand cell.
=IF(SUM(A1:A6) > 100, "More than 100%", SUM(A1:A6))
A spreadsheet does not, in fact, have to contain any formulas at all, in which case it could be considered merely a collection of data arranged in rows and columns (a
databaseA database is an organized collection of data for one or more purposes, usually in digital form. The data are typically organized to model relevant aspects of reality , in a way that supports processes requiring this information...
) 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 a number of supplied
functionsIn mathematics, a function associates one quantity, the argument of the function, also known as the input, with another quantity, the value of the function, also known as the output. A function assigns exactly one output to each input. The argument and the value may be real numbers, but they can...
, 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 ApplicationsVisual Basic for Applications is an implementation of Microsoft's event-driven programming language Visual Basic 6 and its associated integrated development environment , which are built into most Microsoft Office applications...
in the supplied Visual Basic editor, and such functions are automatically accessible on the worksheet. In addition, 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 function sq is introduced using the
Visual BasicVisual Basic for Applications is an implementation of Microsoft's event-driven programming language Visual Basic 6 and its associated integrated development environment , which are built into most Microsoft Office applications...
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,
subroutineIn computer science, a subroutine is a portion of code within a larger program that performs a specific task and is relatively independent of the remaining code....
s 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 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
chartA chart is a graphical representation of data, in which "the data is represented by symbols, such as bars in a bar chart, lines in a line chart, or slices in a pie chart"...
s,
graphsIn computer science, a graph is an abstract data structure that is meant to implement the graph and hypergraph concepts from mathematics.A graph data structure consists of a finite set of ordered pairs, called edges or arcs, of certain entities called nodes or vertices...
or
histogramIn statistics, a histogram is a graphical representation showing a visual impression of the distribution of data. It is an estimate of the probability distribution of a continuous variable and was first introduced by Karl Pearson...
s to be generated from specified groups of cells which are dynamically re-built as cell contents change. The generated graphic component can either be embedded within the current sheet or added as a separate object.
Multi-dimensional spreadsheets
In the late 1980s and early 1990s, first
Javelin SoftwareJavelin Software Corporation was a company in Cambridge, Massachusetts which developed an innovative modeling and data analysis product, also called Javelin, and later Javelin Plus...
and later
Lotus ImprovLotus Improv was a spreadsheet program from Lotus Development that attempted to re-define the way a spreadsheet should work. Instead of treating the grid as the system for referencing data, Improv made all data exist in named ranges. Operations on the data then referred to these names, rather than...
appeared and 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
algorithmIn mathematics and computer science, an algorithm is an effective method expressed as a finite list of well-defined instructions for calculating a function. Algorithms are used for calculation, data processing, and automated reasoning...
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, and this remains unique even today. Javelin was used primarily for financial modeling, but was also used to build instructional models in college chemistry courses, to model the world's economies, and by the military in the early Star Wars project. It is still in use by institutions for which model integrity is mission critical.
In these programs, a
time seriesIn statistics, signal processing, econometrics and mathematical finance, a time series is a sequence of data points, measured typically at successive times spaced at uniform time intervals. Examples of time series are the daily closing value of the Dow Jones index or the annual flow volume of the...
, or any variable, was an object in itself, not a collection of cells which 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 tableIn data processing, a pivot table is a data summarization tool found in data visualization programs such as spreadsheets or business intelligence software. Among other functions, pivot-table tools can automatically sort, count, total or give the average of the data stored in one table or spreadsheet...
s, 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, Javelin's 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.
Logical spreadsheets
Spreadsheets that have a formula language based upon
logical expressionsIn philosophy, Logic is the formal systematic study of the principles of valid inference and correct reasoning. Logic is used in most intellectual activities, but is studied primarily in the disciplines of philosophy, mathematics, semantics, and computer science...
, rather than
arithmetic expressionsArithmetic or arithmetics is the oldest and most elementary branch of mathematics, used by almost everyone, for tasks ranging from simple day-to-day counting to advanced science and business calculations. It involves the study of quantity, especially as the result of combining numbers...
are known as
logical spreadsheetA logical spreadsheet is a spreadsheet in which formulas take the form of logical constraints rather than function definitions.In traditional spreadsheet systems, such as Excel, cells are partitioned into "directly specified" cells and "computed" cells and the formulas used to specify the values of...
s. Such spreadsheets can be used to reason
deductivelyDeductive reasoning, also called deductive logic, is reasoning which constructs or evaluates deductive arguments. Deductive arguments are attempts to show that a conclusion necessarily follows from a set of premises or hypothesis...
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
matricesIn mathematics, a matrix is a rectangular array of numbers, symbols, or expressions. The individual items in a matrix are called its elements or entries. An example of a matrix with six elements isMatrices of the same size can be added or subtracted element by element...
) of data more efficiently in the computer itself.
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 spatial
Space is the boundless, three-dimensional extent in which objects and events occur and have relative position and direction. Physical space is often conceived in three linear dimensions, although modern physicists usually consider it, with time, to be part of a boundless four-dimensional continuum...
relationships to define program relationships. Humans have highly developed intuitionIntuition is the ability to acquire knowledge without inference or the use of reason. "The word 'intuition' comes from the Latin word 'intueri', which is often roughly translated as meaning 'to look inside'’ or 'to contemplate'." Intuition provides us with beliefs that we cannot necessarily justify...
s about spaces, and of dependencies between items. Sequential programming usually requires typing line after line of text, which must be read slowly and carefully to be understood and changed.
- They are forgiving, allowing partial results and functions to work. One or more parts of a program can work correctly, even if other parts are unfinished or broken. This makes writing and debugging programs much easier, and faster . Sequential programming usually needs every program line and character to be correct for a program to run. One error usually stops the whole program and prevents any result.
- Modern spreadsheets allow for secondary notation
In design, a secondary notation is defined as "visual cues which are not part of formal notation". Properties like position, indentation, color, symmetry, when used to convey information, are secondary notation....
. The program can be annotated with colors, typefaces, lines... to provide visual cues about the meaning of elements in the program.
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
graphIn mathematics and computer science, graph theory is the study of graphs, mathematical structures used to model pairwise relations between objects from a certain collection. A "graph" in this context refers to a collection of vertices or 'nodes' and a collection of edges that connect pairs of...
, where the nodes are spreadsheet cells, and the edges are references to other cells specified in formulas. This is often called the dependency graph of the spreadsheet. References between cells can take advantage of spatial concepts such as relative position and absolute position, as well as named locations, to make the spreadsheet formulas easier to understand and manage.
Spreadsheets usually attempt to automatically update cells when the cells on which they depend have been changed. The earliest spreadsheets used simple tactics like evaluating cells in a particular order, but modern spreadsheets 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 cells formulas are not generally invertible, though, this technique is of somewhat limited value.
Many of the concepts common to sequential programming models have analogues in the spreadsheet world. For example, the sequential model of the
indexed loopIn computer science a for loop is a programming language statement which allows code to be repeatedly executed. A for loop is classified as an iteration statement....
is usually represented as a table of cells, with similar formulas (normally differing only in which cells they reference).
Spreadsheets have evolved to use
scriptingA scripting language, script language, or extension language is a programming language that allows control of one or more applications. "Scripts" are distinct from the core code of the application, as they are usually written in a different language and are often created or at least modified by the...
programming languages like
VBAVisual Basic for Applications is an implementation of Microsoft's event-driven programming language Visual Basic 6 and its associated integrated development environment , which are built into most Microsoft Office applications...
as a tool for extensibility beyond what the spreadsheet language makes easy.
Shortcomings
While spreadsheets are a great step forward in quantitative modeling, they have deficiencies. At the level of overall user benefits, spreadsheets have several main shortcomings, especially concerning the unfriendliness of alpha-numeric cell addresses. Systematic study, and use, of the advanced features of a modern spreadsheet software package can minimize the incidence of these pitfalls.
- 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 variable (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 have significant reliability problems. Research studies estimate that roughly 94% of spreadsheets deployed in the field contain errors, and 5.2% of cells in unaudited spreadsheets contain errors.
-
- 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.
- 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. In fact, 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.
- 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 really forces you to decide on dimensionality right of the beginning of your spreadsheet creation, even though it is often most natural to make these choices after your spreadsheet model has matured. The desire to add and remove dimensions also arises in parametric and sensitivity analyses.
-
- Multi-dimensional spreadsheets and tools such as Analytica avoid this important pitfall by generalizing the 2-D paradigm of the classical spreadsheet to a multi-dimensional representation.
- Collaboration in authoring spreadsheet formulas can be difficult when such collaboration occurs at the level of cells and cell addresses.
-
- However, like programming languages, spreadsheets are capable of using aggregate cells with similar meaning and indexed variables with names that indicate meaning. Some spreadsheets have good collaboration features, and it is inadvisable to author at the level of cells and cell formulas to avoid obstacles to collaboration, where many people cooperate on data entry and many people use the same spreadsheet. In collaborative authoring, it is advisable to use the range-protection feature of spreadsheets that prevents the contents of specific parts of a worksheet from being inadvertently altered.
- Productivity of spreadsheet modelers is reduced by the antiquated cell-level focus of spreadsheets that is seldom used today. That old and poor approach means that even conceptually simple changes in spreadsheets (such as changing starting or ending time or time grain, adding new members or a level of hierarchy to a dimension, or changing one conceptual formula that is represented as hundreds of cell formulas) often require large numbers of manual cell-level operations (such as inserting or deleting cells/rows/columns, editing and copying formulas, re-laying out worksheets). Each of these manual corrections increases the risk of introducing further mistakes. For these reasons, the use of named variables and formulas that use variable names is the norm today.
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 proprietary commercial spreadsheet application written and distributed by Microsoft for Microsoft Windows and Mac OS X. It features calculation, graphing tools, pivot tables, and a macro programming language called Visual Basic for Applications...
(versions prior to 2007) and OpenOffice.org CalcOpenOffice.org Calc is the spreadsheet component of the OpenOffice.org software package.Calc is similar to Microsoft Excel, with a roughly equivalent range of features. Calc is capable of opening and saving most spreadsheets in Microsoft Excel file format...
(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 lost data.
- Lack of auditing and revision control
Revision control, also known as version control and source control , is the management of changes to documents, programs, and other information stored as computer files. It is most commonly used in software development, where a team of people may change the same files...
. This makes it difficult to determine who changed what and when. This can cause problems with regulatory compliance. Lack of revision control greatly increases the risk of errors due the inability to track, isolate and test changes made to a document.
- Lack of security
Computer security is a branch of computer technology known as information security as applied to computers and networks. The objective of computer security includes protection of information and property from theft, corruption, or natural disaster, while allowing the information and property to...
. Generally, if one has permission to open a spreadsheet, one has permission to modify any part of it (this is not the case of MS Office). This, combined with the lack of auditing above, can make it easy for someone to commit fraudIn criminal law, a fraud is an intentional deception made for personal gain or to damage another individual; the related adjective is fraudulent. The specific legal definition varies by legal jurisdiction. Fraud is a crime, and also a civil law violation...
.
- Because they are loosely structured, it is easy for someone to introduce an error
The word error entails different meanings and usages relative to how it is conceptually applied. The concrete meaning of the Latin word "error" is "wandering" or "straying". Unlike an illusion, an error or a mistake can sometimes be dispelled through knowledge...
, either accidentally or intentionally, by entering information in the wrong place or expressing dependencies among cells (such as in a formula) incorrectly.
- The results of a formula (example "=A1*B1") applies only to a single cell (that is, the cell the formula is actually located in — in this case perhaps C1), even though it can "extract" data from many other cells, and even real time dates and actual times. This means that to cause a similar calculation on an array of cells, an almost identical formula (but residing in its own "output" cell) must be repeated for each row of the "input" array. This differs from a "formula" in a conventional computer program which would typically have one calculation which would then apply to all of the input in turn. With current spreadsheets, this forced repetition of near identical formulas can have detrimental consequences from a quality assurance
Quality assurance, or QA for short, is the systematic monitoring and evaluation of the various aspects of a project, service or facility to maximize the probability that minimum standards of quality are being attained by the production process...
standpoint and is often the cause of many spreadsheet errors. Some spreadsheets have array formulas to address this issue.
- Trying to manage the sheer volume of spreadsheets which sometimes exists within an organization without proper security, audit trails, the unintentional introduction of errors and other items listed above can become overwhelming.
While there are built-in and third-party tools for desktop spreadsheet applications that address some of these shortcomings, awareness and use of these is generally low. A good example of this is that 55% of
Capital marketA capital market is a market for securities , where business enterprises and governments can raise long-term funds. It is defined as a market in which money is provided for periods longer than a year, as the raising of short-term funds takes place on other markets...
professionals "don't know" how their spreadsheets are audited; only 6% invest in a third-party solution
See also
- Attribute-value system
An attribute-value system is a basic knowledge representation framework comprising a table with columns designating "attributes" and rows designating "objects" An attribute-value system is a basic knowledge representation framework comprising a table with columns designating "attributes" (also...
- Comparison of spreadsheet software
-Online versus Desktop:Web-based spreadsheets do not in general depend on a particular operating system or similar constraints and are therefore generally more portable by their nature than desktop applications.-Collaborative spreadsheets:...
- End-user development
- List of spreadsheets
- List of online spreadsheets
- Model audit
A model audit is the colloquial term for the tasks performed when conducting due diligence on a financial model, in order to eliminate spreadsheet error. . A study in 1998 concluded that even MBA students with over 250 hours of spreadsheet development experience had a 24% chance of introducing...
- Moving and copying in spreadsheets
- Summation in spreadsheets
- Works Records System
The Works Records System , was an IBM mainframe based spreadsheet designed by Dr. Robert Mais, then an employee of ICI Mond Division in the UK and developed for the company to monitor chemical plant operations at Runcorn and elsewhere in the group...
History of spreadsheets
General information