The Origins and Development of the Spreadsheet and Its Use Across Society

The electronic spreadsheet is among the most widely used pieces of software ever written. It is found on the desks of accountants and engineers, in the laboratories of biologists, in the offices of ministries and local councils, and on the home computers of people planning a wedding or keeping track of a mortgage. Its basic form, a grid of rows and columns in which some cells hold numbers or text and others hold formulas that calculate from them, has changed remarkably little since it first appeared on personal computers at the end of the 1970s. Yet the idea behind it is much older, and its history draws together the bookkeeping practices of medieval merchants, the theoretical work of accountants and economists in the mid-twentieth century, the early microcomputer industry, and the networked collaboration of the present day. This essay traces that history and considers how a tool designed to help with financial planning came to be used for almost every kind of quantitative work.

Ledgers, columns and the spread sheet

Long before computers, businesses and governments recorded financial information in tabular form. Clay tablets from Mesopotamia list quantities of grain and livestock in rows, and Roman estate managers kept accounts of income and expenditure. The particular tradition from which the modern spreadsheet descends, however, is that of the merchant’s ledger as it developed in the trading cities of late medieval Italy.

The most important innovation of that tradition was double-entry bookkeeping. In a double-entry system every transaction is recorded twice, once as a debit in one account and once as a credit in another, so that the total of all debits always equals the total of all credits. The method provides a built-in check on arithmetic and a systematic way of tracking assets, liabilities, income and expenses. Fragments of accounts kept in this manner survive from Tuscany around 1300, and the ledgers of the treasurers of the commune of Genoa from 1340 are often cited as an early fully developed example. The system was described in print by the Italian mathematician Luca Pacioli, whose Summa de arithmetica, geometria, proportioni et proportionalita, published in Venice in 1494, contained a treatise on accounts that circulated widely and was translated and adapted across Europe.

Over the following centuries, accounting developed a repertoire of tabular documents. Journals recorded transactions in order of date, ledgers grouped them by account, and trial balances listed the totals of every account to confirm that the books balanced. Accountants also prepared working papers in which figures were arranged in a grid, with items in rows and periods or categories in columns, so that totals could be calculated across and down. Such a document, drawn on a large sheet of paper ruled into columns and often spread across two facing pages of a ledger book, came to be called a spread sheet. The term is attested in American accounting usage in the first half of the twentieth century, and stationers sold pads of columnar paper for the purpose.

Working with a paper spread sheet was laborious. A budget for a company might contain hundreds of figures, many of which depended on others. If a single assumption changed, such as the expected price of a raw material or the rate of growth of sales, every figure that depended on it had to be recalculated by hand, erased and rewritten, and the totals checked again. Mechanical adding machines and later electronic calculators speeded up the arithmetic but did nothing to reduce the clerical work of tracing dependencies and rewriting results. The difficulty was not that any individual calculation was hard, but that a model of a business consisted of a large web of simple calculations linked together.

Accounting theory and the first computerised models

The idea of handing such a web of calculations to a computer emerged in the early 1960s, when mainframe computers were becoming common in large companies and universities. One of the clearest early statements came from the Austrian-born accounting scholar Richard Mattessich, then at the University of California, Berkeley. In a paper titled “Budgeting Models and System Simulation”, published in The Accounting Review in 1961, Mattessich described a way of representing the budget of a firm as a set of interrelated accounts arranged in a matrix, in which the values in some cells were derived from the values in others according to stated rules. He argued that such a model could be run on a computer, so that the consequences of different assumptions could be explored by changing inputs and recalculating.

Mattessich developed the idea further in his book Accounting and Analytical Methods, published in 1964, and in a companion volume he and his collaborators wrote programs in the FORTRAN language to carry out the calculations. The programs did not offer an interactive screen, since the computers of the time worked in batch mode with punched cards and printed output, but they embodied the central concept of the spreadsheet as a grid of cells whose relationships could be specified once and then evaluated automatically. Mattessich is therefore often credited as a conceptual pioneer of the electronic spreadsheet, and he himself later wrote about the connection between his work and the commercial products that followed.

During the same decade several companies developed programs for financial planning on mainframes. These systems, sometimes known as report generators or planning languages, allowed analysts to describe the rows and columns of a financial report and the formulas linking them in a special-purpose language, and the computer then produced the completed report. Products of this kind were used by large corporations through the 1970s, and IBM, among others, offered tools for financial planning that worked on the same principle. They were powerful but required specialist knowledge, and changes had to be submitted as jobs and returned some time later.

LANPAR and automatic recalculation

A significant step was taken in 1969 by two Canadian engineers, René Pardo and Remy Landau. Working with clients that included Bell Canada and AT&T, they faced the problem of preparing operating budgets for telephone companies, which involved large numbers of interdependent figures that had to be revised repeatedly. Writing a custom program for each budget was slow and expensive. Pardo and Landau instead created a system called LANPAR, an acronym for LANguage for Programming Arrays at Random, which allowed users to lay out a budget as a grid and specify formulas for its cells.

The most important technical feature of LANPAR was what its inventors called forward referencing, now usually known as natural order recalculation. In a grid of formulas, a cell may depend on cells that appear below it or to its right, and the order in which the cells must be computed is not simply the order in which they are written. LANPAR determined the dependencies automatically and evaluated the cells in an order that respected them, so that users did not need to arrange their formulas in a particular sequence. Pardo and Landau filed a patent application on the method in 1970. After a long process that included an appeal concerning the patentability of algorithms, United States Patent 4,398,249 was granted in 1983. By then the personal computer spreadsheet had already become established, and the patent was never widely enforced against it.

LANPAR ran on time-sharing mainframe systems and was used by a small number of large organisations. It did not reach the general public, but it showed that the essential logic of a spreadsheet, a grid of cells with formulas evaluated automatically according to their dependencies, could be built as a general tool rather than as a series of custom programs.

VisiCalc and the Apple II

The spreadsheet in its familiar interactive form was created at the end of the 1970s by Dan Bricklin and Bob Frankston. Bricklin had worked as a programmer at Digital Equipment Corporation, where he was involved in word processing software, before enrolling as a student at Harvard Business School in 1977. In his recollection, the idea came to him while watching a lecturer work through a financial model on a blackboard. When the lecturer found an error or wanted to change an assumption, he had to erase and rewrite a long series of figures. Bricklin imagined a calculator with a display large enough to show a whole table, on which a change to one number would cause every dependent number to update immediately.

Bricklin wrote a prototype in BASIC and then enlisted Frankston, a friend from the Massachusetts Institute of Technology, to write a full version. The two founded a company called Software Arts, and Frankston wrote the program in assembly language for the Apple II, one of the first mass-produced personal computers. They chose the Apple II partly because a development system was available to them and partly because the machine was widely sold. The program was published by Personal Software, a company run by Dan Fylstra, which later renamed itself VisiCorp after the product’s success. The name VisiCalc was a contraction of visible calculator.

VisiCalc went on sale in October 1979 at a price of about one hundred dollars. It presented the user with a grid of cells identified by a letter for the column and a number for the row, an arrangement that has been retained by almost every spreadsheet since. The screen of the Apple II could show only a small portion of the grid at once, but the user could move a cursor through it, type numbers, labels or formulas into cells, and watch the results recalculate. Formulas referred to other cells by their coordinates, and a small set of functions, such as sums and averages, was provided. The program could replicate a formula across a range of cells, adjusting its references appropriately, which made it possible to build a model with many periods or categories quickly.

The effect of VisiCalc on the personal computer market was considerable. Until its arrival, personal computers were largely bought by hobbyists, schools and enthusiasts, and their usefulness in business was not obvious. VisiCalc gave financial analysts, accountants and managers a reason to buy a computer, and there are many accounts of people walking into computer shops to ask for the program and then buying an Apple II in order to run it. It is frequently described as the first killer application, a program so useful that it drives the sale of the hardware on which it runs. Over its lifetime VisiCalc sold several hundred thousand copies, and it was ported to many other machines, including the Commodore PET, the Atari 800, the Tandy TRS-80 and eventually the IBM PC.

Bricklin and Frankston did not patent the program. In the late 1970s software patents in the United States were uncertain in their scope, and the lawyers they consulted advised that a patent was unlikely to be granted. As a consequence, the central idea of the interactive spreadsheet was free for competitors to adopt, and many did so within a few years.

SuperCalc, Multiplan and the early competitors

Among the first competitors was SuperCalc, developed by the company Sorcim and released in 1980 for computers running the CP/M operating system. SuperCalc became widely known when it was bundled, along with the WordStar word processor, with the Osborne 1, a portable computer launched in 1981. Its inclusion was part of the Osborne’s appeal, since buyers received a working business toolkit with the machine. SuperCalc improved on VisiCalc in several respects, including its handling of errors and its ability to resolve circular references by iteration, and later versions were sold for the IBM PC well into the late 1980s.

Microsoft entered the market in 1982 with Multiplan, designed to run on many different computers. Multiplan used a different method of referring to cells, numbering both rows and columns and allowing references relative to the current cell, a notation that survives as the R1C1 reference style in later Microsoft products. Multiplan sold well in some markets, particularly in Europe, and it ran on machines ranging from CP/M systems to the Apple II and the IBM PC. In the American market, however, it was soon overtaken by a product designed specifically for the IBM PC.

Lotus 1-2-3 and the IBM PC

The IBM Personal Computer, introduced in August 1981, rapidly became the standard machine for business use, and many companies that had previously been wary of microcomputers bought it because of the reputation of its manufacturer. A version of VisiCalc was available for it, but it had been translated from the Apple II and made little use of the PC’s larger memory and faster processor.

Mitch Kapor, who had worked for Personal Software writing companion programs to VisiCalc for producing charts and statistical analyses, founded Lotus Development Corporation in 1982 with Jonathan Sachs, who wrote most of the program. Their product, Lotus 1-2-3, was released in January 1983. The name referred to its three functions, spreadsheet, graphics and database, which were integrated in a single program. Lotus 1-2-3 was written in assembly language for the IBM PC and wrote directly to the display hardware, which made it markedly faster than its rivals. It could draw charts from the data in a worksheet, sort and query tables of records, and record sequences of keystrokes as macros that could be replayed to automate repetitive tasks.

The success of Lotus 1-2-3 was immediate. The company reported sales of more than fifty million dollars in its first year, and the program became the standard spreadsheet for the IBM PC and the many compatible machines that followed it. In the same way that VisiCalc had helped to sell the Apple II, 1-2-3 helped to establish the IBM PC as the dominant business computer. Its menu system, invoked with the slash key, became familiar to a generation of office workers, and books, training courses and add-on products formed an industry around it. VisiCorp, by contrast, declined rapidly. Disputes between VisiCorp and Software Arts led to litigation, and in 1985 Lotus bought Software Arts and withdrew VisiCalc from sale.

Lotus defended its position vigorously in the courts. In Lotus Development Corporation v. Paperback Software International, decided in 1990, a federal district court in Massachusetts found that a competing product had infringed Lotus’s copyright by copying the menu structure and overall appearance of 1-2-3. A later case against Borland, whose Quattro program included a compatibility mode that reproduced the 1-2-3 menu commands, reached a different result. In 1995 the United States Court of Appeals for the First Circuit held that the menu command hierarchy was a method of operation and therefore not protected by copyright. The Supreme Court heard the case but divided four to four in 1996, leaving the appeals court’s decision in place. The case remains a significant precedent in the law of software copyright.

Excel, the Macintosh and Windows

Microsoft’s response to Lotus 1-2-3 was Excel, which was first released in 1985 for the Apple Macintosh rather than for the IBM PC. The Macintosh, introduced in 1984, used a graphical interface with windows, menus and a mouse, and Excel was designed to take full advantage of it. Users could select ranges of cells by dragging, format text and numbers with different fonts and styles, and see charts displayed alongside their data. Excel also introduced a more efficient approach to recalculation in which only the cells affected by a change were recomputed, which mattered for large worksheets on the slow processors of the time.

In 1987 Microsoft released Excel for its own Windows environment. Windows was not yet widely used, and for several years Lotus 1-2-3 remained dominant on PCs running the text-based MS-DOS operating system. The situation changed in the early 1990s with the success of Windows 3.0, released in 1990, and Windows 3.1, released in 1992. Lotus was slow to produce a strong Windows version of 1-2-3, while Excel was already mature on the platform. Microsoft also began to sell Excel together with its word processor and presentation software as Microsoft Office, first offered in 1990, which gave businesses an inexpensive way to standardise on a set of applications. By the middle of the 1990s Excel had become the leading spreadsheet, and it has remained so since.

Excel 5.0, released in 1993, was an important version. It introduced workbooks containing multiple worksheets, which allowed related tables to be kept together in a single file, and it included Visual Basic for Applications, a programming language that allowed users to write macros manipulating worksheets, cells and charts as objects. It also introduced the PivotTable, which soon became one of the program’s most widely used features. Later versions added a growing library of functions, conditional formatting, data validation and tools for importing data from databases and other sources. In 2007 Microsoft adopted a new file format based on XML, known as Office Open XML, and increased the maximum size of a worksheet from 65,536 rows and 256 columns to 1,048,576 rows and 16,384 columns.

Quattro Pro and the competition of the 1990s

Borland, a company known for its programming tools, entered the spreadsheet market in 1987 with Quattro, and in 1989 it released Quattro Pro. Quattro Pro offered a graphical interface even under MS-DOS, the ability to work with several sheets in a notebook arranged by tabs, and strong charting and presentation features. It was sold at a lower price than 1-2-3 and gained a substantial market share. Borland later sold Quattro Pro to Novell in 1994, which in turn sold it to Corel in 1996, and it continued as part of Corel’s office suite.

The 1990s also saw experiments with different conceptions of the spreadsheet. Lotus Improv, released in 1991 for the NeXT computer, separated the data in a model from its presentation. Instead of referring to cells by coordinates, formulas in Improv referred to named categories, such as months or product lines, and users could rearrange the dimensions of a table by dragging them. Improv did not achieve commercial success, but its approach influenced the design of the pivot table and of later multidimensional analysis tools. Other products, including Javelin and Lotus Symphony, explored integrated or model-based approaches.

Formulas, functions and macros

The core of every spreadsheet is the formula. A formula begins with a marker, usually an equals sign in modern products, and combines references to other cells with arithmetic operators and functions. A cell containing the formula =B2*C2, for example, displays the product of the values in cells B2 and C2, and it changes whenever either of those values changes. The spreadsheet maintains a dependency graph recording which cells depend on which, and when a value changes it recalculates the dependent cells in an order consistent with the graph. If a cell depends, directly or indirectly, on itself, the spreadsheet reports a circular reference, although most products also allow iterative calculation for models in which circularity is intended.

An important feature introduced by VisiCalc and retained by its successors is the distinction between relative and absolute references. When a formula is copied from one cell to another, relative references are adjusted so that they point to cells in the same position relative to the new location, while absolute references, conventionally marked with dollar signs in the A1 notation, remain fixed. This allows a single formula to be written once and copied across hundreds or thousands of rows, each copy performing the same calculation on a different set of data.

Over time the library of functions grew to cover arithmetic, statistics, finance, dates and times, text manipulation, logic and lookup. Financial functions compute present values, internal rates of return and loan repayments. Statistical functions compute means, standard deviations, regressions and probability distributions. Lookup functions retrieve values from tables, and logical functions allow formulas to choose between alternatives. In recent years Microsoft has added dynamic arrays, introduced in 2018 and made generally available in 2020, which allow a single formula to return a range of results, and a function named LAMBDA, which allows users to define their own reusable functions within the spreadsheet. Researchers at Microsoft noted that with LAMBDA the Excel formula language became computationally complete in the technical sense, able in principle to express any computation. In 2023 Microsoft also began to offer the ability to run Python code within Excel workbooks.

Macros extend the spreadsheet beyond formulas. The keystroke macros of Lotus 1-2-3 allowed users to record and replay sequences of commands, and the macro language of early Excel provided a set of commands that could be stored in special sheets. Visual Basic for Applications gave Excel a full programming language, with which users could build forms, automate the production of reports, connect to databases and create applications that used the spreadsheet as a user interface. In many organisations VBA code written by individual employees came to support important processes, a pattern that brought both productivity and risk.

Pivot tables and data analysis

A pivot table summarises a table of records by grouping them according to the values of one or more fields and computing aggregates, such as sums, counts or averages, for each group. A table of sales transactions, for example, can be summarised to show total sales by region and month, and the arrangement can be changed by moving fields between rows, columns and filters. The concept owes much to Lotus Improv, whose designer Pito Salas had studied how analysts rearranged tables of figures, and Microsoft introduced its PivotTable feature in Excel 5.0 in 1993. Pivot tables made a form of analysis that had previously required database queries or specialised statistical software available to ordinary users, and they are now among the most widely taught features of the program.

Later developments extended the spreadsheet’s capacity for data analysis. Excel acquired tools for importing and transforming data from many sources, a data model capable of holding millions of rows in compressed form, and links to business intelligence services. Add-ins provided solvers for optimisation problems, statistical procedures and simulation. For many users the spreadsheet became the principal environment in which data was gathered, cleaned, analysed and presented, even when more specialised tools were available.

Google Sheets and the cloud

The spread of the internet brought a new model of software delivered through a web browser. In 2006 Google acquired 2Web Technologies, a company that had developed a web-based spreadsheet called XL2Web, and in June of that year it launched Google Spreadsheets as an experimental product. Later in 2006 it was combined with a word processor into a service known as Google Docs and Spreadsheets, and the spreadsheet component is now called Google Sheets, part of the broader Google Docs family of applications.

The most significant feature of Google Sheets was not its range of functions, which was initially much smaller than that of Excel, but its support for collaboration. A spreadsheet stored on Google’s servers could be opened by several people at once, each of whom could see the others’ changes as they were made. Files no longer needed to be sent back and forth by email, and the problem of reconciling different versions of the same workbook largely disappeared for teams that adopted the service. A complete revision history allowed earlier states of a document to be restored, and permissions could be set to allow others to view, comment on or edit a sheet.

Microsoft responded by developing web and collaborative versions of Excel, and by the late 2010s Excel files stored in Microsoft’s cloud services could also be edited by several people simultaneously. Apple produced its own spreadsheet, Numbers, first released in 2007, which emphasised layout and presentation and used a free-form canvas on which several tables could be placed. Open-source spreadsheets also developed, notably LibreOffice Calc, which descends from the StarCalc program created in Germany in the 1990s and later released as part of OpenOffice.org. Together these products ensured that a spreadsheet of some kind is available on almost every computer, tablet and smartphone.

Uses in business

The spreadsheet’s original purpose was financial planning, and business remains its largest domain of use. Budgets, forecasts, cash flow statements, sales reports, payroll calculations and inventory records are routinely kept in spreadsheets in companies of every size. In small businesses the spreadsheet may serve as the principal accounting system. In large organisations it often sits alongside enterprise software, used to extract, rearrange and analyse data that the larger systems hold, and to prepare reports for management.

The practice of financial modelling grew up around the spreadsheet. A financial model represents the operations of a business, a project or a transaction in a structured workbook, with inputs such as prices, volumes, costs and interest rates on one set of sheets and calculations and outputs on others. Investment banks use models to value companies and to structure mergers and acquisitions. Project finance specialists use them to assess the viability of power stations, toll roads and other infrastructure. Analysts in corporate finance departments use them to evaluate investments and plan capital spending. Because the spreadsheet allows inputs to be changed and results to be recalculated instantly, it encourages the exploration of scenarios and the testing of sensitivity to assumptions, which was precisely the use that Mattessich and Bricklin had envisaged.

Spreadsheets also became tools for operational management outside finance. Sales teams track customers and orders, human resources departments maintain records of staff and holidays, project managers plan schedules and allocate resources, and logistics teams monitor stock and shipments. In many cases these spreadsheets fill gaps left by formal information systems, and they can be created quickly by the people who need them without waiting for a software development project.

Uses in science and engineering

In science and engineering the spreadsheet is used for recording and analysing experimental data, performing calculations, and producing charts. A laboratory may keep records of samples and measurements in a workbook, compute averages and standard deviations, fit curves and prepare figures for publication. Engineers use spreadsheets to perform design calculations, check loads and tolerances, and produce tables of results. Although specialised software and programming languages are widely used for more complex analyses, the spreadsheet remains popular because it is familiar, flexible and readily shared with colleagues.

In the biological sciences, large tables of gene names, expression levels and other data are frequently exchanged as spreadsheet files, including in the supplementary materials of published papers. This practice has had consequences that are discussed below. In education, spreadsheets are used to teach mathematics and statistics, since students can see the effect of changing a parameter in a formula and explore numerical methods such as iteration and simulation without writing programs.

Uses in government and public administration

Governments at every level rely on spreadsheets. Treasury departments and ministries of finance use them to model tax and spending policies, statistical offices publish data tables in spreadsheet formats, and local authorities use them to manage budgets, housing lists and service records. Public sector analysts use spreadsheets to estimate the costs and benefits of proposed policies and to produce the forecasts on which decisions are based. Election officials, health authorities and emergency planners have all used spreadsheets to collect and process data quickly when other systems were not available.

The convenience that makes spreadsheets attractive in these settings also creates difficulties. Workbooks built by individuals may lack documentation, and when their authors move on, others may struggle to understand or maintain them. Data may be copied between files by hand, introducing errors, and the same figure may exist in several versions. Several public inquiries and audits have examined failures in which spreadsheets played a part, and some governments have issued guidance on the quality assurance of analytical models.

Uses in households

The spreadsheet reached the household along with the personal computer. Families use it to keep household budgets, track spending, plan savings, compare mortgages and loans, and manage the finances of clubs and societies. People use it to organise events, list guests and seating plans, keep records of collections, track exercise and diet, and plan travel. Templates provided by software vendors and shared online make it possible to create such documents without designing them from the start. For many people the spreadsheet is the only form of programming they ever undertake, even if they would not describe it in those terms.

Spreadsheet errors and their study

Because spreadsheets are easy to build and change, they are also easy to get wrong. A formula may refer to the wrong range, a row may be omitted from a total, a number may be typed incorrectly, or a value may be pasted over a formula. Such errors are often invisible, since the spreadsheet displays results rather than the calculations that produce them. Researchers have studied the frequency of errors for several decades. The information systems scholar Raymond Panko, at the University of Hawaii, reviewed field audits of operational spreadsheets and reported that a large majority of the workbooks examined contained at least one significant error, and that the rate of errors per formula cell, though small, was similar to the rate of human error observed in other kinds of detailed work. The European Spreadsheet Risks Interest Group, founded in 1999, holds annual conferences on the subject and collects reports of incidents in which spreadsheet mistakes have caused financial or other harm. Several cases have become widely known.

Growth in a Time of Debt

In 2010 the economists Carmen Reinhart and Kenneth Rogoff published a short paper, Growth in a Time of Debt, in the papers and proceedings issue of the American Economic Review. Drawing on historical data for a group of advanced economies, they reported that countries with public debt above about ninety per cent of gross domestic product had markedly lower median growth, and that their average growth was slightly negative. The paper was widely cited in debates about government spending in the years after the financial crisis of 2008.

In 2013 Thomas Herndon, a graduate student at the University of Massachusetts Amherst, together with Michael Ash and Robert Pollin, attempted to replicate the results and obtained the spreadsheet that Reinhart and Rogoff had used. In a paper published in the Cambridge Journal of Economics they identified several issues: the selective exclusion of some years of data for certain countries, an unconventional method of weighting the averages, and a spreadsheet error in which a formula computing an average omitted five countries, Australia, Austria, Belgium, Canada and Denmark, from the range. With these issues corrected, the average growth rate of the high-debt countries was positive, at about 2.2 per cent, rather than negative. Reinhart and Rogoff acknowledged the spreadsheet error but argued that the weighting and data choices were defensible and that their broader conclusions about the association between high debt and slower growth still held. The episode drew public attention to the role that unexamined spreadsheets could play in research that influenced policy.

The London Whale

In 2012 the American bank JPMorgan Chase reported large losses on a portfolio of credit derivatives managed by its Chief Investment Office in London. A trader at the centre of the positions had been nicknamed the London Whale because of their size, and the losses eventually amounted to about six billion dollars. A report by a task force of the bank, published in January 2013, examined the causes of the loss. Among them was a new value-at-risk model that the office had adopted earlier in 2012. The report found that the model was operated through a series of Excel spreadsheets, which had to be completed manually by copying and pasting data from one to another, and that in one calculation a figure was divided by the sum of two rates rather than by their average. The effect was to understate the measured volatility of the portfolio and therefore the risk it carried. The report noted that this was not the principal cause of the losses, which resulted from trading decisions and weak oversight, but the case became a standard illustration of the operational risks of relying on manually operated spreadsheet models.

Lost test results in England

In October 2020, during the Covid-19 pandemic, it emerged that 15,841 positive test results in England had not been transferred into the national case reporting system during the period from 25 September to 2 October. The cause, as reported by Public Health England and the government, was the use of the older Excel file format, known as XLS, at one stage in the process by which results from commercial laboratories were collated. The XLS format allows a maximum of 65,536 rows in a worksheet, and because each test result occupied several rows, a file could hold only about fourteen hundred results. When the files reached their limit, further records were cut off without warning. The missing results were added to the official figures once the problem was found, but the delay meant that the contacts of the affected people were not traced as quickly as intended. The episode was widely reported and led to the adoption of other methods for handling the data.

Gene names converted into dates

Another well-documented problem arises from Excel’s automatic conversion of data. When a value that resembles a date is typed or imported into a cell, Excel by default converts it into a date. Several human gene symbols, such as SEPT2, for septin 2, and MARCH1, for membrane-associated ring-CH-type finger 1, are interpreted in this way and turned into dates such as 2 September or 1 March. Other identifiers are converted into numbers in floating-point notation. The problem was described by Barry Zeeberg and colleagues in a paper in BMC Bioinformatics in 2004. In 2016 Mark Ziemann, Yotam Eren and Assam El-Osta surveyed supplementary files published with papers in leading genomics journals and found that about one fifth of those containing gene lists in Excel format were affected by such errors. A follow-up study published in 2021 found that the problem had persisted, as reported in Nature.

In 2020 the HUGO Gene Nomenclature Committee, which approves the names of human genes, published revised guidelines that took account of the problem, and it changed the symbols of a number of genes so that they would no longer be misread. SEPT1, for example, became SEPTIN1, and MARCH1 became MARCHF1. In 2023 Microsoft added an option allowing users to turn off some forms of automatic data conversion. The case is unusual in that a scientific naming convention was altered to accommodate the behaviour of a piece of software.

The culture of modelling

Around the professional use of spreadsheets a distinct culture has developed. Financial modellers have established conventions for the layout of workbooks, such as separating inputs from calculations and outputs, using consistent formulas across rows, colour-coding cells according to whether they contain inputs or formulas, and avoiding hard-coded numbers within formulas. Several published standards set out such practices in detail, and professional bodies offer training and certification in modelling. Firms specialising in model audit review workbooks built by others, checking formulas and logic before major financial decisions are made.

Users have also shared techniques through books, websites, forums and video channels. Online communities answer questions about formulas and macros, and some practitioners have become widely known for their tutorials. Users have devised elaborate applications within spreadsheets, including games, calendars, project management systems and even simple three-dimensional graphics, testing the limits of what the formula language and macros can do. Artists have used the grid of cells as a canvas for pictures, and some schools teach programming concepts through spreadsheet formulas.

Competitive spreadsheet work

Spreadsheet skill has also become the subject of competition. ModelOff, the Financial Modeling World Championships, was held from 2012 to 2019 and attracted participants from many countries, who solved case studies in financial modelling under time pressure. After it ended, the Financial Modeling World Cup was established in 2020, with a series of online rounds during the year and a final. A related event, the Microsoft Excel World Championship, emphasises puzzle-solving with spreadsheets rather than financial modelling, and presents competitors with problems that must be solved within a short time limit using formulas and other tools.

These competitions developed a form of presentation that resembles electronic sports. Matches have been streamed online with live commentary, and the final of the Excel World Championship in December 2023, held in Las Vegas, was broadcast on a sports television channel, with competitors eliminated in rounds as they fell behind. Commentators explain the approaches that competitors take, and audiences follow the progress of each participant’s screen. The emergence of spectator events around a business application is an indication of how widely spreadsheet skills are now held and valued.

The spreadsheet’s lasting place

More than four decades after VisiCalc, the spreadsheet remains central to the use of computers in work and private life. Its persistence reflects a combination of properties. It is immediately visible, since data and results are displayed together in a grid. It is responsive, recalculating as soon as a value changes. It is flexible, since the same tool can be used for accounts, experiments, lists and plans. It allows people without formal training in programming to build working computational models, and it makes those models easy to share. These same properties also explain the errors that occur when spreadsheets grow large and complex, are used for purposes their builders did not foresee, or are maintained by people who did not create them.

The history of the spreadsheet thus spans the merchant ledgers of medieval Italy, the theoretical models of mid-twentieth-century accounting, the mainframe planning systems of large corporations, the microcomputer programs that created a mass market for personal computing, and the collaborative services of the internet. Each stage built upon the grid of rows and columns that bookkeepers had long used to organise their figures, adding the capacity to calculate automatically, to respond interactively and to be shared. The result is a tool that has become part of the ordinary working equipment of modern society.

Further reading

• Richard Mattessich, “Budgeting Models and System Simulation”, The Accounting Review 36 (1961).
• Thomas Herndon, Michael Ash and Robert Pollin, “Does High Public Debt Consistently Stifle Economic Growth? A Critique of Reinhart and Rogoff”, Cambridge Journal of Economics 38 (2014).
• Mark Ziemann, Yotam Eren and Assam El-Osta, “Gene Name Errors Are Widespread in the Scientific Literature”, Genome Biology 17 (2016).
• The overview of the spreadsheet on Grokipedia.