How to analyze a survey in Excel?

Preparing a safe copy of data

Keep the source file unchanged and perform analysis on a copy. Remove identifying information about respondents if it is not needed and give the columns short, clear names.

One row should contain one person's responses and one column should contain one variable. Merging several questions into one cell makes filtering difficult and leads to errors.

Encoding and cleaning responses

Convert text responses into consistent codes, but keep a dictionary explaining the meaning of each value. Set a separate code for no response and do not automatically treat it as zero.

Check for duplicates, typos, value ranges, and conflicting answers. Before deleting a record, write down the decision criterion so that the procedure can be described in the methodology.

Tables, percentages and comparisons

COUNTIF, pivot tables, and filtering are useful for summarizing closed questions. Always show both the number of responses and the percentage, as percentage alone may hide a very small group.

When comparing groups, first check their size. A difference of a few percentage points may look interesting, but in a small sample it does not have to be important.

Charts and description of results

A bar chart shows categories well, and a Likert scale can be presented in an ordered stacked chart. Avoid 3D effects, multiple colors, and pie charts with a large number of categories.

Start your description with an answer to the question, not with the sentence "as seen in the chart." If the database is unorganized or you don't know what calculations to perform, submit materials for scoping before you start working.

Separate source, working and results sheets

Prepare separate places for the original export, cleaned dataset and tables intended for the thesis. Preserve the original unchanged so you can check the origin of every value. In the working dataset, add a technical record identifier and consistent variable names. The results sheet should use data in a traceable way rather than contain manually copied numbers without explanation. Record important decisions in a short log: a category label was changed, an incorrect number format was corrected or a missing response was marked. Do not overwrite a questionable response with a value that seems more likely. First check the source and the data preparation rules. This workbook structure helps you return to the analysis after your supervisor's comments and explain why the final table differs from the raw export.

Calculate percentages using an explicit example

Teaching example: 40 people completed a survey, but 36 answered the question about attending consultations. Eighteen selected 'yes'. This represents 50% of valid responses and 45% of the entire sample. Both values are mathematically correct, but describe different bases. The table must clearly identify the selected base and the number of missing responses. If you analyse subgroups, the denominator for the percentage in each group is that group's relevant count, not the entire dataset. Do not round intermediate values if you will use them in further calculations. Round at the presentation stage and apply the rule consistently. If displayed percentages add up to slightly more or less than 100% because of rounding, explain this in a note instead of manually changing one result.

Check what the PivotTable is actually counting

Before interpreting a PivotTable, check its source range, active filters and how the field is summarised. For a single-choice question, you usually need the number of responses in each category. The sum of codes such as 1, 2 and 3 is not the number of respondents and usually makes no sense as a result for that question. You can use a complete record identifier for counting. Then compare the table's total with the expected number of people analysed. After changing the dataset, refresh the table and check again that it includes new rows. Do not assume that updating happens automatically without verification. If the result differs from a simple count, check empty cells, numbers stored as text and hidden categories. Establish the cause of the discrepancy before preparing a chart for publication.

Process multiple-response questions and open-ended answers

For a multiple-response question, one person can select several options, so you must distinguish the percentage of respondents from the percentage of all selections. Teaching example: 10 people made 18 selections in total, and 6 people selected a particular option. The result represents 60% of respondents or approximately 33.3% of selections. The choice of base depends on the analytical question and must be described. For open-ended responses, retain the original text and create separate columns for thematic categories. First define the categories, then apply them to every statement. One response may contain several themes if your coding scheme allows this. Do not combine categories simply to make the chart look more balanced. Make it possible to trace a summary back to the statement that supports it.

Transfer results to the chapter without losing meaning

A table in a thesis should have a clear title, units and a description of the calculation base. Remove technical column names from the export, but preserve consistency of meaning with the questionnaire. Arrange chart categories in a logical order, particularly for responses ranging from low to high ratings. Do not change the order between similar questions without a reason. In the text, describe the most important variation and relate it to the research question. A percentage difference alone does not prove causation or statistical significance. If the thesis requires hypothesis testing, extend the analysis plan to suit the data type and test assumptions. Before submission, compare all numbers across the workbook, tables and conclusion. Also save the file version from which the final results were obtained to avoid mixing successive analyses.

Summary

Survey analysis in Excel is reliable when the database has a clear structure, the codes are described, and each table corresponds to a specific question. The chart will not repair incorrectly prepared data. First, check the quality of the database, then the calculations, and only finally the appearance of the presentation.