How to Analyze Likert Scale Data in Excel

Analyzing survey responses from a Likert scale in Excel doesn’t have to feel like a root canal. If you have a spreadsheet full of "Strongly Disagree" and "Strongly Agree," you can quickly turn that qualitative feedback into clear, insightful charts. This guide will walk you through a simple, step-by-step process for coding, analyzing, and visualizing your Likert scale data in Excel.

First Things First: What is a Likert Scale?

You've seen them a million times. A Likert scale is a survey question format that measures attitudes or opinions on a spectrum. The most common is the 5-point scale:

• Strongly Disagree
• Disagree
• Neutral
• Agree
• Strongly Agree

The words are great for collecting feedback, but they’re not very useful for analysis in a spreadsheet. To work with this data in Excel, you need to convert these text-based answers into numbers. This is the foundation for everything else you'll do.

Step 1: Setting Up and Coding Your Data

Before you can calculate or visualize anything, you need to structure your data correctly and assign numerical values to each response. This process brings consistency to your dataset.

1. Structure Your Raw Data

Organize your data so that each row represents a single respondent and each column represents a different question or demographic information. It should look something like this:

Initial Raw Data:

2. Create a Numerical Key

Decide on a numerical value for each text response. A logical progression is essential. For our 5-point scale, the key will be:

• Strongly Disagree = 1
• Disagree = 2
• Neutral = 3
• Agree = 4
• Strongly Agree = 5

This transforms your ordinal data (data with a clear order) into interval data (data with a defined numerical distance between points), which Excel can easily work with.

3. Convert Text Responses to Numbers

Manually changing every "Agree" to a "4" is a recipe for error and a waste of time. Instead, use Excel’s formulas to do the heavy lifting. The easiest way is with a lookup table and the VLOOKUP function.

First, create your "key" or "lookup table" in a separate area of your sheet or on a new tab:

Next, in a new column next to your first question's data (e.g., column C if your Question 1 data is in column B), enter the VLOOKUP formula. If your lookup key is in cells F2:G6 and your first text response is in B2, the formula would be:

=VLOOKUP(B2, $F$2:$G$6, 2, FALSE)

Let’s break that down:

• B2 is the text response you want to convert.
• $F$2:$G$6 is your lookup table. The dollar signs ($) lock the reference, so it doesn't change when you drag the formula down.
• 2 tells Excel to return the value from the second column of your lookup table (the number).
• FALSE ensures an exact match.

Drag this formula down for all respondents, and then repeat the process for all of your question columns. Your data is now fully quantified and ready for analysis.

Coded Data Ready for Analysis:

Step 2: Summarize Your Findings with Descriptive Statistics

Now that you have numbers, you can start to summarize what they mean. The two most helpful summaries are measures of central tendency (finding the "average" answer) and distribution (seeing the spread of answers).

Finding the Central Tendency

Central tendency helps you understand the typical response to each question.

• Mean (Average): This gives you the overall average score for a question. Use the =AVERAGE() function. For a question in column C, the formula would be =AVERAGE(C2:C101). An average score of 4.1 suggests a strong tendency toward "Agree."
• Median: This is the middle value when all responses are lined up in order. It's less affected by extreme outliers than the mean. Use the =MEDIAN() function. An average and median that are close together indicate a fairly symmetrical distribution of answers.
• Mode: This shows the most frequently chosen answer. Use the =MODE.SNGL() function. A mode of 4 tells you that "Agree" was the single most common response.

Calculate these for each question to get a quick snapshot of the results.

Understanding the Distribution

While averages are useful, they don't tell the whole story. Two questions could have an average of 3.0, but one might have all "Neutral" responses while the other has an equal mix of "Strongly Disagree" and "Strongly Agree." That's a huge difference!

To see this distribution, count the number of times each response (1, 2, 3, 4, 5) was chosen for each question. The =COUNTIF() function is perfect for this.

Create a summary table with your questions as rows and the five possible responses as columns. In the first cell, use a formula like this:

=COUNTIF($C$2:$C$101, F$1)

• $C$2:$C$101 is the range of coded answers for your first question. The dollar signs lock the column as you drag across.
• F$1 is the header of your summary column (e.g., the number 1). The dollar sign locks the row as you drag down.

You can drag this one formula across your entire summary table to populate all the counts quickly. You can also add another column to calculate the total percentage for each response.

Step 3: Visualize Your Data with Charts

Numbers and tables are great, but a well-designed chart tells a story instantly. For Likert scale data, the gold standard is the 100% Stacked Bar Chart.

Why this chart? Because it excels at showing proportions. It makes it easy to compare the distribution of sentiment across multiple questions at a glance. You can immediately see if one question skewed more positive or negative than another.

How to Create a 100% Stacked Bar Chart in Excel

1. Select Your Data: Highlight your summary table that contains the counts or percentages for each response. Make sure to include the question names and the response categories (1-5).
2. Insert the Chart: Go to the Insert tab, click Charts, and select Bar Chart. From the options, choose 100% Stacked Bar.
3. Format for Clarity: An unformatted chart can be misleading. Here are a few essential tweaks:

Pro-Tip: Create a Diverging Stacked Bar Chart

To take your visualization one step further, create a diverging stacked bar chart. This type of chart places the "Neutral" category in the middle, splitting the positive and negative sentiments to either side. It provides an even clearer view of whether the overall sentiment for a question is positive or negative.

This technique requires a bit of clever data prep, but it's well worth it.

1. Split your "Neutral" category (3) counts in half in your summary table.
2. Create two "dummy" data series for your negative responses (1 and 2). Make their values negative by multiplying them by -1, but keep the labels positive. This forces them to appear on the left side of the chart's zero axis.
3. Arrange your table in this order: Strongly Disagree (negative), Disagree (negative), Neutral Part 1, Neutral Part 2, Agree, Strongly Agree.
4. Insert a standard Stacked Bar Chart (not 100%).
5. Format the axis so the negative numbers don't show the minus sign.

The result is a powerful visual that immediately separates agreement from disagreement, anchored around a central neutral point.

Final Thoughts

Analyzing your Likert scale data in Excel comes down to a simple, repeatable workflow: translate words into numbers, summarize those numbers to find averages and distribution, and create a 100% stacked bar chart to visualize the proportions. Following these steps helps you turn a wall of survey responses into a clear, actionable story about what people really think.

While wrangling data and formatting charts in Excel is a valuable skill, it can be time-consuming, especially when managing data across platforms or when you need reports to update automatically. Acknowledging this, we designed Graphed to automate the entire process. Instead of manually coding responses and building charts, you can simply connect your data source (like a Google Sheet where your survey results live) and ask for what you need in plain English — for instance, "Show me a breakdown of customer satisfaction scores from our recent survey." We instantly create live, presentation-ready dashboards, freeing you up to focus on the insights instead of the setup.