How to Analyze Open-Ended Survey Responses in Excel

Quantitative survey questions are easy, they give you clean numbers you can chart in minutes. Open-ended survey questions, however, provide something far more valuable but much messier: paragraphs of raw, unfiltered customer feedback. Analyzing hundreds of text-based responses in Excel can feel daunting, but it's where the richest insights are hiding. This tutorial will walk you through a simple, step-by-step process to turn that unstructured text into clear, quantifiable data using the tools you already have in Excel.

What’s the Point of Open-Ended Responses Anyway?

Before jumping into the “how,” it’s helpful to understand the “why.” Multiple-choice or scaled questions (e.g., "Rate your satisfaction from 1 to 5") give you the what. They tell you 30% of users are "dissatisfied." The open-ended follow-up question, "Why did you give that rating?" tells you the why. They’re dissatisfied because of slow shipping, a recent price increase, or a bug in your app.

This qualitative feedback is pure gold for a few reasons:

• It captures the customer’s voice: You get to read the exact words and phrases your customers use to describe their problems, needs, and delights. This is incredibly useful for marketing copy, sales scripts, and product development.
• It reveals the unknown unknowns: You might think your biggest issue is pricing, but the open-ended feedback reveals a massive, unforeseen problem with customer support that your quantitative questions missed entirely.
• It adds context to your numbers: Knowing that user engagement dropped by 15% is one thing. Knowing it dropped because a recent UI change made a key feature harder to find is a specific, actionable insight.

The challenge, of course, is that Excel wasn't primarily built to analyze text. Our goal is to create a structured process to code this text, turning it from qualitative opinions into quantitative data you can measure, track, and act on.

Step 1: Get Your Data Prepped and Organized in Excel

A clean setup is crucial for a smooth analysis. Jumping straight into reviewing responses without organizing them first is a recipe for wasted time. Most survey tools (like SurveyMonkey, Google Forms, Typeform) will let you export your results as a CSV file, which opens perfectly in Excel.

1. Import and Clean Your Data

Start by opening your exported CSV file. The first thing you'll want to do is some light housekeeping:

• Delete irrelevant columns: Your export might include columns like respondent ID, timestamp, IP address, etc. If you don't need them for your analysis, hide or delete them to reduce clutter. Keep at least one unique identifier if you need to reference specific responses later.
• Standardize capitalization: You can use Excel’s LOWER() or PROPER() functions in a new column to make all text consistent. This isn’t strictly necessary but can make scanning responses easier on the eyes. For instance, in a new column, you could use the formula =PROPER(A2) (assuming A2 is your first response) and drag it down.
• Check and correct typos: Give your responses a quick scan. You don’t need to fix every single typo, but correcting common misspellings of your product name, brand, or key features will help later. Use Excel’s Find and Replace feature (Ctrl+H or Cmd+H) for quick fixes.

2. Format as a Table

This is a small step that makes a big difference. Select all your data and format it as a Table (you can find this under the ‘Insert’ tab, or use the shortcut Ctrl+T or Cmd+T). Formatting as a table gives you a few key advantages:

• Easy Sorting and Filtering: Tables come with built-in filter buttons on each column header, allowing you to quickly sort responses or filter for keywords.
• Automatic Range Expansion: If you add new data, the table automatically expands, which is important when you add formulas or reference the table in PivotTables later on.
• Readable Formatting: The alternating colors (banded rows) make the data easier to read.

3. Add Columns for Categories

This is the most important part of the setup. You need to add new, blank columns next to your open-ended response column. This is where you will do your "coding" — assigning categories or themes to each response.

You can start with a single "Category 1" column. As you get deeper, you may find that some responses touch on multiple topics. For example, a response might mention both pricing and customer service. In that case, having several category columns ("Category 1," "Category 2," "Category 3") is a smart move.

Your sheet should now look something like this:

(Original Response) | Category 1 | Category 2 | Category 3

With this clean and structured foundation, you’re ready to start digging into the feedback itself.

Step 2: Coding an Initial Batch of Responses

"Coding" is simply the process of reading a response and assigning it a short, descriptive label or a "code" that summarizes its main theme. The goal is to move from hundreds of unique sentences to a dozen or so core themes. This part is more art than science, and it’s an iterative process.

1. Read a Sample First

Don't try to create a perfect list of categories before you've even read the feedback. Instead, read through about 10-20% of your responses without coding anything yet. Your only goal right now is to get a general sense of the topics people are bringing up. Are you seeing themes around pricing, feature requests, bugs, ease of use, or praise for a new update? Jot down these initial ideas on a separate scratchpad or another sheet.

2. Create a "Working" List of Categories

Based on your initial read-through, create a rough list of categories. It doesn't have to be perfect. Your first pass might look something like this:

• Pricing / Cost
• Bad Customer Support
• Good Customer Support
• Requests for New Feature X
• Login Bug
• Confusing User Interface
• Positive Comment

3. Do Your First Coding Pass

Now, go back to the top of your list of responses and start filling in your new "Category" columns. For each response, pick the category from your working list that fits best. Here are some pointers:

• Be consistent. If you label a response "Pricing," don’t label a similar one "Cost." Stick to your terminology. Using Data Validation in Excel to create a dropdown list for your category columns is a great way to enforce this consistency.
• It’s okay if some don’t fit. If a response is irrelevant ("IDK") or doesn't fit any of your current categories, you can either label it "Other" or leave it blank for now.
• Use multiple categories when needed. A single response might say, "The price is too high, and when I contacted support, they weren't helpful." That response should be tagged with both "Pricing" and "Bad Customer Support" in your separate category columns.

Step 3: Refine Your Categories

After you’ve coded all (or most) of your responses, it's time to refine your categories. Often, your initial list isn’t the best one. You’ll have categories that are too broad, too narrow, or redundant.

1. Combine Similar or Redundant Categories

Look for tags that mean basically the same thing. Did you create categories for "Login Bug," "Payment Error," and "Page Won't Load"? It might be better to group them all under a broader category called "Technical Issues" or "Bugs." Did you have "Cost" and "Price"? Merge them into "Pricing." Use Excel's Find and Replace functionality to quickly update these.

2. Break Down Overly Broad Categories

Did half of your responses end up tagged as "Feature Requests"? That's a good sign that the category is too broad to be useful. Go back through those responses and create more specific sub-categories, like "Feature Request: Reporting," "Feature Request: Integration," or "Feature Request: Mobile App." This extra detail will make your final analysis much more actionable.

3. Aim for a Clean, Final Set of Themes

By the end of this process, you should have a clean, manageable list of 10-15 key themes. This refined list is the foundation for the final step: quantifying your findings.

Step 4: Analyze and Visualize with PivotTables

Now that you've converted your qualitative feedback into structured categories, you can use Excel's most powerful tools to count and visualize them. This is where your hard work pays off.

Using a PivotTable to Count Your Themes (The Best Way)

A PivotTable is by far the easiest and most dynamic way to summarize your coded data. It will automatically count how many times each category appears.

How to create it:

1. Click anywhere inside your data table that you created in the first step.
2. Go to the Insert tab and click PivotTable. Excel should automatically select your entire table. Click OK.
3. A new sheet will open with a blank PivotTable builder. On the right, you'll see a list of your column headers (your "fields").
4. Find your "Category 1" field. Drag it into the "Rows" box. You should see a unique list of all your categories appear.
5. Next, drag that same "Category 1" field into the "Values" box. By default, Excel should set it to "Count of Category 1."

Instantly, you'll have a clean table showing every theme and exactly how many times it was mentioned. It will look something like this:

You can even make it more sophisticated. If you have a separate column with a "Customer Score" (like a 1-5 rating), you can drag that field into the 'Values' box and set it to 'Average' to see the average score for each feedback category. Are customers who complain about pricing giving lower scores on average? A PivotTable can answer that in seconds.

Visualize Your Findings with a PivotChart

Numbers in a table are great, but a chart is often better for communicating insights to others. Directly from your PivotTable, you can create a PivotChart.

How to do it:

1. Click on your completed PivotTable.
2. Find the PivotTable Analyze tab at the top.
3. Click on PivotChart.
4. Choose a chart type that works well for displaying counts, like a Bar Chart or a Pie Chart (though bar charts are generally easier to read for comparisons).

Excel will instantly generate a chart tied to your PivotTable data. Now you have a clear, data-backed visual showing the most common themes in your open-ended feedback. You can easily share this with your team to highlight that "Bugs" are the most cited issue, followed by concerns over "Pricing."

Final Thoughts

Analyzing open-ended survey responses is a rich, rewarding process. By structuring your setup in Excel, systematically coding your responses, and using a PivotTable to count and summarize your results, you can quickly move from an intimidating wall of text to a clear, actionable story about what your customers truly think and need.

Manually performing this process in spreadsheets is powerful, but it can still be time-consuming, especially when you need to continuously report on customer feedback or connect it to data from other platforms (like linking survey data from Google Forms to sales data in Shopify). At Graphed , we built a tool that automates this entire headache. You can directly connect your data sources, and then simply ask in natural language, "Create a dashboard showing a summary of customer feedback in our latest survey, and compare satisfaction scores to last quarter." We handle connecting the data and building the dashboards in real-time, giving you back the hours you'd otherwise spend wrestling with CSV files and PivotTables.