# Excel as a statistics workbench

It seems that lots of people (including me) like to do exploratory data analysis in Excel. Some limitations, such as the number of rows allowed in a spreadsheet, are a pain but in most cases don't make it impossible to use Excel to play around with data.

A paper by McCullough and Heiser, however, practically screams that you will get your results all wrong -- and probably burn in hell as well -- if you try to use Excel.

Is this paper correct or is it biased? The authors do sound like they hate Microsoft.

• A recent analysis of accuracy of spreadsheet software for statistical calculations appears in [Kellie B. Keeling and Robert J. Pavur (2011): Statistical Accuracy of Spreadsheet Software, The American Statistician, 65:4, 265-273] (currently a free download at amstat.tandfonline.com/doi/pdf/10.1198/tas.2011.09076). The results are mixed and perhaps a little surprising. Notable is the huge improvement in distribution calculations between Excel 2007 and Excel 2010 (which appears to be more accurate than R or SAS). – whuber Sep 13 '12 at 19:23

Use the right tool for the right job and exploit the strengths of the tools you are familiar with.

In Excel's case there are some salient issues:

• Please don't use a spreadsheet to manage data, even if your data will fit into one. You're just asking for trouble, terrible trouble. There is virtually no protection against typographical errors, wholesale mixing up of data, truncating data values, etc., etc.

• Many of the statistical functions indeed are broken. The t distribution is one of them.

• The default graphics are awful.

• It is missing some fundamental statistical graphics, especially boxplots and histograms.

• The random number generator is a joke (but despite that is still effective for educational purposes).

• Avoid the high-level functions and most of the add-ins; they're c**p. But this is just a general principle of safe computing: if you're not sure what a function is doing, don't use it. Stick to the low-level ones (which include arithmetic functions, ranking, exp, ln, trig functions, and--within limits--the normal distribution functions). Never use an add-in that produces a graphic: it's going to be terrible. (NB: it's dead easy to create your own probability plots from scratch. They'll be correct and highly customizable.)

In its favor, though, are the following:

• Its basic numerical calculations are as accurate as double precision floats can be. They include some useful ones, such as log gamma.

• It's quite easy to wrap a control around input boxes in a spreadsheet, making it possible to create dynamic simulations easily.

• If you need to share a calculation with non-statistical people, most will have some comfort with a spreadsheet and none at all with statistical software, no matter how cheap it may be.

• It's easy to write effective numerical macros, including porting old Fortran code, which is quite close to VBA. Moreover, the execution of VBA is reasonably fast. (For example, I have code that accurately computes non-central t distributions from scratch and three different implementations of Fast Fourier Transforms.)

• It supports some effective simulation and Monte-Carlo add-ons like Crystal Ball and @Risk. (They use their own RNGs, by the way--I checked.)

• The immediacy of interacting directly with (a small set of) data is unparalleled: it's better than any stats package, Mathematica, etc. When used as a giant calculator with loads of storage, a spreadsheet really comes into its own.

• Good EDA, using robust and resistant methods, is not easy, but after you have done it once, you can set it up again quickly. With Excel you can effectively reproduce all the calculations (although only some of the plots) in Tukey's EDA book, including median polish of n-way tables (although it's a bit cumbersome).

In direct answer to the original question, there is a bias in that paper: it focuses on the material that Excel is weakest at and that a competent statistician is least likely to use. That's not a criticism of the paper, though, because warnings like this need to be broadcast.

• @whuber A nice and handy overview of pros and cons! – chl Oct 7 '10 at 20:32
• +1 nice and balanced. I especially like the point about "immediacy of interacting directly" which I think is Excel's (or really, the spreadsheet's) biggest selling point. Declarative programming for the masses -- which explains why some people think that 80% of the world's business logic is written in Excel (worth pointing out to programmers and statisticians who argue about R v SAS or Java v C++, etc). – ars Oct 7 '10 at 20:42
• I heard that Microsoft hired some numerical analysts several years ago to fix the broken functions in Excel. Do you know whether the problems with Excel are still there in the 2007 or 2010 versions? – John D. Cook Oct 8 '10 at 0:44
• @Zach For instance, using Excel 2002, compute =TINV(2*p,df) for values of p varying from .01 down almost to 0 and compare them with the correct values. (I checked with df ranging from 2 through 32.) The errors start off in the sixth significant figure and then explode once p is around 1.E-5 or lower. Even though these values of p are small, they are realistic values to test because they are crucial for multiple-comparisons tests and for computing values related to the t distribution, such as the non-central t. – whuber Jun 15 '11 at 14:16
• I think your first bullet point needs to have stars and arrows calling it out. Spreadsheets provide no audit trail, which is critical if you intend to do work that someone actually relies on. R, by contrast, doesn't directly provide an audit trail, but since it accepts commands and you can save the commands to import, preprocess, process, graph, etc, in a separate file you can have a trail of what you did to get Graph #1, and you can recreate it from scratch, just in case you suddenly have reason to question it. – Wayne Mar 20 '12 at 16:25

An interesting paper about using Excel in a Bioinformatics setting is:

Mistaken Identifiers: Gene name errors can be introduced inadvertently when using Excel in bioinformatics, BMC Bioinformatics, 2004 (link).

This short paper describes the problem of automatic type conversions in Excel (in particular date and floating point conversions). For example, the gene name Sept2 is converted to 2-Sept. You can actually find this error in online databases.

Using Excel to manage medium to large amounts of data is dangerous. Mistakes can easily creep in without the user noticing.

• This is far and away the aspect of Excel that infuriates me the most. Data storage needs explicit data types, not formatting. – Matt Parker Oct 8 '10 at 2:45
• Actually, this is something about MS software in general that annoys me: it changes your input into what it believes you actually meant, and you usually don't even see it happening. – Carlos Accioly Oct 8 '10 at 4:17
• @csgillespie (+1) Good to hear of Excel from this perspective! – chl Oct 8 '10 at 6:15
• My favorite error occurred when Excel used to quietly truncate fields during export to other formats. In a file of pesticide concentrations in soil, it converted a value of 1,000,050 (extraordinarily toxic) to 50 (almost inconsequential) by clipping off the initial digit! – whuber Oct 8 '10 at 13:04
• @Matt its only infuriating if you are "storing" data in Excel. Its a great feature when using Excel purely as a front end for reporting and chart creation. – Anonymous Type Dec 20 '10 at 22:02

Well, the question whether the paper is correct or biased should be easy: you could just replicate some of their analyses and see whether you get the same answers.

McCullough has been taking different versions of MS Excel apart for some years now, and apparently MS haven't seen fit to fix errors he pointed out years ago in previous versions.

I don't see a problem with playing around with data in Excel. But to be honest, I would not do my "serious" analyses in Excel. My main problem would not be inaccuracies (which I guess will only very rarely be a problem) but the impossibility of tracking and replicating my analyses a year later when a reviewer or my boss asks why I didn't do X - you can save your work and your blind alleys in commented R code, but not in a meaningful way in Excel.

• (+1) You are nicely echoing the discussion on Medstats about the need to keep a record of data edit and analysis (j.mp/dAyGGY)! Thx. – chl Oct 7 '10 at 20:01
• Saving your "work and blind alleys" isn't any harder to do with Excel than with R. It's just a matter of actually doing it. The main problem with Excel is related to its strength: it's all too easy to change something inadvertently. But for EDA--the focus of the OP--we rarely if ever save everything we do. EDA, after all, is supposed to be interactive. – whuber Oct 7 '10 at 20:18
• it is possible to keep a reproducable record of your methods if you do it in VB, but the GUI focus of Excel doesn't encourage that behaviour. – James Oct 8 '10 at 10:06

I have in mind an older paper which didn't seem so pessimist, but it is only marginally cited in the paper you mentioned: Keeling and Pavur, A comparative study of the reliability of nine statistical software packages (CSDA 2007 51: 3811). But now, I found yours on my hard drive. There was also a special issue in 2008, see Special section on Microsoft Excel 2007, and more recently in the Journal of Statistical Software: On the Numerical Accuracy of Spreadsheets.

I think it is a long-standing debate, and you will find varying papers/opinions about Excel reliability for statistical computing. I think there are different levels of discussion (what kind of analysis do you plan to do, do you rely on the internal solver, are there non-linear terms that enter a given model, etc.), and sources of numerical inaccuracy might arise as the result of proper computing errors or design choices issues; this is well summarized in

M. Altman, J. Gill & M.P. McDonald, Numerical Issues in Statistical Computing for the Social Scientist, Wiley, 2004.

Now, for exploratory data analysis, there are various alternatives that provide enhanced visualization capabilities, multivariate and dynamic graphics, e.g. GGobi -- but see related threads on this wiki.

But, clearly the first point you made addresses another issue (IMO), namely that of using a spreadsheet to deal with large data set: it is simply not possible to import a large csv file into Excel (I'm thinking of genomic data, but it applies to other kind of high-dimensional data). It has not been built for that purpose.

• I like the nuance about "different levels of discussion." – whuber Oct 7 '10 at 20:39

The papers and other participants point out to technical weaknesses. Whuber does a good job of outlining at least some of its strengths. I personally do extensive statistical work in Excel (hypothesis testing, linear and multiple regressions) and love it. I use Excel 2003 with a capacity of 256 columns and 65,000 rows which can handle just about 100% of the data sets I use. I understand Excel 2007 has extended that capacity by a huge amount (rows in the millions).

As Whuber mentions, Excel also serves as a starting platform for a multitude of pretty outstanding add-in software that are all pretty powerful and easy to use. I am thinking of Crystal Ball and @Risk for Monte Carlo Simulation; XLStat for all around powerful stats and data analysis; What's Best for optimization. And, the list goes on. It's like Excel is the equivalent of an IPod or IPad with a zillion of pretty incredible Apps. Granted the Excel Apps are not cheap. But, for what they are capable of doing they are typically pretty great bargains.

As far as model documentation is concerned, it is so easy to insert a text box where you can literally write a book about your methodology, your sources, etc... You can also insert comments in any cell. So, if anything Excel is really good for facilitating embedded documentation.

• Thanks for the additional points and for sharing your perspective. – whuber Oct 8 '10 at 2:31
• great points, good to hear someone using it the right way, and benefiting. – Anonymous Type Dec 20 '10 at 22:09

Excel is no good for statistics, but it can be wonderful for exploratory data analysis. Take a look at this video for some particularly interesting techniques. Excel's ability to conditionally color your data and add in-cell bar charts can give great insight into the structure of your raw data.

Another good reference source for why you might not want to use excel is: