Customer feedback is essential to any business. You need to know what you’re doing right and wrong to continue to grow and prosper. 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.
Excel, of course, is regularly used in almost every business and can be a great tool for analyzing customer surveys. But there are more advanced data analysis tools that you can use to gain insights from 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
- How to Analyze Survey Data in Excel
- How to Analyze Qualitative Survey Data
- Create A Survey Results Report
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. This type of data is most common because it’s easy to quantify. The answers to close-ended questions are usually multiple choice, rated on a number scale, or one-word answers, like Yes/No.
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 questions allow businesses to discover information and insights they may have never thought of. Open-ended survey results offer qualitative data that’s harder to analyze because it can’t simply be represented as a numerical value.
Later on, we’ll introduce you to text analysis tools that make it easy to break down open-ended survey results for real quantitative insights. But first, let’s explore how to analyze quantitative data in Excel.
How to Analyze Survey Data in Excel
In order 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 process 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:
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.
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.
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 one, click on any cell within the data set 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.
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).
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 Data 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.
Take a look at an example from MonkeyLearn’s pre-trained sentiment analyzer:
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.
Take a look at how this pre-trained opinion unit extractor, below, separates text into smaller fragments:
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:
Aspect-based sentiment analysis is perfect for open-ended surveys because the answers could offer multiple statements, mentioning different products or aspects of the business, with differing opinions.
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.
To connect your survey data, you have one of three options:
- Upload new responses in an Excel or CSV file to conduct batch analysis
- Use Monkeylearn’s integrations with Google Sheets, Zapier, Zendesk, and more to analyze your texts
- Or if you know a bit of code, try out our API
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 analysis of reviews for Zoom:
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 data in one place for an expansive view or give you finite insights. Get to the data that’s not simply visible on a simple spreadsheet or graph. And, with MonkeyLearn Studio you can manipulate your data and your findings right in the dashboard.
MonkeyLearn Studio offers countless insights and visualizations that can produce game-changing results for presentations, board meetings, and personal edification.
Customer surveys are an undeniably important aspect of business development, and Excel can be a helpful tool to analyze your results, however you choose to conduct your survey.
Using Excel in concert with survey applications and machine learning text analysis tools can take your data to the next level.
Find out how MonkeyLearn can help you analyze all kinds of 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.
Sign up to our Newsletter
Receive awesome Machine Learning posts and tutorials!