Side by side comparison bar chart with the same axes

Excel 365
To compare two different sets of data, you can create a side-by-side bar chart in Excel, also known as a double chart. It is most informative to compare data in the presence of two identical coordinate grids with the same vertical and horizontal axes:
Side by side comparison bar chart in Excel 365

Of course, you can create two separate charts in Excel, format them similarly, and then position them next to each other. However, with a few additional steps, it is possible to imitate two identical coordinate grids on the same chart. For example, add the two identical coordinate grids to the side-by-side comparison bar chart (see how to create it for more details):

Side by side comparison chart in Excel 365

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

   1.   Prepare a data

   1.1.   Calculate the plot area width as a sum of the first column and the second column widths using the same formula as for the first column (see how to calculate the width of the first column):

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

So, Max: = C7 + ROUNDUP ((MAX (E3:E6) + gap) / major, 0) * major:

Side by side comparison bar chart data in Excel 365

   1.2.   Anywhere on the spreadsheet (or another spreadsheet), add the following formula:

= SEQUENCE (<rows>, [<columns>], [<start>], [<step>])

where:

The SEQUENCE () function generates a list of sequential numbers in an array with a specified number of <rows> and <columns>, starting on the <start> value with specified <step>.

In this example, you need to generate as many zeros as labels should be displayed on the chart:

= SEQUENCE (1, max / major + 1, 0, 0):

SEQUENCE formula for chart data in Excel 365

Note: Instead of adding the data to the spreadsheet, you can define a named range with this formula:

Define name Values in Excel 365

   1.3.   Calculate the horizontal axis labels:

In this example, you need to generate labels for both columns:

= HSTACK ( SEQUENCE (1, width / major, 0, major),
SEQUENCE (1, (max - width) / major + 1, 0, major))

where:

The HSTACK (<array1>, [<array2>], ...) function combines arrays horizontally into a single array.

The first SEQUENCE () function returns the axis labels for the first column.

The second SEQUENCE () function returns the axis labels for the second column.

HSTACK formula for chart data in Excel 365

Notes:

  1. Use the VSTACK () function to combine the vertical sequences (in columns instead of rows).
  2. Unfortunately, labels in Excel don't work with defined names.

   1.4.   Create the new series of data for imitation of the second vertical axis:

In this example, you need to generate labels for both columns:

= HSTACK ( 1, SEQUENCE (1, width / major - 1, 0, 0),
1, SEQUENCE (1, (max - width) / major, 0, 0))
HSTACK 2 formula for chart data in Excel 365

Note: Instead of adding the data to the spreadsheet, you can define a named range with this formula:

Define name Errors in Excel 365

   2.   Add new data series

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

   2.2.   In the Select Data Source dialog box:

  • Under Legend Entries (Series), click the Add button:
    Select Data Source in Excel 365
  • In the Edit Series dialog box, in the Series values field, type the defined names created for values (in this example, H5:O5 or Values), then click OK:
Edit Series in Excel 365

Excel adds a new data series to the chart:

Side by side comparison bar with labels in Excel 365

   3.   Change the new data series type

   3.1.   Right-click on any data series and choose Change Series Chart Type... in the popup menu:

Change Series Chart Type in popup menu Excel 365

   3.2.   In the Change Chart Type dialog box, on the Combo tab, for the new data series:

  • Check the Secondary Axis checkbox,
  • Select the Line chart type:
Change Chart Type in Excel 365

   4.   Format the new data series

To format the newly added data series, add, format, and remove the secondary horizontal axis:

   4.1.   Do one of the following:

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

    From the Add Chart Element dropdown list, select the Axes list and then click Secondary Horizontal:

    Secondary Horizontal axis in Excel 365
  • Click the Chart Elements button, then in the Chart Elements list, in the Axes list, select the Secondary Horizontal checkbox:
    Chart Elements - Add Secondary Horizontal Axis in Excel 365

Excel adds the secondary horizontal axis for the selected data series (on the top of the plot area):

The secondary horizontal axis in Excel 365

   4.2.   Right-click on the secondary horizontal axis (see how to select the invisible chart elements) and choose Format Axis... in the popup menu:

Format Axes in popup Excel 365

   4.3.   On the Format Axis pane, on the Axis Options tab, in the Axis Options section, under Axis position, select the On tick marks option:

Format Axes - On tick marks in popup Excel 365

   4.4.   Delete the secondary horizontal axis:

Formatted new data series in Excel 365

   5.   Add the error bars

To imitate second vertical axes, add and format the error bars for the new data series:

   5.1.   Select the new data series, then do one of the following:

  • On the Chart Design tab, in the Chart Layouts group, click the Add Chart Element drop-down list. From the Add Chart Element list, choose the Error Bars list and then click More Error Bars Options...:
    More Error Bars Options in Excel 365
  • Click the Chart Elements button, then in the Chart Elements list, in the Error Bars list, select More Options...:
    Chart Elements - Add Error Bars in Excel 365

   5.2.   On the Format Error Bars pane for the Vertical Error Bar, on the Error Bar Options tab:

  • In the Direction group, select Plus,
  • In the End Style group, select No Cap,
  • In the Error Amount group, select the Custom option:
Format Error Bars in Excel 365

   5.3.   In the Custom Error Bars dialog box, in the Positive Error Value field, type the specified data range (in this example, H8:O8 or Errors - see step 1.5.):

Custom Error Bars in Excel 365

Excel adds the error bars:

Error bars for new data series in Excel 365

   6.   Format secondary vertical axis

   6.1.   Right-click on the secondary vertical axis (see how to select the invisible chart elements) and choose Format Axis... in the popup menu.

   6.2.   On the Format Axis pane, on the Axis Options tab:

  • In the Axis Options section, under Bounds, type the correct value in the Maximum field (in this example, 1):
    Format Axis bounds in Excel 365
  • In the Labels section, from the Label Position dropdown list, select None:
    Hide Axis labels in Excel 365

   7.   Hide the primary horizontal axis

On the Format Axis pane for the primary horizontal axis, on the Axis Options tab, in the Labels section, from the Label Position dropdown list, select None. See also how to hide the horizontal axis labels for more details.

   8.   Change the new data series labels

   8.1.   Right-click on the new data series labels and choose Format Data Labels... in the popup menu:

Format Data Labels in the popup menu Excel 365

   8.2.   On the Format Data Labels pane, on the Label Options tab, in the Label Options section:

  • In the Label Contains group:
    • Check the Value From Cells option,
    • Choose the appropriate cells (in this example, H6:O6) in the Data Label Range dialog box:
      Value from Cells for Label in Excel 365
    • Unselect all other checkboxes.
  • In the Label Position group, select Below:
    Labels position Below in Excel 365

   9.   Format the new data series

For example, hide the middle data series point:

Side by side comparison bar chart in Excel 365

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.