Editing Spreadsheet (section)

== Shortcomings ==
While spreadsheets represented a major step forward in quantitative modeling, they have deficiencies. Their shortcomings include the perceived unfriendliness of alpha-numeric cell addresses.<ref>[http://wwwg.uni-klu.ac.at/stochastik.schule/ICTMT_5/ICTMT_5_CD/Special%20groups/CD_Special4.htm Douglas Butler, "Why are spreadsheets so unfriendly?", The Fifth International Conference on Technology in Mathematics Teaching, August 2001] {{Webarchive|url=https://web.archive.org/web/20220128192824/http://wwwg.uni-klu.ac.at/stochastik.schule/ICTMT_5/ICTMT_5_CD/Special%20groups/CD_Special4.htm |date=2022-01-28 }}. Accessed 25 June 2014</ref>

* Research by ClusterSeven has shown huge discrepancies in the way financial institutions and corporate entities understand, manage and police their often vast estates of spreadsheets and unstructured financial data (including [[comma-separated values]] (CSV) files and Microsoft Access databases). One study in early 2011 of nearly 1,500 people in the UK found that 57% of spreadsheet users have never received formal training on the spreadsheet package they use. 72% said that no internal department checks their spreadsheets for accuracy. Only 13% said that Internal Audit reviews their spreadsheets, while a mere 1% receive checks from their risk department.<ref name="Actuarial">{{cite web
|url=http://www.actuarialpost.co.uk/article/spreadsheet-risk-management-within-uk-organisations-351.htm
|title= Spreadsheet Risk Management within UK Organisations
|date=July 2011}}</ref>

* Spreadsheets can have reliability problems. Research studies estimate that around 1% of all formulas in operational spreadsheets are in error.<ref name="Errors_in_spreadsheets">{{cite web
|url=http://mba.tuck.dartmouth.edu/spreadsheet/product_pubs_files/Errors.pdf
|title= Errors in Operational spreadsheets
|access-date=2022-02-06
|first1= Stephen G. |last1=Powell |first2=Kenneth R. |last2=Baker |first3=Barry |last3=Lawson
|publisher=Tuck School of Business at Dartmouth College
|date=July–September 2009}}</ref>

::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.<ref name="Blaustein">{{cite web|url=http://www.theiia.org/intAuditor/itaudit/2009-articles/eliminating-spreadsheet-risks/|title=Eliminating Spreadsheet Risks|work=Internal Auditor Magazine|publisher=Institute of Internal Auditors (IIA)|access-date=2010-05-10|author=Richard E. Blaustein|date=November 2009|archive-url=https://web.archive.org/web/20100905061742/http://www.theiia.org/intAuditor/itaudit/2009-articles/eliminating-spreadsheet-risks/|archive-date=2010-09-05|url-status=dead}} [http://www.analyticsolutions.com/index_files/page0017.htm unabridged version] {{Webarchive|url=https://web.archive.org/web/20110118021341/http://www.analyticsolutions.com/index_files/page0017.htm |date=2011-01-18 }}</ref>

* The practical expressiveness of spreadsheets can be limited unless their modern features are used. Several factors contribute to this limitation. Implementing a complex model on a cell-at-a-time basis requires tedious attention to detail. Authors have difficulty remembering the meanings of hundreds or thousands of cell addresses that appear in formulas.

::These drawbacks are mitigated by the use of named variables for cell designations, and employing variables in formulas rather than cell locations and cell-by-cell manipulations. Graphs can be used to show instantly how results are changed by changes in parameter values. The spreadsheet can be made invisible except for a transparent user interface that requests pertinent input from the user, displays results requested by the user, creates reports, and has built-in error traps to prompt correct input.<ref name=Bullen>{{cite book|title=Professional Excel Development |author=Stephen Bullen, Rob Bovey & John Green |publisher=Addison-Wesley |url=https://books.google.com/books?id=VnegO0pMYlIC&pg=PA1 |isbn=978-0-321-50879-9 |year=2009 |edition=2nd }}</ref>

* 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.<ref name="Powell">{{cite web
|url=http://mba.tuck.dartmouth.edu/spreadsheet/product_pubs.html
|title= A Critical Review of the Literature on Spreadsheet Errors
|access-date=2008-04-18
|first1= Stephen G. |last1=Powell |first2=Kenneth R. |last2=Baker |first3=Barry |last3=Lawson
|publisher=Tuck School of Business at Dartmouth College
|date=2007-12-01}}</ref> That is another reason to use named variables and formulas employing named variables.

::Specifically, spreadsheets typically contain many copies of the same formula. When the formula is modified, the user has to change every cell containing that formula. In contrast, most computer languages allow a formula to appear only once in the code and achieve repetition using loops: making them much easier to implement and audit.

* The alteration of a dimension demands major surgery. When rows (or columns) are added to or deleted from a table, one has to adjust the size of many downstream tables that depend on the table being changed. In the process, it is often necessary to move other cells around to make room for the new columns or rows and to adjust graph data sources. In large spreadsheets, this can be extremely time-consuming.<ref name="Henrion">{{cite web|url=http://lumina.com/dlana/papers/Whats%20wrong%20with%20spreadsheets.pdf|title= What's Wrong with Spreadsheets&nbsp;– and How to Fix them with Analytica|access-date=2010-11-13|author= Max Henrion|date=2004-07-14}}</ref><ref name="Savage">{{cite journal|url=http://www.lionhrtpub.com/orms/orms-2-97/savage.html|title=Weighing the Pros and Cons of Decision Technology in Spreadsheets |journal=OR/MS Today |volume=24 |issue=1|access-date=2010-11-13|author=Sam Savage|date=February 2010}}</ref>
* Adding or removing a dimension is so difficult, one generally has to start over. The spreadsheet as a paradigm forces one to decide on dimensionality right of the beginning of one's spreadsheet creation, even though it is often most natural to make these choices after one's spreadsheet model has matured. The desire to add and remove dimensions also arises in parametric and sensitivity analyses.<ref name="Henrion" /><ref name="Savage" />
* Collaboration in authoring spreadsheet formulas can be difficult when such collaboration occurs at the level of cells and cell addresses.

Other problems associated with spreadsheets include:<ref>{{cite web|url=http://www.it-director.com/article.php?articleid=12681|title=Managing spreadsheets|access-date=2006-06-29|author=Philip Howard|date=2005-04-22|work=IT-Directors.com|archive-date=2006-03-16|archive-url=https://web.archive.org/web/20060316152542/http://www.it-director.com/article.php?articleid=12681|url-status=dead}}</ref><ref>{{cite web|url=http://panko.shidler.hawaii.edu/ssr/Mypapers/whatknow.htm|title=What We Know About Spreadsheet Errors|access-date=2006-09-22|author=Raymond R. Panko|date=January 2005|archive-date=2010-06-15|archive-url=https://web.archive.org/web/20100615182751/http://panko.shidler.hawaii.edu/ssr/Mypapers/whatknow.htm|url-status=dead}}</ref>

* Some sources advocate the use of specialized software instead of spreadsheets for some applications (budgeting, statistics)<ref>[http://www.exceluser.com/bi/mistake.htm Is Excel Budgeting a Mistake?] {{Webarchive|url=https://web.archive.org/web/20100803172128/http://www.exceluser.com/bi/mistake.htm |date=2010-08-03 }}<br />Excel's critics say that Excel is fundamentally unsuited for budgeting, forecasting, and other activities that involve collaboration or consolidation. Are they correct?</ref><ref>http://www.cs.uiowa.edu/~jcryer/JSMTalk2001.pdf {{Webarchive|url=https://web.archive.org/web/20090126164319/http://cs.uiowa.edu/~jcryer/JSMTalk2001.pdf |date=2009-01-26 }} Problems With Using Microsoft Excel for Statistics</ref><ref>{{cite web|url=http://www.burns-stat.com/pages/Tutor/spreadsheet_addiction.html|title=Spreadsheet Addiction|work=burns-stat.com}}</ref>
* The [[Microsoft Excel|Microsoft xls]] file format which is the default file format used in versions prior to 2007 had a capacity limit of 65,536 rows by 256 columns (2<sup>16</sup> and 2<sup>8</sup> respectively).<ref>{{cite web |title=Excel specifications and limits&nbsp;– Excel&nbsp;– Microsoft Office |url=https://support.office.com/en-us/article/excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3 |access-date=2018-11-06 |publisher=Office.microsoft.com}}</ref>  This presents a problem for people using larger datasets, and can result in data loss. In spite of the time passed, a recent example is the loss of [[COVID-19 in the United Kingdom|COVID-19]] positives in the British statistics for September and October 2020 when the Microsoft xls file format had been used in a legacy computer system.<ref name="BBC202010">{{cite news |last1=Kelion |first1=Leo |title=Excel: Why using Microsoft's tool caused Covid-19 results to be lost |url=https://www.bbc.com/news/technology-54423988 |access-date=20 April 2021 |work=BBC News |date=5 October 2020 |language=en-GB}}</ref>
* Lack of auditing and [[revision control]]. This makes it difficult to determine who changed what and when. This can cause problems with regulatory compliance. Lack of revision control greatly increases the risk of errors due to the inability to track, isolate and test changes made to a document.{{citation needed|reason=There are some admittedly restricted revision control techniques and applications that can be applied to spreadsheets.  This paragraph should acknowledge that and be more explicit about their significant limitations.|date=December 2016}} Modern spreadsheets include revision control.
* Lack of [[Computer security|security]]. Spreadsheets lack controls on who can see and modify particular data. This, combined with the lack of auditing above, can make it easy for someone to commit [[fraud]].<ref>{{cite web |url=http://www.deloitte.com/assets/Dcom-UnitedStates/Local%20Assets/Documents/AERS/us_risk_spreadsheet_mgt_022509%20(2).pdf |title=Spreadsheet Management: Not what you figured |author=<!--Staff writer(s); no by-line.--> |date=2009 |website=deloitte.com |publisher=[[Deloitte]] |access-date=24 July 2014}}</ref>
* Because they are loosely structured, it is easy for someone to introduce an [[error]], either accidentally or intentionally, by entering information in the wrong place or expressing dependencies among cells (such as in a formula) incorrectly.<ref name="Henrion" /><ref>{{cite web|url=http://www.accountingweb.co.uk/cgi-bin/item.cgi?id=40301|title=Excel spreadsheets in School budgeting – a cautionary tale (2001)|work=AccountingWEB|access-date=2007-12-18|archive-url=https://web.archive.org/web/20071007195537/http://www.accountingweb.co.uk/cgi-bin/item.cgi?id=40301|archive-date=2007-10-07|url-status=dead}}</ref><ref>{{cite web|url=http://www.eusprig.org/stories.htm|title=European Spreadsheet Risks Interest Group – spreadsheet risk management and solutions conference|work=eusprig.org}}</ref>
* The results of a formula (example "=A1*B1") applies only to a single cell (that is, the cell the formula is located in—in this case perhaps C1), even though it can "extract" data from many other cells, and even real-time dates and actual times. This means that to cause a similar calculation on an array of cells, an almost identical formula (but residing in its own "output" cell) must be repeated for each row of the "input" array. This differs from a "formula" in a conventional computer program, which typically makes one calculation that it applies to all the input in turn. With current spreadsheets, this forced repetition of near-identical formulas can have detrimental consequences from a [[quality assurance]] standpoint and is often the cause of many spreadsheet errors. Some spreadsheets have array formulas to address this issue.
*Trying to manage the sheer volume of spreadsheets that may exist in an organization without proper security, audit trails, the unintentional introduction of errors, and other items listed above can become overwhelming.

While there are built-in and third-party tools for desktop spreadsheet applications that address some of these shortcomings, awareness, and use of these is generally low. A good example of this is that 55% of [[Capital market]] professionals "don't know" how their spreadsheets are audited; only 6% invest in a third-party solution<ref>{{cite web|url=http://download.microsoft.com/download/4/6/1/461062F0-2D3E-4620-890E-826CE55D27B9/SpreadsheetsandCapitalMarkets.pdf|title=Spreadsheets and Capital Markets|date=June 2009|access-date=2009-08-13|archive-url=https://web.archive.org/web/20110604204942/http://download.microsoft.com/download/4/6/1/461062F0-2D3E-4620-890E-826CE55D27B9/SpreadsheetsandCapitalMarkets.pdf|archive-date=2011-06-04|url-status=dead}}</ref>