How to Create a Likert Scale Chart in Excel

Trying to chart survey feedback in Excel can feel surprisingly frustrating. You have clear data - "Strongly Agree," "Disagree," "Neutral" - but turning it into a chart that actually means something at a glance is another story. This guide gets straight to the point, showing you exactly how to build a clear, professional Likert scale chart that makes your survey results easy to understand.

Why Your Standard Excel Chart Fails with Likert Scales

When you have survey results, your first instinct in Excel might be to highlight your data - questions on one side, response counts on the other - and insert a standard bar or pie chart. The problem is, these default charts aren't designed to show sentiment or distribution around a central point, which is the entire purpose of a Likert scale.

For example, a standard stacked bar chart shows the total distribution, but it's hard to quickly compare the balance of positive responses versus negative ones because everything starts from the left.

To really tell the story in your data, you need a chart that clearly separates the negative opinions from the positive ones, with the neutral responses sitting in the middle. This is called a diverging stacked bar chart, and it’s the perfect way to visualize this kind of feedback.

How to Create a Diverging Stacked Bar Chart in Excel

While Excel doesn’t offer a one-click Likert scale chart option, creating one is simple once you know how to structure your data. We are going to build a 100% diverging stacked bar chart, which automatically normalizes your data and makes it easy to compare questions that might have different numbers of total respondents. Let's get started.

Step 1: Summarize and Organize Your Initial Data

First, you need your survey data summarized in a simple table. Your questions should be in the first column, followed by columns for each Likert scale response, in order from most negative to most positive. The cells should contain the raw count for each response.

You also need a final column that calculates the total number of responses for each question using a simple SUM formula. This total is crucial for calculating the percentages in the next step.

Your data table should look something like this:

Step 2: Create a New Table for Chart Calculations

This is the most important step. We need to create a new table that prepares our data for the chart. The goal is to convert the response counts into percentages and split the "Neutral" category so that it creates a central divider in our chart. Negative responses will become negative percentages, pushing them to the left of the zero line.

Create a second table below your original one with the following columns:

• Strongly Disagree (% Negative): Convert the "Strongly Disagree" count to a negative percentage. The formula would be =-(B2/$G2), assuming your "Strongly Disagree" count is in B2 and the "Total Responses" is in G2.
• Disagree (% Negative): Do the same for the "Disagree" category. Formula: =-(C2/$G2).
• Neutral - Left (%): This splits the Neutral group. Take half of the "Neutral" percentage. Formula: =(D2/$G2)/2.
• Neutral - Right (%): And this is the other half. Formula: =(D2/$G2)/2.
• Agree (%): Convert the "Agree" count to a regular percentage. Formula: =(E2/$G2).
• Strongly Agree (%): Same for "Strongly Agree." Formula: =(F2/$G2).

After applying these formulas, your calculation table should look like this:

Step 3: Insert the 100% Stacked Bar Chart

Now it's time to build the chart itself.

1. Select the data from your new calculation table. Be sure to include the question labels in column A and the new column headers.
2. Go to the Insert tab on the ribbon.
3. Click Insert Column or Bar Chart.
4. Select the 100% Stacked Bar chart type.

Excel will instantly generate a chart. It won't look right just yet, but that's what the next steps are for.

Step 4: Format Your Chart to Perfection

This is where we clean things up and create the diverging effect.

1. Reverse the Question Order

By default, Excel often puts the last question at the top of the chart. To fix this, right-click on the vertical axis (where the question labels are) and select Format Axis. In the Axis Options pane that appears, check the box for Categories in reverse order.

2. Color-Code Your Data Series

Make your chart readable by applying intuitive colors. We want a "stoplight" color scheme: red for negative, grey/yellow for neutral, and green/blue for positive.

Right-click on each bar segment in the chart one by one and select Fill to change its color.

• Strongly Disagree: Dark Red
• Disagree: Lighter Red or Orange
• Neutral (both series): Grey
• Agree: Light green or blue
• Strongly Agree: Dark green or blue

3. Clean Up the Horizontal Axis

The horizontal percentage axis currently shows negative values, which we don't want. We need to format the labels to appear as absolute percentages on both sides of the zero point.

1. Right-click the horizontal axis and select Format Axis.
2. In the Format Axis pane, expand the Number section.
3. Change the Category dropdown to Custom.
4. In the Format Code box, enter the following code and click Add:

0%,0%,0%

This little trick tells Excel to display negative numbers as if they were positive, giving you a clean 0% to 100% scale running in both directions from the center.

1. Next, expand the Axis Options tab. Set the Minimum Bound to -1.0 and the Maximum Bound to 1.0. This ensures your chart covers the full range from -100% to 100%, keeping your neutral bars perfectly centered.

4. Fix the Legend

Your legend probably has "Neutral - Left" and "Neutral - Right," which is not very reader-friendly. To fix this:

1. Right-click on the chart and choose Select Data.
2. In the dialog box, click on a series name in the left-hand box (e.g., "Neutral - Left (%)") and then click the Edit button.
3. In the "Series name" field, you can either type a new name (like "Neutral") or link to a cell on your spreadsheet that has the correct text.
4. Since you can't have duplicate series names, simply name the first one "Neutral" and the second one "Neutral " (with a space at the end). Excel will view them as unique, but they will appear almost identical in your legend.
5. Once you're done, you can delete the extraneous "Neutral" entry from the visible legend.

Step 5: Add Finishing Touches

Finally, give your chart a descriptive title, like "Customer Satisfaction Survey Results." You might also consider sorting your original data table. By sorting your questions based on the sum of "Agree" and "Strongly Agree" responses (from highest to lowest), your chart will tell an even clearer story by highlighting the areas of strongest agreement at the top.

Final Thoughts

While Excel doesn’t offer a built-in function to create a Likert scale chart, using the 100% stacked bar method transforms convoluted survey data into a clear and powerful visual. This technique perfectly separates positive, neutral, and negative feedback, enabling you and your team to see the big picture in a single glance.

If you're tired of constantly wrangling CSVs and wrestling with Excel formulas to visualize data, you might appreciate the way we've tackled this at Graphed. We automate the whole process by connecting directly to your data sources - be it survey tools, your CRM, or your ad platforms. You can create insightful dashboards, including complex visualizations like this one, just by describing what you want to see in plain English. No more formulas, and no more manual updates.