Audience Dialogue

Using Excel for survey analysis (1)

What's the best way to analyse survey data with Excel? My advice: don't try! A statistical program is much better: something like SPSS, Statistica, Epi Info, SAS, Minitab, or many others.

However, a lot of people don't have a statistical program. There's not only the cost involved ($1000 upwards for most statistical packages) but also the time it takes to learn to use these massive programs.  For most people the cost associated is prohibitive; after all you could take a vacation with cheap flights to Timbuktu or Hawaii for $1,000.

If you already know Excel well, you can use that - in a limited way. There are various methods you can use to analyse survey data in Excel, but most of them are a real pain, and are likely to produce errors unless you are very, very careful.

This group of pages is written for people who are familiar with using Excel, but are not experts. It shows you some simple ways of using Excel for survey analysis, covering features such as the Data Analysis Toolpak, data entry forms, data validation, and pivot tables.

The instructions on these pages work with Excel 97, Excel 98 (Mac), and Excel 2000. 

Note, a useful visual guide based on Excel 2002 has been produced by Jennifer Leahy of the University of Wisconsin, you can download the PDF (2.8 Mb) here.  

There does not seem to be much new written about using Excel for survey data analysis, which in itself tells you it's not the best tool for the job, and better options exist..  Read on for more comments and references about Excel, all a bit dated now but hopefully will be of benefit to some people.


Excel has different ways of handling continuous variables (numbers with decimal points) and nominal variables (questions with answers which don't form a scale - e.g. red, blue, green). For continuous variables, you probably want to produce averages, standard deviations, and ranges, and test significance using the T test. The Data Analysis Toolpak is best for this.

To analyse nominal variables, you'll probably want to produce frequency distributions and crosstabs, perhaps with simple significance tests such as chi squared. Excel's best option for these is Pivot Tables. (You can't do a chi squared test directly from a pivot table, unfortunately: there are several tedious steps to go through, with a high chance of error unless you understand the chi squared test thoroughly.)

Before you can analyse anything, you have to enter the data from the questionnaires. This is by far the most time-consuming part of the process.


Read more

Using Excel to enter data from questionnaires

Analysing numeric variables using the Data Analysis Toolpak

Analysing nominal variables using pivot tables

Creating two-way tables (crosstabs) using pivot tables

Analysing numeric variables using the Frequencies function

Using spreadsheets for content analysis. Perhaps surprisingly, spreadsheet software works very nicely for the analysis of short verbal data.


Some related pages on other websites

Problems with using Microsoft Excel for statistics - a detailed explanation of the problems, by Jonathan D. Cryer (PDF file - requires Acrobat Reader).

Is it practical to use Excel for stats?- a page that discusses the limitations of Excel.

Using Excel for Statistical Data Analysis, by Eva Goldwater.

Tips for using Excel - from the Wisconsin Tobacco Control Board.

Microsoft's own guide to using Excel.

Disciplined use of spreadsheets for data entry - do it right, or you'll be smacked by the University of Reading!

Guidelines for Writing Spreadsheets. It's horrifyingly easy to make major errors when you create spreadsheets. These guidelines have some suggestions on avoiding errors. I can offer some additional suggestions (the result of bitter experience):
(1) Display calculated values on a different colour background from entered values. When I set up a spreadsheet for somebody else to use, I can tell them "Enter data only in the cells with a white background. The pale blue background is general information, and the yellow background is for automatic calculations."
(2) Wherever possible, calculate important totals in two different ways (e.g. sum of rows and sum of columns), and put the difference between those two calculated values in a cell labelled Check. If that cell is not zero, you have a problem!
(3) Display column totals at the top of the page, not the bottom. Though this is opposite to the way you'd do it on paper, it has two advantages on a computer screen: (a) it saves you from scrolling down to see the total, and (b) if you set the range to add to be the entire column, you can add more entries later, without changing the formula.
(4) If you really want to do database work - not the ad hoc calculations that spreadsheets are so good at - use a database program, not a spreadsheet. Two database programs that are fine for things like name and address records are Filemaker (Mac and PC) and Approach (PC). Statistical database programs, like Epi Info and SAS, are also excellent for normal database work. I don't recommend SPSS for this purpose, or Microsoft Access, unless you already know them backwards. Both have too many nasty peculiarities that only expert users get to know about.

Meanwhile, if you want to know a lot more about survey analysis why don't you buy a copy of our uniquely comprehensive and practical guide to audience and media research called Know your Audience?  You can scan the contents of the book and if you like it, you can buy a copy simply and securely using PayPal for just $20, by credit card or PayPal account, click the button below.

 


Excel add-ins

Why should Excel be so awkward for analysing survey data? And it's not only Excel: almost all spreadsheets share the same problem. About 10 years ago, Lotus produced a spreadsheet which was almost a database, called Improv. This was almost perfect for survey analysis, but after a few years, Lotus discontinued it. It seemed that Improv was ahead of its time, and didn't sell well enough. However, in 2003, Quantrix was introduced (www.quantrix.com) based on Improv - but very expensive.

Statistical software, though it makes calculations quickly and efficiently, has the disadvantage of hiding its methods. You want a principal components factor analysis? Just choose some variables and click this box! In five seconds, you'll see a result that would have taken a mathematician a week in pre-calculator days. It'll be accurate the first time, too. Unfortunately, the software won't help you understand what you're doing.

The potential of spreadsheets is that they can lay out calculations step by step, and show you how a result is achieved. So using a spreadsheet can be a power-assisted method of learning statistics.

Another way of analysing surveys with Excel is to use add-in software which extends Excel's statistical capabilities. There are lots of these add-ins; see details online at www.add-ins.com and www.analyse-it.com/links_excel.htm

After trying these with real data, the top choice seems to be XLStatistics. From Deakin University in Australia, this Excel add-in makes it much easier to analyse survey data. You can download a movie to see how it works, and/or download the software itself.

The next best (because it is expensive and perhaps still slow) is XLStat - see www.xlstat.com. You can download a fully functional trial version.

Not for beginners, but useful if you need to make statistical comparisions of unusual samples, is an add-in called RSE: Resampling Stats in Excel. For further detail, see this page on resampling methods.

Next » Data entry with Excel

 

Did you find this content helpful? If yes, why not help us continue to research, evaluate and provide you with updated content on using Excel to analyse surveys?  You can make a small donation via PayPal to assist our work, click the button at right.