How to Analyze Survey Data in Excel & Beyond

Customer surveys can be one of the quickest and, if done right, one of the most dependable ways to get useful information about your brand, your products, and individual product features, at every step of the customer journey.

Once you’ve collected your survey data, though, you’ll need to analyze it.

The type of questions you ask and how many responses you receive will shape the way you perform survey analysis.

Excel, of course, is regularly used in almost every business and can be a great tool for analyzing quantitative customer surveys. But there are more advanced data analysis tools that you can use to gain more in-depth insights from the qualitative data in your surveys.

In this post, we’ll show you how easy it is to perform survey data analysis on both qualitative and quantitative data, in Excel and beyond.

Types of Survey Data

There are two basic data types in surveys: quantitative and qualitative

Quantitative data is found in responses to closed-ended questions. You’d perform statistical data analysis of this survey data type since it’s quantifiable. The answers to close-ended questions are usually multiple-choice, rated on a number scale, or one-word answers, like Yes/No.

The customer satisfaction (CSAT) survey is the most widely used questionnaire for requesting customer feedback, and can quickly help companies measure how happy their customers are with their product, service, event, or brand. The NPS score is by far the most well known CSAT score and buckets customers into three groups based on their responses.

Qualitative data is found in responses to open-ended questions. Open-ended questions elicit longer, more objective responses. They are looking for an individual’s opinion or feelings.

Open-ended survey results offer qualitative data that’s harder to analyze because it can’t simply be represented as a numerical value.

Most surveys, like NPS surveys, often ask a follow-up question to understand why customers leave a high or low score. These open-ended answers offer more detailed insights and can help companies really understand what they’re doing right or wrong.

Table showing the differences between qualitative and quantitative data

Later on, we’ll introduce you to survey analysis tools that make it easy to break down open-ended survey results for real qualitative insights. But first, let’s explore how to analyze survey data in Excel.

How to Analyze Survey Data in Excel

To get the most out of any survey, you need to decide what you’re looking for. What are your goals? What are the insights you want to gather? Start with the end result in mind.

You can ask customers to rate the quality of your product or find out how likely they would be to use your services again. You can ask them to compare your brand to your competitors. Or get more detailed results with open-ended questions to discover reactions and emotions that can’t easily be understood with Yes/No questions.

Whether quantitative or qualitative, Excel can be a useful tool to help collect your survey data. 

Analyzing quantitative survey data in Excel can be a snap with built-in formulas, tables, and charts. The below examples show the results of over 2,500 survey responses for the messaging app, Slack. Reviewers ranked the app from 1 to 5 stars on general use (“Stars” column), value for money, ease of use, features, and customer support. A snapshot of the overall data in Excel:

Data from Slack reviews organized into an Excel table.

Filter survey data by different criteria

The below shows reviews filtered by survey respondents that delivered a rating of 3 stars or lower. To do this, click the left-hand corner to select the entire table, and choose ‘turn on filter’ by selecting the filter icon. Then, choose a conditional value of 3 or lower, and rename this view to ‘detractors’, survey respondents that give you a below-average rating.

Slack reviews in Excel s filtered by those that ranked 3 stars or lower.

Calculate mean, maximum, and minimum

The columns, rows, and cells in Excel have built-in formulas. In this case, simply highlight the entire column (or group of columns), and choose the correct formula in Excel. Type ‘=average’, ‘=max’, or ‘=min’ in the corresponding field and Excel will calculate the statistics for you.

Mean, maximum, and minimum calculations for Slack reviews in Excel.

Perform cross-tabulation with a pivot table

A pivot table is a new (pivoted) table that summarizes the data of a more comprehensive table. Also referred to as cross-tabulation, it can provide a quick comparison of how different groups of respondents answered your survey questions, for example, you could focus on respondents in a particular age group, with a specific occupation, and more.

To create a pivot table, select the cells you want to use, click ‘Insert’from the menu bar, select ‘PivotTable,’ then choose the location for your pivot table. Drag the fields you want to use into the ‘PivotTable Fields’ pane that pops up.

In this table, we can see a summary of the column "Stars." It counts the number of times each star appears in a review.

An Excel pivot table of Slack review data.

Create charts and graphs to visualize data

Simply choose the chart or graph you’d like to use from the ‘Insert’ menu, and Excel will walk you through choosing your fields. This graph shows the percentage value by star for each category. We can see that the results are quite positive, with more than 50% of the reviews being 5 stars (in all categories). 

Graph showing percentage results of star ratings for each category of Slack ratings.

How to Analyze Qualitative Survey Data

Qualitative data can be harder to analyze because it can’t simply be turned into numerical values to run through Excel formulas. 

Now, imagine if your online survey response rates are higher than expected, and you need to quickly transform your data into insights. With machine learning tools, you can transform open-ended survey responses into real, actionable insights in just a few steps, and in next to no time at all.

Survey Analysis Tools

MonkeyLearn is a SaaS platform with powerful text analysis tools that can automatically organize surveys, read responses for sentiment and emotion, extract the most important words, and more. MonkeyLearn offers no-code solutions to build your own machine learning models and analyze huge amounts of data in real time. 

You can simply upload an Excel file with long-form survey responses and perform any number of text analyses, then output the results back into Excel. Or upload and download directly from survey tools you already use, like SurveyMonkey, Survey Gizmo, Google Forms, and more.

Sentiment analysis is a cornerstone of text analysis that uses natural language processing (NLP) to understand text (our survey responses) much like a human would, then rate each response as positive, negative, or neutral. 

Sentiment analysis goes beyond mere word definitions into opinion and emotion, even sarcasm. And it can be used on social media, customer service tickets, regular documents, all manner of text.

Try our this MonkeyLearn’s pre-trained sentiment analyzer:

Test with your own text

Results

TagConfidence
Positive100.0%

Powerful algorithms break the comment down and easily score the sentiment as Positive.

Some open-ended survey responses, however, may contain more than one opinion. To gain a more accurate analysis, these have to be separated into individual opinion units (fragments of text), otherwise, responses will be categorized as ‘neutral’ since opposing sentiments in one text will cancel each other out. 

Try out how this pre-trained opinion unit extractor, below, separates text into smaller fragments: 

Test with your own text

Results

TagValue
OPINIONThe hotel has a great location
OPINIONbut all in all it was a horrible experience!
OPINIONOnly stayed here because it was the pre-accommodation choice for one of our tours
OPINIONWill never stay here again!

Once you have separate opinion units, you can get even more granular with your data analysis

Aspect-based sentiment analysis, for example, will break the text down into aspects (categories), and then assign a sentiment to each piece of text. 

If we’re surveying software customers, we could use categories, like Usability, Functionality, and Support, to sort texts.

So, we would end up with aspect-based sentiment analysis in Excel that looks like this:

Opinion units organized in an excel spreadsheet

Aspect-based sentiment analysis is perfect for analyzing your open-ended questions because customers will have different opinions about different products or aspects of the business.

Getting started with qualitative data analysis is easy. You can even train your own models, specific to your business, and your criteria in just a few minutes.

Just sign up to MonkeyLearn, then watch this tutorial to learn how to create your own survey analyzer.

To connect your survey data, you have one of three options:

Create A Survey Results Report

Bring your data together and uncover even more insights with MonkeyLearn Studio – your all-in-one survey analysis and data visualization tool.

The below is a sample dashboard showing an analysis of reviews for Zoom:

The MonkeyLearn Studio dashboard showing multiple text analysis results together.

You can see sentiment analysis by category (aspect), individual responses by date, and keyword extraction that shows the most used and most important words: in the ‘Qualifier’ section on the bottom left and in the word clouds created on the bottom right.

Data visualization tools can put all of your survey data into one place for an expansive view or finite insights. Get to the data that’s not simply visible on a simple spreadsheet or graph.

With MonkeyLearn Studio you can manipulate your survey data and your findings right in the data visualization dashboard.

MonkeyLearn Studio offers countless insights and visualizations that can produce game-changing results for presentations, board meetings, and personal edification.

Get Started with Survey Data Analysis

Customer surveys are an undeniably important aspect of business development, and Excel can be a helpful tool to analyze your survey results, however, you choose to conduct your survey.

Using Excel in concert with survey applications and AI survey analysis tools can take your data to the next level.

Find out how MonkeyLearn can help you analyze your surveys and improve your analytics by showcasing your data in broad strokes or finite detail.

You can try out most of the powerful text analysis tools for free to see how they work. Or schedule a demo to understand the true potential of machine learning.

Tobias Geisler Mesevage

September 4th, 2020