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.
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.
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.
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.