How to create simple diverging bar chart

Excel 365
Diverging stacked bar charts, also known as centered stacked bar charts, are widely used to display the results of surveys, polls, or questionnaires analyzed through a ranking scale such as a Likert or numeric scale.

They allow comparison of respondents' responses when they are separated by different categories. In a diverging chart, the bars of one category are displayed from right to left (usually, negative values), while the bars of another category are displayed in reverse (usually, positive values):

Diverging bar chart in Excel 365

Opposite sides may show more than just positive or negative values. The divergence point, where the stacked horizontal bar chart aligns, may separate some ranked responses as more important than others.

The following describes the simplest option for constructing a divergent chart, allowing you to maximize Excel's capabilities.

Notes:

  1. See also how to create a bidirectional bar chart in Excel.
  2. If you have only two datasets, you can create a simpler but equally impressive butterfly, mirror, or tornado chart.
  3. If you have some kind of neutral response, see how to create a diverging chart with extra neutral or diverging chart integrated neutral.

For example, the diverging chart will be created for the following survey results:

Diverging bar chart data in Excel 365

To create a diverting bar chart in Excel, do the following:

   1.   Modify the chart data

   1.1.   Modify all the negative (or conditionally negative) values by adding a minus symbol (see how to quickly transform your data without using formulas).

   1.2.   Reordering columns with negative values in reverse order:

Diverging bar chart changed data in Excel 365

   2.   Add new data (optional)

To calculate the percentage of each data point, use the following formula:

Percent = ABS (<cell value>) / SUM (ABS (<values of this product>)),

The ABS (<number>) function returns the absolute value of a <number>.

So, H3: = ABS (E3) / SUM (ABS (C3:G3)), J3: = ABS (D3) / SUM (ABS (C3:G3)), etc.:

Diverging bar chart data for labels in Excel 365

Note: You can leave the actual values - just remove the negative sign.

   3.   Create a diverging chart

   3.1.   Select the data range (in this example, B2:G7).

   3.2.   On the Insert tab, in the Charts group, click the Insert Column or Bar Chart button:

Insert Bar or Column Chart in Excel 365

From the Insert Column or Bar Chart dropdown list, under 2-D Bar, select 100% Stacked Bar:

100% stacked bar chart in Excel 365

Excel creates a simple 100% stacked bar chart:

Simple 100% stacked bar chart in Excel 365

   3.3.   Switch rows and columns by doing one of the following:

  • On the Chart Design tab, in the Data group, click the Switch Row/Column button:
    Switch Row/Column button in Excel 365
  • Right-click on the chart plot area and choose Select Data... in the popup menu:
    Select Data in popup menu Excel 365

    In the Select Data Source dialog box, click the Switch Row/Column button:

    Select Data Source in Excel 365

Excel changes the chart:

Switched 100% stacked bar chart in Excel 365

   4.   Format the axes

   4.1.   To change the order in which data is displayed on a vertical scale, see how to change axis labels order in a bar chart.

   4.2.   To position the vertical axis labels to the minimal position instead of the zero, do the following:

Right-click on the horizontal axis (attention! To position the vertical axis, it should be customized on the Format Horizontal Axis pane), then click the Format Axis... in the popup menu:

Format Axis in popup menu Excel 365

In the Format Axis pane, on the Axis Options tab, in the Axis Options section, in the Vertical axis crosses group, select the Axis value option, then type the minimal value in the appropriate text field.

In this example, -40:

Vertical axis crosses in Excel 365

   5.   Format the chart

   5.1.   Fill the data series as you prefer (see more about filling options in Microsoft).

   5.2.   Optionally, hide the horizontal axis, type the chart title, and remove the legend.

For example:

Formatted 100% stacked bar chart in Excel 365

   5.3.   Optionally, insert logos instead of company names (see how to add pictures to a vertical chart axis):

100% stacked bar chart with pictures in Excel 365

   5.4.   To change the bar width, do the following:

Right-click on any data series and choose Format Data Series... in the popup menu:

Format data series in popup menu Excel 365

On the Format Data Series pane, on the Series Options tab, change the Gap Width setting:

Gap Width in data series Excel 365

   6.   Add data labels

To add data labels to the chart, do one of the following:

  • On the Chart Design tab, in the Chart Layouts group, click the Add Chart Element button:
    Add Chart Element button in Excel 365

    From the Add Chart Element dropdown list, choose Data Labels, then select the place for the labels:

    Add Data Label in Excel 365
  • Click on the Chart Elements button, select the Data Labels list, then select the position of the labels:
    Chart Elements, Data Labels, Center in Excel 365

   7.   Format data labels

   7.1.   To format data labels, right-click on any of them for each data series, then select Format Data Labels... in the popup menu:

Format Data Labels in the popup menu Excel 365

   7.2.   On the Format Data Labels pane, in the Label Options section, under Label Contains:

  • Check the Value From Cells option,
  • Choose the appropriate cells in the Data Label Range dialog box:
    Value from Cells for Label in Excel 365
  • Unselect all other checkboxes.

   7.3.   Position some labels as you prefer or modify their filling options.

For example:

100% stacked bar chart with labels in Excel 365

   8.   Add totals

In many cases, the total number of positive and negative values is more important than the individual values. To add the totals, do the following:

   8.1.   Add a new column to the data with the following formula:

Total (percent) = SUM (<values>) / SUM (ABS (<values of this brand>)),

where, <values> are positive values.

The ABS (<number>) function returns the absolute value of a <number>. The absolute value of a <number> is its value without its sign.

So, D4: = - SUM (ABS (C3:D3)) / SUM (ABS (C3:F3)), F4: = SUM (ABS (E3:F4)) / SUM (ABS (C3:F3)), etc.:

Additional data for totals in Excel 365

Note: You can add labels as sums of corresponding values instead of percentages. You can also add labels for negative values.

   8.2.   Add a new data series to the chart as scatters with totals.

See how to add such data series to the bar chart for more details:

Diverging bar chart with totals in Excel 365

Please, disable AdBlock and reload the page to continue

Today, 30% of our visitors use Ad-Block to block ads.We understand your pain with ads, but without ads, we won't be able to provide you with free content soon. If you need our content for work or study, please support our efforts and disable AdBlock for our site. As you will see, we have a lot of helpful information to share.