How to Analyze Survey Data with Multiple Responses in Excel

Analyzing survey data should be straightforward, but when you have "select all that apply" questions, you often end up with a single Excel cell stuffed with multiple answers separated by commas. This format makes standard analysis nearly impossible. This tutorial will walk you through two powerful methods to clean, count, and analyze multiple-response survey data directly within Excel, transforming your jumbled text into clear, actionable insights.

The Challenge: Why Multiple Responses Break Standard Analysis

Let's say you've run a customer survey and asked, "Which social media platforms have you used to interact with our brand in the last month?" In your spreadsheet, the raw data from your survey tool looks something like this:

You can't just throw this into a PivotTable. Excel sees "Facebook, Instagram" as one unique answer, "Instagram, TikTok, Twitter" as another, and so on. A simple COUNTIF doesn't work either, because it's looking for an exact match. You need a way to tell Excel to count each platform individually, regardless of how it's combined in the cell.

We will cover two effective ways to solve this: the quick and straightforward COUNTIF method for immediate results, and the more robust and automated Power Query method for bigger datasets and ongoing analysis.

Method 1: The Classic Formula Trick (COUNTIF with Wildcards)

This method is perfect for quick, one-off analyses when you don't want to leave the main spreadsheet grid. It relies on a clever use of the COUNTIF function by using wildcards to find a specific word within a cell.

Step 1: Set Up Your Summary Table

First, you need to create a unique list of all the possible answers. Find a blank space in your sheet and list out each potential platform in its own cell. This will form the basis of your summary table.

Let's assume your raw survey data is in column B (from B2 to B11). You can set up your summary list starting in cell D2.

Step 2: Write the Magic Formula

Now, in the cell next to your first item (so, in E2, next to "Facebook"), we'll write our COUNTIF formula. The trick is to wrap the word we're looking for with asterisks (*). The asterisk acts as a "wildcard," telling Excel to count the cell if it contains our text anywhere inside it.

Click on cell E2 and enter the following formula:

=COUNTIF($B$2:$B$11, "*"&D2&"*")

Let's break that down:

• =COUNTIF(...)" : This is our good old counting function.

• $B$2:$B$11 : This is the range where all your raw, comma-separated answers live. We use the dollar signs ($) to make it an absolute reference. This means that if we drag the formula down, this range won't change. It will always look at every response.

• "*"&D2&"*" : This is the key part. It tells Excel to look for the value in cell D2 (which is "Facebook") surrounded by wildcards. So, it will count any cell in our range that contains "Facebook" within it, regardless of what other text comes before or after.

Step 3: Drag and Get Your Counts

Press Enter. You'll see the total count for "Facebook." Now, just click on the small green square at the bottom-right corner of cell E2 and drag it down across the other platforms. Excel will automatically update the D2 part of the formula to D3, D4, and so on, giving you the total for each platform.

Considerations for the COUNTIF Method:

• Pros: It's fast, easy to implement, and doesn't require learning a new tool. It works great for a quick overview.

• Cons: It can be prone to errors. For example, if you were searching for "Facebook," it would also count a cell containing "Facebook Ads," which might not be what you want. It's also not dynamic, if you add new survey responses, you'll need to manually update your formula range ($B$2:$B$11).

Method 2: The Power Query Pro Move (Automated & Scalable)

If you're dealing with a large dataset or want to build a reusable report that can be updated with one click, Power Query is your best friend. It’s a data transformation engine built into modern versions of Excel (from 2016 onwards, it's under the "Data" tab). While it has a slight learning curve, it's a game-changer for repeatable reporting.

Step 1: Load Your Data into The Power Query Editor

First, make sure your raw data is formatted as an Excel Table. Simply click anywhere inside your data and press Ctrl + T (or go to Insert > Table).

With your new table selected, navigate to the Data tab on the Ribbon and click From Table/Range. This will open the Power Query editor in a new window, with your survey data displayed inside.

Step 2: Split the Column by Delimiter (The Magic Step)

This is where Power Query shines. We need to split the single column containing our multiple responses into separate rows for each answer.

1. Select the column with the platforms (e.g., "Social Media Platforms Used").

2. Go to the Home tab in the Power Query editor and click Split Column > By Delimiter.

3. A pop-up window will appear. Excel is smart and will probably have already identified the comma as the delimiter. If not, select it from the dropdown.

4. Here's the most important part: click on Advanced options. Change the split setting from "Columns" to "Rows".

5. Click OK.

Instantly, your data will transform. Instead of one row with "Facebook, Instagram," you will now have two separate rows: one for "Facebook" and one for "Instagram," both associated with the original respondent.

Step 3: Clean and Group Your Data

Sometimes, when you split by a comma, you're left with annoying leading spaces (e.g., " Instagram" instead of "Instagram"). Power Query makes this easy to fix.

• Right-click the column header you just split, go to Transform, and select Trim. This removes any accidental spaces from the beginning or end of your text.

Now, let's count everything up.

• With the same column still selected, go to the Transform tab and click Group By.

• The default settings are exactly what we need. It will group by the platform column and create a new column named "Count" with the count of rows for each item.

• Just click OK.

Step 4: Load It Back to Excel

You now have a clean, aggregated table of your results. Go to the Home tab and click the top half of the Close & Load button. Power Query will close, and your new summary table will be loaded into a fresh worksheet in your Excel file, neatly formatted.

Why Power Query is Worth It:

The best part? This report is now fully automated. If you add 100 more responses to your original raw data table, you don't have to repeat a single step. Just go to your new summary table, right-click, and select Refresh. Power Query will automatically run all those transformation steps and update your counts in a single second.

Bringing Your Data to Life: Visualization

Once you have your aggregated data (either from the COUNTIF method or Power Query), creating a chart is simple. A horizontal bar chart is usually best for displaying this kind of categorical data.

1. Select your summary table, including the headers.

2. Go to the Insert tab and click Recommended Charts. Excel will likely suggest a bar chart right away.

3. Choose a Bar Chart (or a Column Chart if you prefer) and click OK.

As a final tip, sort your summary data from largest to smallest count before creating the chart. This makes your visualization much easier to read, as the most popular responses will be right at the top.

Final Thoughts

Dealing with multiple-response survey answers in Excel moves from a daunting task to a simple process once you have the right tools at your disposal. You can effortlessly turn jumbled, comma-separated data into clean counts and clear, professional visual reports.

Mastering these Excel techniques such as Power Queries and Pivot Tables enhances your data science and engineering skill set. Imagine skipping the raw manual data preparation process altogether and having all your data ready for analysis automatically. We designed Graphed because we believe your time should be spent on understanding insights and taking action based on data, not on cleaning, splitting, or sorting it. Our Graphed tool prepares data and generates insights for you automatically. Instead of manually wrangling data sources like Salesforce, Google Analytics, Shopify, CSVs, and others every week, we connect and integrate them all for you. No need to write formulas, you simply ask your questions in plain English to get instant, automated, real-time dashboards and reports.