Excel as a statistics workbench

Question

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.

Answer 1

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.

Answer 2

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.

Answer 3

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.

Answer 4

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

Spreadsheet addiction

If you find yourself in a situation where you really need to use excel (some accademic departments insist), then I would suggest using the Rexcel plugin. This lets you interface using Excel, but uses the R program as the computational engine. You don't need to know R to use it, you can use drop down menus and dialogs, but you can do a lot more if you do. Since R is doing the computations they are a lot more trustworthy than Excel and you have much better graphs and boxplots and other graphs missing from excel. It even works with the automatic cell updating in excel (though that can make things really slow if you have a lot of complex analyses to recompute every time). It does not fix all the problems from the spreadsheet addiction page, but it is a huge improvement over using straight excel.

Answer 5

Incidently, a question around the use of Google spreadsheets raised contrasting (hence, interesting) opinions about that, Do some of you use Google Docs spreadsheet to conduct and share your statistical work with others?

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.

Answer 6

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.

Answer 7

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.

Answer 8

Excel can be great both for exploratory data analysis and linear regression analysis with the right plugins. There are a number of commercial products, although most of them leave something to be desired in terms of the quality of the output they produce (they don't take full advantage of Excel's charting options or the ability to link with other Office applications) and in general they are not as good as they could be for data visualization and presentation. They also tend to not support a disciplined modeling approach in which (among other things) you keep a well-documented audit trail for your work. Here is a FREE plugin, "RegressIt", that addresses many of these issues: http://regressit.com. It provides very good support for exploratory analysis (including the ability to generate parallel time series plots and scatterplot matrices with up to 50 variables), it makes it easy to apply data transformations such as lagging, logging, and differencing (which are often not applied appropriately by naive users of regression), it provides very detailed table and chart output that supports best practices of data analysis, and it maintains an audit-trail worksheet that facilitates side-by-side model comparisons as well as keeping a record of what models were fitted in what order. It makes a good complement to whatever else you may be using, if you are dealing with multivariate data and at least some of your work is being carried out in an Excel environment.