How to analyze a survey in Excel?
Excel is sufficient for many basic survey analyses, provided that the database has been prepared consistently. Most problems arise not when creating a chart, but before: when coding answers, handling missing data and choosing the right denominator for percentages.
Key information
- ✓One line should correspond to one respondent.
- ✓Create a code dictionary and keep a copy of the raw data.
- ✓Check the numbers along with the percentages.
- ✓Choose a chart based on the type of question, not the visual effect.
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.
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.
Frequently asked questions
Is Excel enough to analyze a bachelor's thesis?
Often yes, especially with descriptive statistics and simple comparisons. Relationship tests or more complex models may require a statistical program.
How to analyze multiple choice questions?
It is best to write each possible answer in a separate column as 1 for a choice and 0 for no choice. The sum of the percentages may then exceed 100%.
What should I send for a survey analysis quote?
Preferably a database of responses, survey form, research questions, program requirements and deadline. On this basis, we can assess the scope of cleaning, calculations and description.
Need support with your work?
Describe the topic, scope and deadline. We will review the materials, recommend the right support and prepare a clear quote.
