The export is open, six hundred rows by twenty two columns, and the first instinct is to select a column and look at the status bar. That works for one question and stops working at the second, because half the columns are multiple answer questions stored as one long string, three are dates that arrived as text, and the percentages about to be calculated are all going to use the wrong denominator.
Excel is a perfectly good tool for this. The failures are specific and repeatable, and most of them happen in the first twenty minutes, before any analysis starts. What follows is the order that avoids them.
Shape the export before touching a formula
Excel analyses one thing well: a rectangle with one row per respondent, one column per question, and a single header row. Survey exports often arrive close to that and not quite there.
Four fixes, in order.
Delete the decoration. Some exports carry a title row, a blank row, or a second header row holding the full question text. Cut the full question text to a separate sheet, because it will be needed later when writing up, and leave one header row of short names.
Give every column a short, unique name. A header of Q7. How satisfied were you with the time taken to respond to your most recent request? is unusable in a formula and unreadable in a PivotTable. Rename it Q7_speed_sat and keep the original wording on the question sheet.
Convert the range to a Table. Select a cell and press Ctrl+T. Formulas then refer to Table1[Q7_speed_sat] rather than to G2:G601, which means adding late responses extends every calculation instead of silently leaving them out. This one step prevents the most common wrong number in survey analysis, which is a total that stopped including new rows two days ago.
Fix the types. CSV exports routinely deliver numbers and dates as text. Numbers stored as text are left aligned by default and will be skipped by AVERAGE and counted oddly by COUNTIF. Select the column, use Text to Columns with no delimiter and the right column format, or VALUE into a helper column. Check the date column at the same time, particularly if the export used a different regional order.
Excel handles 1,048,576 rows by 16,384 columns and a single cell can hold 32,767 characters, so neither response volume nor a long comment is a real constraint. The constraint is shape.
Clean, without deleting anything
Every export contains records that should not be counted, and removing them changes the numbers more than most of the analysis that follows.
Do not delete them. Add a column called keep with 1 or 0, filter on it, and base every formula on the kept rows. The removed records stay available when somebody asks why the report says 574 and the tool says 600.
What to mark out:
- Test submissions made during setup, usually with placeholder text in the first text field.
- Duplicates, common after a reminder. Flag them with
COUNTIFSon the email column and decide once whether to keep the first or the last. - Out of scope respondents: colleagues and internal addresses that came along with a list export.
- Straight liners, where every rating down a long grid is identical, especially where the grid mixes positive and negative statements.
- Speeders, where the completion time is a fraction of the median. If the median is four minutes, a fifty second submission did not involve reading.
- Partials. Decide one rule and apply it everywhere: either analyse each question on the people who answered it, or exclude partial records entirely. Mixing the two produces a report that contradicts itself.
Record the counts removed by reason in a corner of the sheet. That note is the method section of whatever gets written later.
The four calculations that do most of the work
Survey analysis in Excel is mostly counting, and four functions cover the great majority of it.
The base for each question, which is the number of people who answered it, not the number of rows. =COUNTIFS(Table1[keep],1,Table1[Q7_speed_sat],"<>") gives it. Every percentage in the analysis divides by this number, and it changes from question to question because people skip.
Counts per option, with COUNTIFS filtered on keep. Build a small block listing each option down the rows with its count beside it, then the share of the base next to that. A block like this is checkable, which a status bar reading is not.
Cross tabulations, which is where surveys earn their keep, because a total is rarely actionable and a difference between two groups usually is. COUNTIFS takes pairs of criteria, so counting respondents in one region who chose one option is a single formula. Cut by attributes already known from the list, such as tenure, plan or region, rather than by self reported ones where it is possible, and always show both group bases next to the comparison.
Averages, carefully. AVERAGEIFS works and is the function most likely to produce a misleading number. A five point agreement scale is ordered but not evenly spaced, so a mean of 3.8 feels precise and hides whether the distribution is a tight cluster or a split between two camps. If a scale exported as text labels rather than numbers, AVERAGE returns an error, which is an inconvenient way of being told something true.
Two habits prevent most reporting errors from here. Put the base beside every percentage in the sheet itself, so it travels with the number. And below about thirty answers on a question, report counts rather than percentages, because with fourteen respondents one person is seven points.
Charting from the count block, not from the raw column
Build every chart from the small count block rather than from the source column, because the block is where the base, the sort order and the labels already live. Three defaults are worth changing every time: sort the bars by size rather than by the order the options were listed, start the value axis at zero so a three point gap does not look like a chasm, and keep one colour throughout with a second colour only on the bar being pointed at.
For a rating scale, a stacked bar built from five COUNTIFS cells shows the distribution in one row and can be repeated per segment underneath, which makes the comparison readable without averaging anything. Put the base in the chart title, because the chart will be copied into a deck and separated from the sheet that explains it.
Multiple answer questions, which is where most exports get messy
Select all that apply questions arrive in one of two shapes, and the shape decides the method.
One column per option, with a 1 or a value where the option was chosen. This is the easy case: COUNTIFS per column, base is the number of respondents who answered the question at all, and the percentages will add up to more than 100, which is correct and should be labelled on the chart.
One column holding all chosen options, separated by semicolons or commas. This is the common case and the one that produces wrong numbers. The tempting formula is =COUNTIF(Table1[Q9_channels],"*Chat*"), and it is a trap: the wildcard also matches Chatbot, and Email matches Email newsletter. Any option whose name is contained in another option name will be over counted, silently.
Two ways out. The reliable one is to split the column into one column per option first, either with Power Query, which handles the splitting and unpivoting and can be refreshed when more responses arrive, or with Text to Columns followed by a COUNTIF across the resulting block. The quicker one is to wrap the values in delimiters before matching, comparing against ";"&value&";" so that the match is for a whole option rather than a fragment.
Before choosing, check whether the form tool can export the wide shape directly. Many can, and a setting change is cheaper than a formula that has to be right every month.
PivotTables and the three ways they mislead
A PivotTable is the fastest route from a cleaned Table to a cross tabulation, and it supports up to 1,048,576 unique items per field, so size is never the issue. Three behaviours cause most of the wrong numbers.
Blanks are counted as a category. A field with skipped answers produces a blank row that quietly becomes part of the total, so percentages are being calculated against everyone rather than against the people who answered. Filter blanks out explicitly, and decide whether the base should be answerers or respondents rather than letting the default decide.
Percentage of what. Show Values As offers percent of grand total, of row total and of column total, and the three answer different questions. For a cross tabulation of region against satisfaction, percent of row total says what each region thinks, which is almost always the intended question, while percent of grand total says what share of everybody this cell represents, which is rarely useful and looks similar.
Stale data. A PivotTable built on a Table updates when refreshed. A PivotTable built on a fixed range does not include rows added after it was created. Refresh before reading, and check the record count against the count of kept rows in the sheet.
The Analyze Data button will suggest patterns and can be a useful second pair of eyes on a clean table. Treat its output as a prompt to look somewhere, not as a finding, because it has no idea which comparisons are meaningful in this survey.
Where Excel stops being the right place
Excel is strong at counting closed questions and weak at three things that every survey eventually needs.
Open text. Coding free text into themes means reading it, and a spreadsheet gives no way to assign a code, change the definition of that code and have earlier rows update. Keyword formulas across comments produce counts that look rigorous and miss every response that used a different word.
Repetition. A one off analysis is fine in a workbook. A survey that runs monthly turns into a folder of near identical files, and the third version will contain a formula fix that the first two do not, which is how two true numbers end up in the same meeting.
Responses that need a reply. Some answers are not data at all. A complaint with a name on it, a request, an offer to help: those need an owner and a response, and a spreadsheet cell is where they go to be forgotten. Keeping each submission with its own status and owner in the tool that collected it is what stops the analysis and the follow up from being two disconnected exercises, and it is the difference visible in most intake processes worth comparing.
What to change first
Convert the export to a Table, add the keep column, and calculate each question's base as its own cell before writing a single percentage. Those three steps take ten minutes and remove the two errors that show up most often in finished survey reports: percentages divided by the wrong denominator, and totals that stopped counting new responses.
Q1. Why do the percentages for a multiple answer question add up to more than 100?
Because each respondent could choose several options, so the counts are per option while the base is per person. This is correct and should be stated on the chart. Adding the percentages together, or presenting them as shares of a whole, is the error.
Q2. What is the right base for a percentage when people skipped the question?
The number of people who answered that specific question after cleaning, calculated per question rather than taken once from the top of the sheet. Using the total number of respondents everywhere understates every result on questions that were skipped often.
Q3. Why does a COUNTIF with wildcards give the wrong count on a select all question?
Because the wildcard matches fragments. An option named Chat is also found inside Chatbot, and Email inside Email newsletter, so any option whose name sits inside another gets over counted. Split the column into one column per option, or wrap both sides in delimiters before matching.
Q4. Is it acceptable to average a five point rating scale in Excel?
The formula works and the number is fragile, because the gaps between scale points are not known to be equal and a mean hides a split distribution. Reporting the full distribution, or the share choosing the top two points, is harder to argue with as long as the same rule is used throughout.
Q5. How many responses can Excel handle for survey analysis?
Far more than a typical survey produces, at 1,048,576 rows per sheet. Response volume is almost never the limit. What breaks first is the workbook becoming unmanageable across repeated survey rounds and multiple editors, long before any row ceiling is approached.