How to Make a Diverging Stacked Bar Chart in Excel (with Easy Steps)

A stacked chart is an efficient tool for visualizing and comparing data with each other. Diverging chart means that it can spread in both directions. If you are curious to learn about this type of chart, this article may come in handy for you. In this article, we are going to show how you can make a diverging stacked bar chart in Excel with elaborate explanations.

How to Make a Diverging Stacked Bar Chart in Excel: Step-by-Step Procedure

For the demonstration purpose, we are going to use the below dataset. We got the top 5 most popular US mobile network operator and their customer review. We will present them as a form of diverging stacked bar chart to understand the property.

Step 1: Prepare Dataset

Before we delve into creating the stacked char, we first need to collect and organize the information correctly.

• In the beginning, we need to collect the information that is going to be plotted in a stacked bar chart.
• We got the US cellular operators’ customer reviews, categorized from Poor to Excellent Service.
• Customers gave their verdicts, and those were presented in the percentages format.

• For this type of data, you need to use the diverging format of the bar chart.
• In the diverging type of bar chart, you need to set a middle line from where your data will diverge.
• As a neutral review in many cases considered a positive one, we can set all kinds of reviews before the neutral on the one side and others on the other side.
• To do this, we will set the percentages before the Neutral negative.
• For efficient execution, we can use the Paste Special tool.
• Input -1 in any part of the sheet, we input -1 in cell D11.
• Right-click on the cell and from the context menu, click on the Copy.

• Then select the range of cells C5:D9 and right-click on the mouse.
• Then from the context menu, Go to Paste Special > Paste Special.

• Then in the Paste Special dialog box, select Multiply under the Operation group.
• Click OK after this.

• After that, the percentages will now have a negative sign in the range of cells C5:D9.

Read More: How to Make a Simple Bar Graph in Excel

Step 2: Create 2-D Stacked Bar Chart

After we have all the necessary information, we can now create the 2D stacked bar chart. And later we will modify it according to our needs.

• We will create a stacked 2D bar chart from the table we modified earlier.
• Go to Insert tab > Charts group.
• Then click on the Insert Column and Bar Charts, then from the drop-down menu, click on the 2-D bar chart.

• Then you will see a new chart inserted into the sheet.
• The chart though is not in the expected form.
• Hence this chart needs some modifications.

• Now from the Chart Design, click on Select Data.

• Now, in the Select Data Source window, you need to click the Switch Row/Column button.

• After clicking OK, you will notice that the Legend Entries in the previous step are now swapped in the Horizontal Axis Labels. Which is exactly what we wanted.
• Click OK after this.

• After clicking OK, we will notice that the data are now added to the chart.
• And there is an update on the legends also.
• We need to modify the legend for our use.

Read More: How to Make a 100 Percent Stacked Bar Chart in Excel

Step 3: Make Adjustments to Reorder Legends

Although we got the chart, the second chart legend is not in the desired order. We need to fix the order.

• First, we will delete the vertical axis text box.
• and then enter value 0 in the range of cells C5:C9. And place the column header name as Not Up to the Mark.

• We then click on Select Data after right-clicking on the chart.

• And then move the Not up to the Mark to the top of the Legends Entries.

• The newly added Legend now showing in the first position of the chart.
• Next, we will adjust the color.

• To alter the color, right-click on the data series, and from the context menu, click on the Format Data Series.
• Then from the side panel, click on the Color.
• Then choose a color that matches the color of the first entry in the legend list.

• After we match the color, we will delete the original Not Up to the Mark legend entry.
• Click once on the legend to select the Legend.
• And then click again on the Not up to Mark Legend to select it.
• Press the Delete button to delete the legend.

• Now we finally make the diverging stacked bar chart in Excel
• But there are still more things to do for the modification purpose.

Read More: How to Create Clustered Stacked Bar Chart in Excel

Step 4: Finalize Diverging Stacked Bar Chart

We will finally add the text box on the right side of the chart to denote which row denotes which company.

• While selecting the chart, from the Format tab, click on the text box option in the Insert Shapes group.

• After clicking on the Text box icon, draw a box right side of the chart.
• Then input the first entry in the chart.

• Repeat the same process for the rest of the entries
• After this, our chart will look something like this.

Read More: How to Create a Bar Chart in Excel with Multiple Bars

Download Practice Workbook

Download this practice workbook below.

Conclusion

Here make a diverging stacked bar chart in Excel. In this process, we have to make legends reorder, and modify the original data. For this problem, a workbook is available for download where you can practice these methods. Feel free to ask any questions or feedback through the comment section.

Related Articles

• How to Make a Bar Graph in Excel without Numbers
• Excel Chart Bar Width Too Thin
• How to Make a Grouped Bar Chart in Excel
• How to Make a Double Bar Graph in Excel

<< Go Back to Stacked Bar Chart in Excel | Excel Bar Chart | Excel Charts | Learn Excel