Excel: How to Create a Diverging Stacked Bar Chart

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:


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


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.

The following stacked bar chart will appear:

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:

Additional Resources

The following tutorials explain how to perform other common tasks in Excel:

How to Show Percentages in Stacked Column Chart in Excel
How to Create a Clustered Stacked Bar Chart in Excel
How to Add Total Values to Stacked Bar Chart in Excel

Leave a Reply

Your email address will not be published. Required fields are marked *