Creating a survey (especially with intuitive software) is relatively simple. Distributing it to get as many responses as possible is usually not rocket science either.
Analysis of the data in such a way as to reveal the most insights here is the most challenging part of this process, though.
At first, you feel joy as you observe the rising response rates and see how the “total responses” counter quickly shifts from two to three and on to four digits. But then, you start asking yourself: this is great, but how do I analyze survey data so that it’s useful?
Today, we'll explore some of the most effective ways to untangle the message hidden behind data with tools such as Survicate, Excel, Google Sheets, and Data Studio and analyze their pros and cons.
We’ll also show you how Survicate breaks down survey results data, giving you ready-to-use graphics for any purpose you need, be it marketing assets, stakeholder meetings, or just your own curiosity.
What is survey analysis?
Survey analysis is the process of analyzing customer insights. The goal is to turn numbers into objectives that your teams can use to improve and grow your product, customer experience, customer support, and overall business.
P.S. If you track your Net Promoter Score, it's a good practice to benchmark it against industry averages. You’ll need to know how to calculate NPS and we recommend you check out our NPS calculator to quickly make sense of the numbers and NPS formula.
Once you are done with your Net Promoter Score calculation, you’ll need to see if you keep improving and keep the dialogue with the customers going. Benchmarking your score will let you discover if your NPS score is good in your industry and lets you maintain a competitive edge.
All of your text answers (with demographic data if you enabled integrations!)
A word cloud showing the most popular words in text answers
You can even exclude words to really zoom in on what you want to analyze.
All this info can be exported into .cvs or .xls formats.
How to analyze survey data in Excel
If you’re not an Excel pro, here are a few tips that might motivate you to upgrade your Excel data analysis skills. And believe us when we say this – it’s worth becoming friends with this Microsoft data superstar.
So, without further ado, let’s get right to it!
Once you get your responses from a survey, export the desired file in a .xls or .csv format. Then, open the survey results in Excel. It’s time to format the data to make it more analysis-friendly.
Tip #1: Create blank data rows with a filter
A lot of survey respondents provide answers only to some of your questions. This may result in a survey data file that resembles a checkerboard.
So, how do you delete empty rows and make sure you’re not accidentally deleting a row that only appears to be empty at first glance?
While you’ll find a lot of different ways online, we believe the safest way is to identify blanks consecutively, column after column.
Let’s go through the steps of making this:
Looks more like this:
To start, select Column A and click the “Filter” icon.
A filter will appear on the top row of the first column. Click on it and choose “(Blanks)” from the list.
Repeat these steps for the remaining columns.
Select all the filtered (blank) rows. The numbers will appear in red on the left. Right-click and choose “Delete” > “Delete Sheet Rows”.
Now clear the filters. All that will remain are rows with at least one field filled out. Your data will instantly look better and be more actionable.
Tip #2: Count the number of blanks in a range with COUNTBLANK
You might also want to find out how many empty cells there are per row, column, or entire file. In terms of your survey data analysis, this will tell you how many answers weren’t provided for each question.
COUNTBLANK allows you to quickly define and test a range against empty cells. Let’s assume you want to inspect several rows.
Create a new column at the end of your file. Enter the formula:
In our survey analysis example, this would correspond to cells A2 and D2.
When you hit “Enter”, you’ll see the answer. You can now drag the formula down to as many rows as applicable.
It’s that simple!
Note: If you’re an analytics newbie, your initial instinct might prompt you to delete all blank cells and retain answers only. We advise you not to do this unless you’re certain this won’t hurt your data structure or desired results.
Now that we got this covered, here’s a formula to instantly start making sense of your numbers.
Tip #3: Convert numbers to ranges with VLOOKUP
This will do wonders if you want to segment responses based on numerical values in a selected column. It is also a great first step to deriving data in a way that can be used for more intricate analysis.
Data Scientist Ashutosh Nandeshwar recommends using the VLOOKUP formula to quickly overview what the numbers in your file stand for.
Example: Let’s say you asked your respondents to provide information on the number of employees hired at their organization. Your goal is to understand how many respondents fall into the category of micro, small, medium, and so on companies.
Let’s assume you define the size of businesses according to the ranges below:
0-49 - Micro
50-99 - Small
100-199 - Medium
200-499 - Large
500-999 - Huge
1000+ - Enterprise
Once you’ve defined this, apply and copy the formula with the following syntax to the entire column:
In terms of the table below, this corresponds to:
Use the dollar ($) sign to keep the formula from shifting the numbers.
To make things simpler to understand, column C shows what formulas were inserted in column B.
As you can see, VLOOKUP instantly assigned the responses according to your definition.
The best thing? This is just one of the countless ways to measure various data to derive insights from your audience.
Imagine where VLOOKUP can take you in terms of measuring the number of purchases a user has made, the value of their subscription plan, or the number of sessions they initiated with your software, to name just a few.
PRO TIP: With Survicate, you can go even a step further in grouping your respondents and pass custom respondent attributes from your website or app.
As you can see, two cells have been selected. Now, a numerical value is displayed. But what does it actually mean? If you insert a line chart, all will be graphically revealed:
You can see that the users who have activated the new integration tend to give higher NPS scores.
Pros and cons of survey data analysis with Excel
Hopefully, you just learned a couple of useful formulas to guide you through your future user feedback analysis.
So, is Excel the best solution for you? Let’s sum up the pros and cons.
Almost endless analytics possibilities with advanced use
Widely used; it has kept its hegemony for years and will likely be popular for many years to come
Not an ideal solution if you’re working on data with a team – if you don’t use Office 365, there’s always the risk of overwriting data
Not especially convenient if you want to regularly export and update real-time data (if this is the case, then you’ll like our Google Sheets integration)
Comparing different spreadsheets is impossible without add ons
Working in the cloud is practically impossible
How to analyze survey results in Google Sheets
For the occasional spreadsheets user, Excel and Google Sheets appear to do more or less the same. The fact is, most Google Sheets formulas are either identical or very similar in syntax to the Microsoft software.
For the sake of focusing on tips specific to Google Sheets, here's a cheat sheet with the available functions and their descriptions.
So, without further ado, let’s take a look at how Google Sheets can be used differently from Excel as far as survey data analysis goes.
Let’s connect our Survicate account with Google Sheets. Go to the Connect tab in your panel.
And search for the Google Sheets integration.
You can send previous responses…
Or create an entirely new sheet.
Save data from several surveys to one spreadsheet
Here at Survicate, we are often asked for tips on how you can export your survey responses to a single spreadsheet.
Certainly, while some surveys are best saved to a single file, this doesn’t apply to all use cases. Especially if you run regular surveys of the same type and want instant access to all previous response data – not just your recent export.
You want to store all survey response data for NPS surveys your organization carried out between 2018 and 2022. Ideally, you’d like each new survey response exported to save to a separate worksheet/tab of a single file called “NPS surveys 2018-21”.
With Survicate’s integration with Google Sheets, this is all possible. Just click “View Sheet” in the “Connect tab” to get your up-to-date information.
Saving surveys to a single file is just one of the benefits of our Google Sheets integration. Here’s one that will take your standard analytics charts and tables to a whole new level.
Consider survey data visualization with Google Data Studio
Data Studio (now called Looker Studio) is another unbeatable Google Suite perk that puts basic spreadsheet histograms to shame.
If you’re not familiar with the tool, it turns your .xls/.csv file into real-time graphics reports that transform data into visual, actionable insights.
Let’s imagine you’ve just run an NPS survey and want to visualize it aesthetically for your next stakeholder meeting. You also want to run the data by results from the previous quarter.
After you’ve uploaded the Google Sheets file, you’ll quickly see the magic – you don’t need to move any data into charts manually or create any custom formulas. All is synchronized with your file, in real-time.
Looks great, doesn’t it?
Still, what if you’ve already uploaded the .xls file before, but with survey responses from the previous quarter? How can you upload the newest Worksheet?
It’s very simple:
1. Go to “Data Sources” and select “Google Sheets” from the list.
2. Find the name of your source file on your survey list.
3. Select the new Worksheet you want to update your report with.
4. Hit “Connect” in the upper right corner.
5. Double-check if there’s anything you want to correct in the worksheet before you proceed.
6. Upload the data – say, even simply convert a “number” to a “currency” field.
7. Hit “Add to report” in the upper-right corner.
You can now choose between your older survey responses and the newest worksheet and decide which data sources you want to compare or use for specific tables and charts.
Pros and cons of survey data analysis with Google Sheets
Now that we’ve shown you our two favorite tricks for real-time analysis (hats off, Google), it’s time to summarize the pros and cons before you start analyzing data with Google Sheets.
Google Sheets is free (or reasonably priced for Enterprise accounts)
Can be worked on by multiple users (useful for remote teams)
Data can be sent and updated in a matter of seconds, to both new and previously created files
You can incorporate survey results into your company’s other key metrics such as revenue, registration, or churn rates, and create stunning visuals for all critical data
If you’re a state-of-the-art data analyst, you might find Excel much more powerful; but for basic-to-intermediate survey analysis, Sheets win at versatility (read this comparison for more in-depth details).
How to analyze survey results with Survicate
If you’re already using Survicate to collect feedback and run surveys, why not also use it for analysis? You have all the tools you need right there at your fingertips. Our own results tab can transform most of the raw data into analyzable metrics and statistics.
“Too often, (…) we focus purely on analysis—and the identification of facts—and ignore synthesis, which often occurs organically during analysis.”
As you can see, this relates as much to UX research methodology, as it does to survey response analysis efforts. So, let’s take a look at some tips that might help you organize your work effectively.
Make a list of patterns
Now’s the time to review what your efforts in Excel/Google Spreadsheets have revealed. See any surprising numbers or peculiar correlations of data? It might be worth watching this pattern first.
Write down every single hypothesis that comes to mind
What might seem to be of minor importance now, might reveal itself as part of a much more significant pattern later on. It's better to observe and note it now instead of returning to it from scratch in the future, even if it'll eventually land in a backlog (forever).
It’s like a dental appointment. Your teeth might be perfectly fine now, but your dentist might see a tooth or two that might become troublesome in the future.
Discuss what insights these patterns reveal
Sit down with your team and think about the patterns that keep showing up in your survey results. In most cases, there is a common thread that keeps showing up in survey results with a similar audience.
Is this a recurring trend?
Is it something worth acting upon now, or putting it to the test of time?
What would this piece of data say if we contested it with other notable information?
These are all effective questions to engage your team in processing and synthesizing data. You’ll be surprised to see how far you go with some of your best minds set on the task.
Validate your insights beyond the team
If you’re still hesitant as to whether the results are worth acting upon, extend your search. Ask your team members for their opinion or send follow-up questions to a selected segment of your audience.
Talking to other team members will often reveal a fresh point of view on the matter while asking your users might uncover the missing elements of the puzzle.
In the case of the latter, we recommend taking your time to formulate questions in a way that doesn’t leave any doubt and clearly validates or disallows your hypothesis.
Unresolved? Give it some time
If there’s a result or pattern you don’t think should be dismissed but you can’t find the source of, don’t give up. Write it down and correlate the results with several of your future response results. What isn’t apparent now, might reveal itself in months to come.
Collect and analyze survey results with ease
In this post, we’ve shown you several actionable steps and tools that may prove valuable in your future survey analysis endeavors.
I'm Sabina Fox, a video marketer at Survicate, and I specialize in creating videos and optimizing SEO strategies. I love making videos that connect with people and using creative ideas and data to get great results for our brand.
NET PROMOTER, NPS, AND THE NPS-RELATED EMOTICONS ARE REGISTERED U.S. TRADEMARKS, AND NET PROMOTER SCORE AND NET PROMOTER SYSTEM ARE SERVICE MARKS, OF BAIN & COMPANY, INC., SATMETRIX SYSTEMS, INC. AND FRED REICHHELD.