How to Summarize Survey Responses in Excel

The survey closed, you exported the responses, and now you have a sheet with 300 rows and 25 columns staring back at you. Some questions are 1-to-5 ratings, some are multiple choice, a few are open text, and a scatter of cells are blank where people skipped. Before any of it goes in a report, you need the numbers: what's the average rating, how do the groups differ, how many picked each option. To summarize survey responses in Excel by hand, you'd build a pile of formulas and a PivotTable or two. This is the faster version.

It works through the summary in the order a researcher actually needs it, using free macros where they save time and Excel's built-in functions where they're already the right tool.

TL;DR: summarize survey responses in Excel in 4 steps

• Flag the blanks. Highlight skipped answers so you can decide what counts as a complete response.
• Summarize each rating. Get the mean, median, mode, and spread of every 1-to-5 question in one click.
• Split ratings by group. Compare average scores across segments like age band or department.
• Tally the choices. Count how many people picked each multiple-choice option.

With the macros loaded this takes a few minutes. The mean-and-median step alone, done by hand across a dozen rating questions, is where most of the time usually goes.

What a raw survey export looks like

Whatever tool you used, Forms, Qualtrics, SurveyMonkey, the export lands the same way: one row per respondent, one column per question. That's tidy in theory and messy in practice. Rating columns hold numbers, choice columns hold text, and skipped questions leave blanks that quietly break your averages if you treat them as zeros.

So the first job isn't analysis, it's seeing the shape of what you've got. Which respondents finished, which questions people bailed on, and which columns are numeric enough to run stats on. Get that straight and the summary is quick.

Step 1: Flag the incomplete responses

Start by seeing the gaps. Select the response area and run Highlight Blank Cells. It paints every empty or whitespace-only cell red, so skipped questions and partial responses jump out instead of hiding in row 214.

Now you can decide your rule before you calculate anything. Drop respondents who skipped more than half the questions, keep the rest, and note the response rate per question. A blank is a skip, not a zero, and deciding that up front keeps a wall of unanswered questions from dragging every average down.

Step 2: Summarize each rating question

For a 1-to-5 or 1-to-7 rating column, you want more than the average. Select the single column of ratings and run Quick Statistics Summary. It writes a small table two columns to the right with the count, mean, median, mode, sample standard deviation, min, max, and range.

That one table tells the real story. The mean says where the center sits, the median says whether the mean is being pulled by outliers, and the standard deviation says how much people disagreed. Leave two empty columns to the right of your data before you run it, since that's where the table lands.

Step 3: Split the ratings by group

An overall average hides the interesting part. The useful question is usually whether the new hires rated onboarding lower than veterans, or whether one region scored support worse than the rest. Select your data with the header row and run Auto Pivot Summary.

It asks for two column numbers: the group column and a numeric column to total, like 2,6 to group by department and sum a rating. It builds a Summary sheet with each group, the summed rating, and a count of responses, so the average for each group is the sum divided by the count. You get a segment-by-segment comparison without setting up a PivotTable. It needs a numeric column and it overwrites any existing sheet named Summary, so rename an old one first.

Step 4: Tally the multiple-choice answers

Categorical answers need counting, not averaging. For a question like "Which plan do you use?" the built-in COUNTIF function is the clean tool: =COUNTIF(D2:D301,"Basic") counts how many chose Basic. List each option and count it, and you have your distribution.

If you coded responses by hand with fill colors, say you shaded every "would not recommend" answer red, run Count and Sum Cells by Color instead. You pick a sample cell of that color and it counts every cell that matches. One honest limit: it reads the actual fill color, not a color applied by conditional formatting, so it works for cells you filled yourself.

The hot take: report the median, not just the mean

Averaging a 1-to-5 rating is standard, and it's also where a lot of survey write-ups quietly mislead. A mean of 3.4 can mean everyone felt lukewarm, or it can mean half the room loved it and half hated it. Those are opposite findings with the same average.

The median and the standard deviation are what separate them. A median of 4 with a mean of 3.4 tells you a cluster of low scores is dragging the average down, which is a story worth telling. Report the spread, not just the center. That's why the stats macro gives you all of it at once instead of just the average everyone quotes.

Common pitfalls

• Treating blanks as zeros. A skipped 1-to-5 question is missing data, not a score of 0. Averaging it as 0 tanks the result. Flag and exclude blanks first.
• Running the stats macro on the wrong range. It expects a single column of numbers. Select one rating column at a time, not the whole grid.
• Summarizing before you set your inclusion rule. Decide which partial responses to keep before you calculate, or your numbers shift every time you change your mind.
• Expecting color counts from conditional formatting. The color macro reads real fills, not conditional-formatting colors. Fill the cells yourself if you want them counted.

Frequently asked questions

How do I summarize survey responses in Excel?

Work in four passes: highlight blank cells to see skipped answers, run a quick-statistics macro on each rating column for the mean and median, use a pivot-summary macro to compare ratings across groups, and use COUNTIF to tally multiple-choice options. Together they turn a raw export into a clear summary in minutes.

How do I find the average of a survey rating question?

Select the single column of ratings and run the Quick Statistics Summary macro. It returns the count, mean, median, mode, standard deviation, min, max, and range in a small table. The mean gives the center and the median and standard deviation show whether responses were tight or split, which the mean alone hides.

How do I count how many people chose each answer?

Use COUNTIF. For a choice question in column D, =COUNTIF(D2:D301,"Option A") counts how many respondents picked Option A. Repeat for each option to build the distribution. If you color-coded answers by hand instead, the Count Cells by Color macro tallies every cell of a chosen fill.

How do I compare survey results between groups?

Run the Auto Pivot Summary macro. Give it the group column, such as department or age band, and a numeric rating column to total. It builds a summary sheet with the sum and count per group, so each group's average is the sum divided by the count. That surfaces differences an overall average would bury.

Do these macros work on Excel for Mac?

Yes. Every linked macro runs on Excel 2016 and later on both Windows and Mac. The pivot-summary macro avoids Windows-only features, so it behaves the same on either platform, which matters if your lab shares files across both.

What to do next

Get the shape first, then the numbers. Flag the blanks, run the stats on each rating, and you're most of the way to a results section.

To summarize survey responses in Excel end to end, browse the full set of Excel tools for researchers, or start with Quick Statistics Summary on your first rating column. For a deeper stats walkthrough, see how to calculate mean, median, and mode in Excel.