How to create one chat of split bars or small multiple bars in Excel

Excel 365
It is often necessary to visualize the results of voting or polls in which the answers belong to different scales. For example, when collecting data on preferences, such as choosing from the proposed persons, teams (parties); applications, browsers; gadget models; food, colors; etc. See how to organize a team color preferences survey in Outlook.

In some cases, it is important to compare each individual segment column by column and not add up positive (or conditionally positive) and negative (or conditionally negative) results:

Split bars or Small multiple bars in Excel 365

The trick of such charts is to supplement each data segment of a regular 100% stacked bar chart with intermediate data (gaps) so that each data segment plus the gaps make up 100%. This will align all columns of each segment to the left, giving the illusion of separate columns of bar charts.

In Excel, you can easily create small multiple bars or small multiples, also known as split bars, merged bar chart, or faceted bar chart like the one above, by doing the following:

   1.   Modify the chart data

For example, the chart will be created based on the results of a survey on color preferences in the enterprise (see how to organize such a survey in Outlook):

Split bars or Small multiple bars data in Excel 365

   1.1.   Optionally, add anywhere the cell with data for width between columns (in this example, B7 = 5). You can use some constants in formulas instead of the value from this cell.

   1.2.   For each column except the last one, add a new column for gaps (for this example, gap1 - gap5):

Column width in Excel 365

   1.3.   In the first gap column, in the additional row, add the formula to calculate the maximum of all values of the previous column - calculate the width of the first column.

In this example, D8: = MAX (C3:C7).

   1.4.   For all cells of the gap column, add the formula to calculate the difference between the calculated maximum plus the gap width (set in step 1.1. or a constant) and the value of this row - calculate the gap width.

In this example, D3: = $D$8 + $C$8 - C3, D4: = $D$8 + $C$8 - C4, etc.:

Gap value in Excel 365

   1.5.   Repeat steps 1.3.-1.4. for all other gap columns.

In this example:

  • F8: = MAX (E3:E7), ..., L8: = MAX (K3:K7),
  • F3: = F$8 + $C$8 - E3, ..., L3: = L$8 + $C$8 - K3:
Column widths in Excel 365

   2.   Create a stacked chart

   2.1.   Select the data range (in this example, B2:M7).

   2.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 Stacked Bar:

Stacked bar chart in Excel 365

Excel creates a simple stacked bar chart:

Simple stacked bar chart in Excel 365

   2.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 stacked bar chart in Excel 365

   3.   Format the chart

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

   3.2.   Optionally, hide the horizontal axis, remove the legend, type the chart title, or remove it.

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

Format data series in popup menu Excel 365

   3.4.   On the Format Data Series pane, on the Fill & Line tab:

  • For every data series, select the appropriate filling option (see more about filling options in Microsoft).
  • For the gap data series:
    • In the Fill section, select the No fill option,
    • In the Border section, select the No line option:
No fill and No line in Format Data Series Excel 365

   3.5.   Repeat the previous steps to fill the data columns and hide all the gaps columns:

Simple Splitted bars or Small multiple bars in Excel 365

   3.6.   Optionally, on the Format Data Series pane, on the Series Options tab, in the Gap Width field, choose the value you like.

For example, 50%:

Gap Width in data series Excel 365

   4.   Add data labels

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

  • Click on the Chart Elements button, select the Data Labels list, then select the position of the labels:
    Chart Elements, Data Labels, Inside Base in Excel 365
  • 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 Labels in Excel 365

Remove unnecessary labels:

Splitted bars or Small multiple bars with labels in Excel 365

   5.   Optionally, add column headers

   5.1.   Add a new row to the data.

For this example, row 3.

   5.2.   Add new data for headers to every data column (not for gaps) to display the column width:

C3: = D9 + $C$9, E3: = F9 + $C$9, etc.:

Add a new row to the data in Excel 365

   5.3.   Select the chart to see the data range on the spreadsheet, then move the mouse to see the resizing cursor:

Add a new data series in Excel 365

   5.4.   Drag and move the cursor to include data you need to the chart data range:

Add a new data series to the chart in Excel 365

Excel renews the chart data:

New data series for headers in Excel 365

   6.   Optionally, format column headers

   6.1.   To format data labels, right-click on each of them, then select Format Data Label... in the popup menu:

Format Data Label in Excel 365

   6.2.   On the Format Data Label pane, on the Label Options section, in the Label Contains group:

  • Check the Series Name option,
  • Unselect all other options:
Label contains Series Name in Excel 365

   6.3.   Select each data point of headers, then on the Format Data Point pane, on the Fill & Line tab, in the Fill group, select No fill:

Splitted bars or Small multiple bars with headers in Excel 365

Make any other adjustments you prefer.

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.