Editing Microsoft Excel (section)

== Limitations and errors ==

In addition to [[Spreadsheet#Shortcomings|issues with spreadsheets]] in general, other problems specific to Excel include numeric precision, misleading statistics functions, mod function errors, date limitations and more.

=== Numeric precision ===
{{main|Numeric precision in Microsoft Excel}}
[[File:Excel fifteen figure.PNG|thumb|390px|Excel maintains 15 figures in its numbers, but they are not always accurate: the bottom line should be the same as the top line.]]

Despite the use of 15-figure precision, Excel can display many more figures (up to thirty) upon user request. But the displayed figures are ''not'' those actually used in its computations, and so, for example, the difference of two numbers may differ from the difference of their displayed values. Although such departures are usually beyond the 15th decimal, exceptions do occur, especially for very large or very small numbers. Serious errors can occur if decisions are made based upon automated comparisons of numbers (for example, using the Excel ''If'' function), as equality of two numbers can be unpredictable.{{citation needed|date=August 2016}}

In the figure, the fraction 1/9000 is displayed in Excel. Although this number has a decimal representation that is an infinite string of ones, Excel displays only the leading 15 figures. In the second line, the number one is added to the fraction, and again Excel displays only 15 figures. In the third line, one is subtracted from the sum using Excel. Because the sum in the second line has only eleven 1's after the decimal, the difference when 1 is subtracted from this displayed value is three 0's followed by a string of eleven 1's. However, the difference reported by Excel in the third line is three 0's followed by a string of ''thirteen'' 1's and two extra erroneous digits. This is because Excel calculates with about half a digit more than it displays.

Excel works with a modified 1985 version of the [[IEEE 754-2008|IEEE 754 specification]].<ref name="microsoft_spec">Microsoft's overview is found at: {{cite web|url=http://support.microsoft.com/kb/78113/en-us|title=Floating-point arithmetic may give inaccurate results in Excel|work=Revision 8.2; article ID: 78113|publisher=Microsoft support|date=June 30, 2010|access-date=July 2, 2010|archive-date=May 14, 2011|archive-url=https://web.archive.org/web/20110514172904/http://support.microsoft.com/kb/78113/EN-US|url-status=live}}</ref> Excel's implementation involves conversions between binary and decimal representations, leading to accuracy that is on average better than one would expect from simple fifteen digit precision, but that can be worse. See the main article for details.

Besides accuracy in user computations, the question of accuracy in Excel-provided functions may be raised. Particularly in the arena of statistical functions, Excel has been criticized for sacrificing accuracy for speed of calculation.<ref name="Altman">
{{cite book|title=Numerical issues in statistical computing for the social scientist|last1=Altman|first1=Micah|author-link1=Micah Altman|last2=Gill|first2=Jeff|author-link2=Jeff Gill (academic)|last3=McDonald|first3=Michael|year=2004|publisher=Wiley-IEEE|isbn=978-0-471-23633-7|chapter-url=https://books.google.com/books?id=j_KevqVO3zAC&pg=PA12|chapter=§2.1.1 Revealing example: Computing the coefficient standard deviation|page=12}}
</ref><ref name="deLevie2">{{cite book|first=Robert|last=de Levie|title=''cited work''|isbn=978-0-19-515275-3|year=2004|pages=45–46|publisher=Oxford University Press}}</ref>

As many calculations in Excel are executed using VBA, an additional issue is the accuracy of VBA, which varies with variable type and user-requested precision.<ref name="John_Walkenbach_0">
{{cite book|title=Excel 2010 Power Programming with VBA|chapter=Defining data types|pages=198 ''ff'' and Table 8–1|isbn=978-0-470-47535-5|first=John|last=Walkenbach|year=2010|publisher=Wiley|chapter-url=https://books.google.com/books?id=dtSdrjjVXrwC&pg=PA198}}</ref>

=== Statistical functions ===
The accuracy and convenience of statistical tools in Excel has been criticized,<ref>{{cite journal|last=McCullough|first=Bruce D.|author2=Wilson, Berry|title=On the accuracy of statistical procedures in Microsoft Excel 2000 and Excel XP|journal=Computational Statistics & Data Analysis|year=2002|volume=40|issue=4|pages=713–721|doi=10.1016/S0167-9473(02)00095-6}}</ref><ref>{{cite journal|last=McCullough|first=Bruce D.|author2=Heiser, David A.|title=On the accuracy of statistical procedures in Microsoft Excel 2007|journal=Computational Statistics & Data Analysis|year=2008|volume=52|issue=10|pages=4570–4578|doi=10.1016/j.csda.2008.03.004|citeseerx=10.1.1.455.5508}}</ref><ref>{{cite journal|last=Yalta|first=A. Talha|title=The accuracy of statistical distributions in Microsoft Excel 2007|journal=Computational Statistics & Data Analysis|year=2008|volume=52|issue=10|pages=4579–4586|doi=10.1016/j.csda.2008.03.005}}</ref><ref>{{cite web|url=http://www-unix.oit.umass.edu/~evagold/excel.html|title=Using Excel for Statistical Data Analysis—Caveats|publisher=University of Massachusetts School of Public Health|last=Goldwater|first=Eva|access-date=November 10, 2008|archive-date=May 16, 2008|archive-url=https://web.archive.org/web/20080516091435/http://www-unix.oit.umass.edu/%7Eevagold/excel.html|url-status=live}}</ref><ref name="Heiser">
{{cite web|url=http://www.daheiser.info/excel/frontpage.html|title=Microsoft Excel 2000, 2003 and 2007 faults, problems, workarounds and fixes|first=David A.|last=Heiser|year=2008|access-date=April 8, 2010|url-status=dead|archive-url=https://web.archive.org/web/20100418172334/http://www.daheiser.info/excel/frontpage.html|archive-date=April 18, 2010}}
</ref> as mishandling situations when data is missing, as returning incorrect values due to inept handling of round-off and large numbers, as only selectively updating calculations on a spreadsheet when some cell values are changed, and as having a limited set of statistical tools. Microsoft has announced that some of these issues are addressed in Excel 2010.<ref name="corrections">
[http://blogs.msdn.com/excel/archive/2009/09/10/function-improvements-in-excel-2010.aspx Function improvements in Excel 2010] {{webarchive|url=https://web.archive.org/web/20100406191751/http://blogs.msdn.com/excel/archive/2009/09/10/function-improvements-in-excel-2010.aspx |date=April 6, 2010 }} Comments are provided from readers that may illuminate some remaining problems.
</ref>

=== Excel MOD function error ===
Excel has issues with [[modulo operation]]s. In the case of excessively large results, Excel will return the error warning {{samp|#NUM!}} instead of an answer.<ref>{{cite web|url=http://www.bygsoftware.com/issues/modbug.html|title=The MOD bug|publisher=Byg Software|access-date=November 10, 2008|archive-url=https://web.archive.org/web/20160111114148/http://www.bygsoftware.com/issues/modbug.html|archive-date=January 11, 2016|url-status=dead}}</ref>

=== Fictional leap day in 1900 ===
Excel includes February 29, 1900, incorrectly treating 1900 as a [[leap year]], even though e.g. 2100 is correctly treated as a non-leap year.<ref>{{cite web|url=http://support.microsoft.com/kb/214058/en-us|title=Days of the week before March 1, 1900 are incorrect in Excel|publisher=Microsoft|access-date=November 10, 2008|archive-url=https://archive.today/20120714062344/http://support.microsoft.com/kb/214058/en-us|archive-date=July 14, 2012|url-status=live}}</ref><ref>{{cite web|url=https://docs.microsoft.com/office/troubleshoot/excel/wrongly-assumes-1900-is-leap-year|title=Excel incorrectly assumes that the year 1900 is a leap year|publisher=Microsoft|access-date=May 1, 2019|archive-date=June 7, 2023|archive-url=https://web.archive.org/web/20230607133954/https://learn.microsoft.com/en-ca/office/troubleshoot/excel/wrongly-assumes-1900-is-leap-year|url-status=live}}</ref> Thus, a formula counting dates between (for example) February 1, 1900 and March 1, 1900 will return an incorrect result. The bug originated from [[Lotus 1-2-3]], where it was deliberately implemented to save computer memory, and was also intentionally implemented in Excel for the purpose of [[bug compatibility]].<ref>{{cite web|url=http://www.joelonsoftware.com/items/2006/06/16.html|title=My First BillG Review|first=Joel|last=Spolsky|publisher=Joel on Software|date=June 16, 2006|access-date=November 10, 2008|archive-date=June 7, 2008|archive-url=https://web.archive.org/web/20080607153522/http://www.joelonsoftware.com/items/2006/06/16.html|url-status=live}}</ref> This legacy has later been carried over into Office Open XML file format.<ref name="The Contradictory Nature of OOXML">{{cite web|url=http://www.consortiuminfo.org/standardsblog/article.php?story=20070117145745854|title=The Contradictory Nature of OOXML|date=January 17, 2007|publisher=ConsortiumInfo.org|access-date=September 23, 2009|archive-date=August 18, 2009|archive-url=https://web.archive.org/web/20090818095243/http://www.consortiuminfo.org/standardsblog/article.php?story=20070117145745854|url-status=live}}</ref>

=== Date range ===
Excel supports dates with years in the range 1900–9999, except that December 31, 1899, can be entered as 0 and is displayed as 0-jan-1900.

Converting a fraction of a day into hours, minutes and days by treating it as a moment on the day January 1, 1900, does not work for a negative fraction.<ref>{{cite web|url=http://support.microsoft.com/kb/182247|title=Negative date and time value are displayed as pound signs (###) in Excel|publisher=Microsoft|access-date=March 26, 2012|archive-date=February 26, 2015|archive-url=https://web.archive.org/web/20150226120445/http://support.microsoft.com/kb/182247|url-status=live}}</ref>

=== Conversion problems ===
If text is entered that happens to be in a form that Excel interprets as a date, the text can be unintentionally changed to a standard date format. A similar problem occurs when a text happens to be in the form of a floating-point notation of a number. In these cases the original exact text cannot be recovered from the result. Formatting the cell as TEXT before entering ambiguous text prevents Excel from converting it.

This issue has caused a well known problem in the analysis of [[DNA]], for example in [[bioinformatics]]. As first reported in 2004,<ref name=ExcelAteMyDNA>{{cite journal|last1=Zeeberg|first1=Barry R|last2=Riss|first2=Joseph|last3=Kane|first3=David W|last4=Bussey|first4=Kimberly J|last5=Uchio|first5=Edward|last6=Linehan|first6=W Marston|last7=Barrett|first7=J Carl|last8=Weinstein|first8=John N|journal=[[BMC Bioinformatics]]|volume=5|issue=1|year=2004|pages=80|pmid=15214961|pmc=459209|doi=10.1186/1471-2105-5-80|doi-access=free|title=Mistaken Identifiers: Gene name errors can be introduced inadvertently when using Excel in bioinformatics}}</ref> genetic scientists found that Excel automatically and incorrectly converts certain [[gene nomenclature|gene names]] into dates. A follow-up study in 2016 found many [[peer review]]ed [[scientific journal]] papers had been affected and that "Of the selected journals, the proportion of published articles with Excel files containing gene lists that are affected by gene name errors is 19.6%."<ref name=ExcelAteMyDNAagain>{{cite journal|last1=Ziemann|first1=Mark|last2=Eren|first2=Yotam|last3=El-Osta|first3=Assam|title=Gene name errors are widespread in the scientific literature|journal=[[Genome Biology]]|volume=17|issue=1|pages=177|year=2016|pmid=27552985|doi=10.1186/s13059-016-1044-7|doi-access=free|pmc=4994289}}</ref> Excel parses the copied and pasted data and sometimes changes them depending on what it thinks they are. For example, [[MARCH2|MARCH1 (Membrane Associated Ring-CH-type finger 1)]] gets converted to the date March 1 (1-Mar) and [[SEPT2|SEPT2 (Septin 2)]] is converted into September 2 (2-Sep) etc.<ref name=bbcnewsexcel>{{cite web|url=https://www.bbc.co.uk/news/technology-37176926|publisher=BBC News|location=London|year=2016|title=Microsoft Excel blamed for gene study errors|author=Anon|access-date=June 21, 2018|archive-date=November 21, 2018|archive-url=https://web.archive.org/web/20181121204709/https://www.bbc.co.uk/news/technology-37176926|url-status=live}}</ref> While some secondary news sources<ref>{{cite web|last1=Cimpanu|first1=Catalin|title=One in Five Scientific Papers on Genes Contains Errors Because of Excel|url=http://news.softpedia.com/news/one-in-five-scientific-papers-on-genes-contains-errors-because-of-excel-507582.shtml|website=[[Softpedia]]|publisher=SoftNews|date=August 24, 2016|access-date=August 24, 2016|archive-date=August 25, 2016|archive-url=https://web.archive.org/web/20160825195333/http://news.softpedia.com/news/one-in-five-scientific-papers-on-genes-contains-errors-because-of-excel-507582.shtml|url-status=live}}</ref> reported this as a fault with Excel, the original authors of the 2016 paper placed the blame with the researchers misusing Excel.<ref name=ExcelAteMyDNAagain/><ref>{{cite web|url=http://genomespot.blogspot.co.uk/2016/08/my-personal-thoughts-on-gene-name-errors.html|first=Mark|last=Ziemann|year=2016|title=Genome Spot: My personal thoughts on gene name errors|website=genomespot.blogspot.co.uk|archive-url=https://web.archive.org/web/20160830135847/http://genomespot.blogspot.co.uk/2016/08/my-personal-thoughts-on-gene-name-errors.html?m=1|archive-date=August 30, 2016}}</ref>

In August 2020 the [[HUGO Gene Nomenclature Committee|HUGO Gene Nomenclature Committee (HGNC)]] published new guidelines in the journal [[Nature (journal)|Nature]] regarding gene naming in order to avoid issues with "symbols that affect data handling and retrieval." {{as of|2020}}, 27 genes had been renamed, including changing MARCH1 to MARCHF1 and SEPT1 to SEPTIN1 in order to avoid accidental conversion of the gene names into dates.<ref>{{Cite web|last=Vincent|first=James|date=August 6, 2020|title=Scientists rename human genes to stop Microsoft Excel from misreading them as dates|url=https://www.theverge.com/2020/8/6/21355674/human-genes-rename-microsoft-excel-misreading-dates|access-date=October 9, 2020|website=The Verge|language=en|archive-date=October 9, 2020|archive-url=https://web.archive.org/web/20201009125307/https://www.theverge.com/2020/8/6/21355674/human-genes-rename-microsoft-excel-misreading-dates|url-status=live}}</ref>

In October 2023, Microsoft introduced a settings option that allows the conversion to be disabled.<ref>{{cite web|title=Microsoft Fixes Excel Feature That Forced Scientists to Rename Human Genes|url=https://gizmodo.com/microsoft-fixes-excel-feature-that-forced-scientists-to-1850949443|website=Gizmodo|access-date=October 25, 2023|language=en|date=October 23, 2023}}</ref>

=== Errors with large strings ===

The following functions return incorrect results when passed a string longer than 255 characters:<ref>{{cite web|title=Excel: type() and <code>WorksheetFunction.IsText()</code> fail for long strings|url=https://stackoverflow.com/questions/53128145/excel-type-and-worksheetfunction-istext-fail-for-long-strings|website=Stack Overflow|date=November 3, 2018|access-date=November 5, 2018|archive-date=November 5, 2018|archive-url=https://web.archive.org/web/20181105211758/https://stackoverflow.com/questions/53128145/excel-type-and-worksheetfunction-istext-fail-for-long-strings|url-status=live}}</ref>
* {{code|type()}} incorrectly returns 16, meaning "Error value"
* {{code|IsText()}}, when called as a method of the VBA object {{code|WorksheetFunction}} (i.e., {{code|WorksheetFunction.IsText()}} in VBA), incorrectly returns "false".

=== Filenames ===
Microsoft Excel will not open two documents with the same name and instead will display the following error:

: A document with the name '%s' is already open. You cannot open two documents with the same name, even if the documents are in different folders. To open the second document, either close the document that is currently open, or rename one of the documents.<ref>{{cite news|url=http://www.thehindubusinessline.com/ew/2004/08/02/stories/2004080200260401.htm|newspaper=[[Business Line]]|location=Chennai|title=Trouble with macros|first=Gary|last=Rajah|date=August 2, 2004|access-date=March 19, 2019|archive-date=July 29, 2010|archive-url=https://web.archive.org/web/20100729203805/http://www.thehindubusinessline.com/ew/2004/08/02/stories/2004080200260401.htm|url-status=live}}</ref>
The reason is for calculation ambiguity with linked cells. If there is a cell {{code|1=='[Book1.xlsx]Sheet1'!$G$33}}, and there are two books named "Book1" open, there is no way to tell which one the user means.<ref>{{cite web|url=http://blogs.msdn.com/b/excel/archive/2009/01/07/why-can-t-i-open-two-files-with-the-same-name.aspx|title=Microsoft Excel – Why Can't I Open Two Files With the Same Name?|archive-url=https://web.archive.org/web/20100729140409/http://blogs.msdn.com/b/excel/archive/2009/01/07/why-can-t-i-open-two-files-with-the-same-name.aspx|archive-date=July 29, 2010|access-date=March 19, 2019|first=Joseph|last=Chirilov|date=January 8, 2009|work=[[MSDN Blogs]]|publisher=[[Microsoft Corporation]]}}</ref>