Side by side comparison bar chart

Excel 365
To compare two sets of data, you can create various comparison charts, such as a butterfly chart, mirror chart, tornado chart, etc., or a double-sided chart. However, comparing the values in opposite directions is not always convenient. In order to display two datasets, you can use Excel to create a side-by-side bar chart, also known as a double bar chart.

On a double bar chart, two bars are drawn in parallel for each dataset:

Side by side comparison bar chart in Excel 365

Of course, using this approach, you can display multiple datasets in grouped horizontal columns (see how to create small multiple bars). See also the side-by-side comparison chart with differences:

Side by side comparison chart with differences in Excel 365

To create a side-by-side chart in Excel, do the following:

   1.   Prepare the data

For example, a chart must be created for some survey data in several departments of an enterprise:

Side by side comparison bar chart data in Excel 365

   1.1.   Define two new values:

  • Major - grid size for the chart, according to which the axes will need to be aligned (in this example, 10),
  • Gap - minimum size between data columns (in this example, 5).

   1.2.   Calculate the width of the first column using the following formula:

= round up the maximum value of the first dataset plus the defined gap value,

So, Width: = ROUNDUP ((MAX (C3:C6) + gap) / major, 0) * major:

Side by side chart additional data in Excel 365

Note: See more about different rounding effects in Excel.

   1.3.   Add a new column between two data series to calculate the gap between them by the following formula:

= Calculated width in the previous step minus the current dataset value:

Side by side chart additional data 2 in Excel 365

   2.   Create a chart

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

   2.2.   On the Insert tab, in the Charts group, choose 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 bar chart in Excel 365

   3.   Format the vertical axis

   3.1.   Right-click on the vertical axis and choose Format Axis... in the popup menu:

Format Axes in popup Excel 365

   3.2.   On the Format Axis pane, on the Axis Options tab, in the Axis Options section:

  • Under Horizontal axis crosses, select the At maximum category option,
  • At the end of the section, check the Categories in reverse order checkbox:
Format Vertical Axis in Excel 365

   4.   Optionally, format the legend

   4.1.   Do one of the following:

  • On the Chart Design tab, in the Data group, click the Select Data button:
    Select Data 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

   4.2.   In the Select Data Source dialog box, select the added data series (in this example, Empty), then under Legend Entries (Series), click the Edit button:

Select Data Source in Excel 365

   4.3.   In the Edit Series dialog box, in the Series name field, type the empty symbols (in this example, ten spaces - " "), then click OK:

Edit Series in Excel 365

On the chart, the legend displays the empty label instead of the unnecessary one:

Empty legend label in Excel 365

   5.   Format the data series

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

Format data series in popup menu Excel 365

   5.2.   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

   5.3.   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

   5.4.   Type the chart title, remove the unnecessary label from the legend, move the legend, then make any other adjustments you desire:

Formatted data series in Excel 365

   6.   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 End 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 Label in Excel 365

Remove unnecessary labels:

Formatted data series with labels in Excel 365

   7.   Format the horizontal axis

Choose one of the options:

Make any other adjustments you desire.

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.