How to analyse survey data in Excel

Are you sitting on a goldmine of survey responses but unsure how to gain meaningful insights? If so, Excel is an accessible yet powerful tool that can help.

This guide will show you how to analyse survey data within Excel using its native capabilities, without the need for any add-ins or third-party tools.

In this guide, we’ll show you how to: 

Survey data analysis examines collected feedback to identify patterns, draw conclusions and use the data to drive decisions.

Different types of survey data require different approaches to analysis. Quantitative data (numbers, ratings and scales) can be analysed using statistical methods, whereas qualitative data (open-ended responses, and comments) requires thematic analysis and categorisation.

Standard survey metrics that businesses typically track include:

Related: How to analyse survey data

For example, to export data from SurveyMonkey to import into Excel:

Note: There are additional export options, such as:

When exporting survey data, the analysis requires numerical cells instead of the actual answer text.

Raw survey data seldom comes in a perfectly analysable format, so follow these steps to prepare your data:

Even ratings/scales come in text (e.g. strongly agree, somewhat agree, etc.). You must select ‘numerical value (1-n)’ for responses to have a number instead of text before exporting data. All of this article’s calculations depend entirely on responses being exported as numerical values instead of text.

Excel offers several functions for basic statistical analysis that work perfectly with survey data. Here’s how to use them:

For numerical survey responses (such as ratings or scales), you can calculate the following:

Measures of central tendency:

Response counting:

For example, if you had customer satisfaction ratings in column C, you could quickly calculate the average satisfaction score with =AVERAGE(C2:C100).

Different question formats require different analysis approaches:

Single-choice questions: When analysing questions where respondents select one option, you’ll need to count the frequency of each response. To do this, use COUNTIF and calculate the percentages.

Multiple choice questions: For “select all that apply” questions, each option typically appears in its own column (E, F, G, etc.) with a 1 if selected or 0 if not. To analyse:

Likert scale questions: For questions with rating scales (e.g. 1–5), you can:

Text responses: For open-text responses, Excel offers several approaches:

When you apply these functions to your survey data, you can quickly generate statistical summaries that reveal trends and insights.

Visual representations make survey data easier to understand:

To create any chart:

Create heat maps using conditional formatting. Always include clear labels and sample sizes, and keep visualisations focused on key insights.

Pivot tables are powerful tools for crosstabulation analysis, allowing you to explore relationships between different variables or compare metrics across segments. To create a pivot table:

Use filters and slicers for interactive analysis:

Correlation analysis: Excel’s CORREL function reveals relationships between variables. Results range from -1 to 1. The formula is =CORREL(ARRAY1, ARRAY2), where ARRAY 1 is responses from one question and ARRAY 2 is responses from another question:

1 = a perfect linear relationship, where a unit increase in ARRAY 1 leads to an equal unit increase in ARRAY 2. 

T-tests compare means between groups. Use Excel’s TTEST function to determine whether the differences between groups are statistically significant using the Student’s T-Test technique. For example, you might compare satisfaction scores between male and female respondents. The function needs two ranges of data (one for each group) and parameters for test type and data type.

The chi-square test for independence examines whether or not two categorical variables are independent (i.e. statistically significantly different from each other). This test produces a p-value (probability value) that indicates whether the relationship is statistically significant. A p-value below 0.05, based on a confidence level of 95%, suggests that those two categories are independent and that the difference is not due to chance. Excel offers a built-in chi-square test for users. 

Start with a structured data analysis plan:

You should ensure that you document your approach to ensure consistency.

Look out for bias:

Related: 3 survey bias types to avoid (and why)

Interpretation cautions:

Consider multiple angles:

Enhance your survey analysis by combining it with:

Take advantage of SurveyMonkey integrations to connect your survey data to tools such as:

This integration creates a more complete picture of your customer experience and business performance.

Although Excel is a powerful tool for survey analysis, SurveyMonkey offers built-in analytics that make the process even easier:

Try SurveyMonkey today to collect, analyse and act on feedback more efficiently than ever before. Find out more.

NPS, Net Promoter and Net Promoter Score are registered trademarks of Satmetrix Systems, Inc., Bain & Company and Fred Reichheld.