How do I create a diverging stacked bar chart in Excel?

---

Often you may want to create a diverging stacked bar chart in Excel.

This tutorial provides a step-by-step example of how to create the following diverging stacked bar chart:

Let’s jump in!

Step 1: Enter the Data

First, let’s create the following dataset that shows the percentage of people who responded with one of five possible answers to the statement: I would recommend this movie to a friend.

Step 2: Add Helper Columns to the Data

Next, we must create two helper columns that will allow us to eventually create the diverging stacked bar chart.

Insert a new column before the Strongly Disagree column with the following formula in cell B2:

=SUM(C2:D2)

Then insert a new column after the Strongly Agree column with the following formula in cell H2:

=SUM(F2:G2)

Here is what the data will now look like:

Step 3: Insert 100% Stacked Bar Chart

Next, highlight the cell range A1:H6 and then click the Insert tab along the top ribbon, then click the icon titled 100% Stacked Bar within the Charts group.

Step 4: Switch Rows and Columns

Next, right click anywhere on the chart and click Select Data from the dropdown menu.

In the new window that appears, click the button called Switch Row/Column and click OK:

The survey responses will now be displayed along the x-axis and the movie names will be displayed along the y-axis:

Step 5: Delete the Helper Columns

Next, right click on the helper bars in the plot and set their Fill color to No Fill:

Step 6: Customize the Chart Appearance

Lastly, delete the “helper” entries from the legend, delete the x-axis percentage values, add a chart title, and customize the colors to make the chart easier to read: