Analyzing survey data in Excel follows a fixed sequence: export the responses, clean the data, code the answers into numbers, count absolute and relative frequencies, choose the right summary statistic (median, mode, mean), cross-tabulate with a PivotTable, add a chart for each question type and finish with an interpretation linked to your hypotheses. For a typical thesis survey, Excel is enough; you do not need specialist statistical software.
How to get your responses into Excel
If you collected responses online, export them instead of retyping. In Google Forms, open the tab with the responses and either link the form to Google Sheets, from where you download the data as an .xlsx file, or download the responses directly as a CSV. Microsoft Forms, SurveyMonkey and similar tools offer the same export option in their results section. Paper questionnaires need to be entered by hand in the same structure.
Three rules apply to the worksheet you work with:
- One respondent = one row, one question = one column. Never merge answers from several people into a single cell.
- The first row is a header with short variable names (
gender,year,Q1). Keep the full wording of the questions in a separate sheet. - Never overwrite the raw data. Keep it in a separate sheet and make your edits on a copy.
If you are still putting your questionnaire together, this is where you can save yourself work later. Question wording is covered in the guide to creating a thesis questionnaire.
Clean the data before the first calculation
The first calculation from unedited data is almost always wrong. So check four things first.
Duplicate responses. If all the answers and the timestamp match, you are looking at a duplicate. Select the data and use Remove Duplicates on the Data tab. Mind the difference: filtering for unique values only hides duplicates temporarily, while Remove Duplicates deletes them for good, so run it on a copy of the sheet.
Incomplete responses. Set a rule in advance and state it later in the thesis: for example, that you exclude questionnaires with the key questions left blank. The rule must apply to everyone equally.
Nonsense values. An age of 200, a negative number of hours, two mutually exclusive answers. Remove the record or mark the item as missing.
Inconsistent text. In open-ended questions, unify variants of the same answer ("London", "LDN", "london"), otherwise Excel counts three categories instead of one.
After cleaning, write down three numbers you will report in the thesis: how many questionnaires came back, how many you excluded and how many you analyzed. If you end up with a smaller sample than planned, read how many survey respondents you actually need and acknowledge the limitation openly.
Code the answers: turn text into numbers
Coding means assigning a number to each answer option and recording the assignments in a coding table on a separate sheet. Excel can count text as well, but numbers are what unlock calculations and PivotTables.
With scales, always code in the same direction. If 1 = strongly disagree and 5 = strongly agree, that has to hold for every item. Recode reverse-worded statements, otherwise your totals will mix contradictory values.
Leave a missing answer as an empty cell, or give it a separate code that you exclude from the calculations. Zero is the worst choice, because it enters the mean as a real value.
How to handle multiple-choice questions
A "select all that apply" question cannot be coded in a single column. Create a separate column for each option, coded 1 = selected, 0 = not selected. If your survey tool exported all the selections into one cell separated by commas, the Data tab has a Text to Columns tool that splits them.
Calculate percentages from the number of respondents, not from the number of selections, and say so in the table. The percentages will add up to more than 100%, which is correct.
Absolute and relative frequency: the basis of every question
For every closed question you need two numbers. Absolute frequency is the number of respondents who chose a given option. Relative frequency is their share of the valid answers, expressed as a percentage.
You get the absolute frequency with the COUNTIF function, which counts the cells that meet a given criterion, for example =COUNTIF(B$2:B$121,3). For the percentage, divide that count by the number of valid answers: =COUNTIF(B$2:B$121,3)/COUNT(B$2:B$121), and format the cell as a percentage.
Three things reviewers regularly point out:
- Percentages without counts. Always report n as well. "62%" means something different with 21 respondents than with 400.
- Rounding. Round to one decimal place and check the total. If it comes to 99.9%, add a note.
- Over-interpreting small differences. With 40 respondents, one person represents 2.5%, so a difference of two people is not a finding.
Mean, median or mode? Careful with Likert scales
Which statistic makes sense depends on the type of variable. The guide to variable types from UCLA's statistical consulting group explains why: an average of a nominal variable makes no sense, and averaging ordinal data gives questionable results, because the steps between the values are not equally spaced. In practice this translates into a simple rule: mode for nominal variables, median and mode for ordinal ones, and mean, median and mode for metric (interval) variables.
A Likert scale is ordinal. You know the order of the points, but the distances between them are not equal. So report the distribution of answers in percentages plus the median (the MEDIAN function) or the mode, the most frequent value, which you can read off the frequency table or calculate with MODE.SNGL. The mean of a 1 to 5 scale appears in theses all the time and some supervisors ask for it, but on its own it misleads: a value of 3.0 comes out the same for a sample where everyone picked the middle and for a sample split between the agree and disagree poles.
Save the mean (the AVERAGE function) for numeric questions: age, hours per week, years of experience. Never compute it from nominal codes; an average gender of 1.4 means nothing.
A PivotTable step by step
A PivotTable pulls frequencies and percentages out of your sheet without any formula writing.
- Click any cell inside your data so Excel recognizes the range.
- On the Insert tab, choose PivotTable. Microsoft's documentation sums the step up as "Select Insert > PivotTable".
- Check the suggested data range and pick a location. A new worksheet is easier to keep tidy.
- Confirm with OK. The field list opens on the right with four areas: Rows, Columns, Values and Filters.
- Drag the question you are evaluating into the Rows area. Its answer options appear.
- Drag the same question into the Values area as well and set it to Count. That gives you the absolute frequencies.
- In the value field settings, switch the display from count to percentage of the grand total. Now you have the relative frequencies.
- Drag any variable you want to restrict the results by into the Filters area.
Do not rebuild the next question from scratch; it is enough to swap the variable in the Rows area of the field list.
Cross-tabulation: answers by gender or year of study
Cross-tabulation is the point where description turns into analysis. Put the question you are studying into the Rows area and the grouping variable into the Columns area: gender, year of study, mode of study or length of experience. The result is a table that shows the differences between groups.
Compare percentages within each group, not absolute counts. If your sample contains 90 women and 30 men, the absolute numbers in the women's column will always be higher, even if both groups hold the same attitude. In the value display, switch to percentage of the column total.
A cross-table on its own does not prove that a difference is more than chance. If your hypothesis claims that, you need a statistical test. For the test of independence, Excel offers the CHISQ.TEST function, which compares observed and expected frequencies. Check the choice of test with your supervisor; it belongs in the methodology.
Which chart fits which question type
The chart type follows from the question type. The table covers the most common cases.
| Question type | What to calculate | Suitable chart |
|---|---|---|
| Closed, single choice (gender, mode of study) | absolute and relative frequency, mode | pie chart up to 5 categories, otherwise column chart |
| Closed, multiple choice | frequency of each option separately, % of respondents | bar or column chart, never a pie chart |
| Likert scale (agreement 1 to 5) | distribution in %, median, mode | stacked bar or column chart |
| Ranking question (order of importance) | median rank, mode | bar chart |
| Numeric question (age, hours) | mean, median, minimum and maximum | histogram or column chart |
| Open-ended question | categorization into groups, then frequency | bar chart, or just a table |
Microsoft's overview of available chart types backs this up: a pie chart shows the size of items in a single data series in proportion to their sum, so it only makes sense where the values form parts of one whole. A bar chart is recommended when the axis labels are long, which is a frequent case with survey questions.
How to make a chart that survives the review
- State in the title or caption what is on the axis, what the percentages are based on and what n is.
- Skip 3D effects, shadows and color gradients. They reduce readability and credibility.
- If the chart has a single data series, remove the legend.
- Order the categories logically: on a scale from disagreement to agreement, otherwise by size.
- Check the chart in black-and-white print. If two shades merge, change the fill or the pattern.
- Do not place a table and a chart with the same numbers side by side; move one of them to the appendix.
If you are unsure, start from the Recommended Charts on the Insert tab.
How to interpret the results and link them to your hypotheses
A table or a chart is not a result, only the raw material. The result is the sentence that says what the number means for your research question or hypothesis.
Proceed in three steps: state the finding with the number, say what it means for the phenomenon you study, and explicitly connect it to the hypothesis. Instead of "the chart shows that most respondents agree", write which group, in what proportion, and what that does to hypothesis H1: confirmed, not confirmed, or only partially confirmed.
Every hypothesis deserves its own output, and every output should serve a hypothesis. The examination committee will spot charts that have no link to the hypotheses, and a hypothesis without supporting evidence is a frequent objection in reviewers' reports.
The presentation of results and their factual interpretation belong in the empirical part; more on its structure in the guide to the empirical section of a thesis. Comparison with other studies, the limits of your sample and recommendations belong in the discussion. And if you get stuck in the analysis or the deadline is closing in, you can order custom support materials for your survey evaluation, including the tables and charts.
How to label tables and charts correctly
- A table has its number and title above the table, for example "Table 4 Respondents' attitudes to online teaching by year of study".
- A chart or figure has its number and title below the object.
- State the source below the object; for your own survey use a formula such as "Source: author's own research".
- Number the objects continuously through the whole thesis (Table 1, Table 2) or by chapter (Table 3.1). One system, not both.
- Refer to every object in the text. A table the text never mentions looks like filler.
Formal requirements differ between institutions: some want italics, a different placement of the source, or a list of tables. Open your school's guidelines before formatting, not after.
The most common mistakes in survey analysis
- Question by question, no cross-tabulation. You end up with description, not analysis. Compare groups at least for the key questions.
- Averaging nominal codes. A mean answer of 2.3 on a mode-of-study question is nonsense.
- A pie chart for multiple-choice questions. The parts do not form one whole, so the chart lies.
- As many charts as questions. Thirty uninterpreted charts are weaker than eight interpreted ones.
- Percentages without n and without saying what they were calculated from.
- Retyping numbers into the text by hand. That is how the tables and the text end up contradicting each other.
- Interpretation with no link to a hypothesis. A result that answers nothing does not belong in the thesis.
Frequently Asked Questions
Is Excel enough for analyzing a survey, or do I need SPSS? For a bachelor's thesis and most master's theses, Excel is enough. It handles frequencies, percentages, mean, median, mode, PivotTables, charts and the test of independence. Specialist software makes sense for factor analysis or regression, or when your faculty requires it.
How do I calculate percentages when a respondent could select several options? Give each option its own column coded 1 and 0, add up the ones with COUNTIF and divide them by the number of respondents who answered the question. The percentages will sum to more than 100%, so note under the table that respondents could select several options.
Can I use the mean of a Likert scale? A Likert scale is ordinal, so the median with the mode and the distribution of answers in percentages are more defensible. The mean is used in practice and some supervisors ask for it, but never report it on its own. Without the distribution it hides the difference between a sample sitting in the middle and a sample split into two opposite camps.
What should I do with unfinished questionnaires? Set an exclusion rule before the analysis, apply it to everyone equally and report in the thesis how many questionnaires came back, how many you excluded and why. Excluding some responses is not a mistake; concealing it is.
How do I find out in Excel whether a difference between groups is significant? Prepare a cross-table with the observed frequencies, calculate the expected frequencies and use the CHISQ.TEST function, which returns the result of the test of independence. Describe the conditions of the test in your methodology.