Take a look at the following survey data we wish to visualize.

We observed that 4 people strongly disagreed. They said the team’s work was not evenly distributed. In contrast, 32 people felt that the work was balanced.

Method 1: Stacked Bar Chart

Our first option to chart Excel survey results is a stacked bar chart. Begin the visualization process, by clicking in the data and select a Stacked Bar chart from the chart library.

The default chart places the answers along the y-axis and the segmented bars represent the questions.

We wish to reverse this arrangement. We can do this by selecting Chart Design (tab) -> Data (group) -> Switch Row/Column.

This is a quick and simple way to visualize the collected survey data.

Although the chart is easy to create, it has its issues.

Method 2: Diverging Stacked Bar Chart

A more elegant approach to chart Excel survey results would be to create a Diverging Stacked Bar Chart

This chart centers the neutral responses. This provides a clearer representation when comparing the categories with one another.

This technique quickly shows which category has the most positive emotional impact. And which one makes them feel the worst.

In order to get the “Neutral” category to be centered in the chart, we need to have the “Disagree” categories represented as negative values. The “Agree” categories will be represented as positive values.

As an additional requirement, we need to have half of the “Neutral” values to be negative and the other half of the values positive. This will allow the center line to “split” the neutral responses right down the middle.

This will require the creation of a data preparation table which will serve as a translator between the original data and the visualization.

Step 1: Creating the Data Preparation Table

To save time on development, we’ll copy the original data from cells B5:G10 and paste it as links in cells B13:G18. Then, we’ll make adjustments in the linked data to chart Excel survey results.

We need the “Strongly Disagree” and “Disagree” values (columns C and D) to be negative. To achieve this, we will place a negative symbol in front of the cell references in cells C14:D18.

Next, we will add an additional “Neutral” category by shifting the data in cells F13:G18 to the right by one column.

Create a link in cell F13 to cell E5 to repeat the “Neutral” title.

The “Neutral” values in column E not only need to be negative but they also need to be divided by 2.

The “Neutral” values in column F need only to be divided by 2.

Step 2: Creating the Diverging Stacked Bar Chart

Select the data in cells B13:H18 and insert a stacked bar chart as done previously in the post.

Excel survey results - diverging stacked bar chart

Just as we did earlier, switch the row and column positions to get the questions to appear on the y-axis.

To better read the chart, we will place the y-axis labels to the far left of the chart. To format the y-axis labels, click one time on one of the y-axis labels and press

CTRL + 1

In the Format Axis panel, under Axis Options -> Labels -> Label Position select “Low” from the dropdown.

Step 3: Checking the Bar Chart Order

When you look at the colors and order of the bar segments, you’ll see “Neutral,” “Agree,” and “Strongly Agree” arranged correctly from left to right.

However, “Neutral,” “Disagree,” and “Strongly Disagree” are also arranged left to right, which is not what we want.

Currently, there are two different colors for the negative and positive “Neutral” segments. We will set both to the same color and place them next to each other to make them look like one segment.

You can fix this by either:

  • Rearranging items in the Select Data Source dialog box
  • Adjusting the data table, which is easier and avoids complicated chart changes.

To adjust the data table:

  1. Copy the formulas in cells C13:C18 and paste them into cells E13:E18.
  2. Copy the formulas in cells E13:E18 and paste them into cells C13:C18.

Now, the negative “Neutral” category (dark blue) will move to the left of the center y-axis, and the “Strongly Disagree” category (gray) will move to the far left.

Step 4: Updating the Bar Chart Colors

To make your stacked bar chart look better, use these colors for the segments:

  • Neutral (both negative and positive): Light gray
  • Agree: Light blue
  • Strongly Agree: Dark blue
  • Disagree: Light orange
  • Strongly Disagree: Dark orange

For a cleaner look, remove the gridlines. Click on any gridline and press Delete on your keyboard.

Step 5: Updating the Legend

To update the legend in your chart, follow these steps:

Move the Legend:

  • Place the legend at the top of the chart.
  • Remove the far-left “Neutral” reference by clicking it twice (two single clicks with a pause between) and pressing Delete on your keyboard.

Fix Legend Order:

The “Disagree” label appears first in the list, but “Strongly Disagree” is first in the chart. Here’s a trick to correct this:

Shift Data:

  • Move the contents of cells F13:H18 to the right by one column.
  • Enter “Disagree” into cell F13.

This empty column won’t supply values but will help fix the legend order.

Add New Series:

  • Highlight cells F13:F18 and press Copy.
  • Click a corner of the chart and press Paste. This adds the new data to the chart without visible changes to the bars but adds “Disagree” to the legend.

Rearrange Legend:

  • Right-click the chart and select Select Data.
  • In the Select Data Source dialog box, find the new “Disagree” entry.
  • Use the “up/down” buttons to move “Disagree” between “Strongly Disagree” and “Neutral”. Click OK.

Match Colors:

  • Click the legend to select it, then click the middle “Disagree” reference to select it.
  • Set the Fill Color to light orange to match the chart segments.

Remove Redundant Reference:

Click the legend to select it, then click the left-most “Disagree” reference.
Press Delete to remove the extra reference.

Now, your Diverging Stacked Bar Chart is complete.

Download the Template

Enhance your learning experience by downloading our workbook. Practice the techniques discussed in real-time and chart Excel survey results with a hands-on example. Download the template here and start applying what you’ve learned directly in Excel.

Excel Download Practice file

Leila Gharani

I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.

My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.