Side by side comparison bar chart with the same axes

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):

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:

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

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

   1.3.   Calculate the horizontal axis labels:

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

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:

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

   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:
  
• Right-click on the chart plot area and choose Select Data... in the popup menu:
  

   2.2.   In the Select Data Source dialog box:

• Under Legend Entries (Series), click the Add button:
  
• 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:

Excel adds a new data series to the chart:

   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:

   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:

   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:
  

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

  
• Click the Chart Elements button, then in the Chart Elements list, in the Axes list, select the Secondary Horizontal checkbox:
  

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

   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:

   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:

   4.4.   Delete the secondary horizontal axis:

   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...:
  
• Click the Chart Elements button, then in the Chart Elements list, in the Error Bars list, select More Options...:
  

   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:

   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.):

Excel adds the error bars:

   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):
  
• In the Labels section, from the Label Position dropdown list, select None:
  

   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:

   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:
    
  • Unselect all other checkboxes.
• In the Label Position group, select Below:
  

   9.   Format the new data series

For example, hide the middle data series point:

Make any other adjustments you desire.