How to analyze survey data in Excel

Sitting on a goldmine of survey responses, but unsure how to get meaningful insights? Excel is an accessible yet powerful tool that can help.

This guide will show you how to analyze survey data within Excel, using its native capabilities without requiring 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 analysis approaches. Quantitative data (numbers, ratings, scales) can be analyzed with statistical methods, while qualitative data (open-ended responses, comments) requires thematic analysis and categorization.

Standard survey metrics that businesses typically track include:

Related: How to analyze 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 rarely comes in a perfectly analyzable format. 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 (like ratings or scales), you can calculate:

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 analyzing questions where respondents select one option, you'll want to count the frequency of each response. To do this, use COUNTIF and calculate 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 analyze:

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

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

Applying these functions to your survey data lets you 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, sample sizes, and keep visualizations focused on key insights.

Pivot tables are powerful tools for cross-tabulation analysis, allowing you to explore relationships between different variables or to 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 if 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 test 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:

Be sure to document your approach to ensure consistency.

Watch 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 with tools like:

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

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

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

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