Side by side comparison bar chart with the same axes
![Side by side comparison bar chart in Excel for Microsoft 365 Side by side comparison bar chart in Excel 365](/images/tips/838_365/1.png)
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 for Microsoft 365 Side by side comparison chart in Excel 365](/images/tips/837_365/1.png)
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 for Microsoft 365 Side by side comparison bar chart data in Excel 365](/images/tips/838_365/2.png)
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 for Microsoft 365 SEQUENCE formula for chart data in Excel 365](/images/tips/838_365/3.png)
Note: Instead of adding the data to the spreadsheet, you can define a named range with this formula:
![Define name Values in Excel for Microsoft 365 Define name Values in Excel 365](/images/tips/838_365/4.png)
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 for Microsoft 365 HSTACK formula for chart data in Excel 365](/images/tips/838_365/5.png)
Notes:
- Use the VSTACK () function to combine the vertical sequences (in columns instead of rows).
- 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 for Microsoft 365 HSTACK 2 formula for chart data in Excel 365](/images/tips/838_365/6.png)
Note: Instead of adding the data to the spreadsheet, you can define a named range with this formula:
![Define name Errors in Excel for Microsoft 365 Define name Errors in Excel 365](/images/tips/838_365/7.png)
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:
![Edit Series in Excel for Microsoft 365 Edit Series in Excel 365](/images/tips/838_365/9.png)
Excel adds a new data series to the chart:
![Side by side comparison bar with labels in Excel for Microsoft 365 Side by side comparison bar with labels in Excel 365](/images/tips/838_365/10.png)
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 for Microsoft 365 Change Series Chart Type in popup menu Excel 365](/images/tips/charts365/change_series_chart_type.png)
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 for Microsoft 365 Change Chart Type in Excel 365](/images/tips/838_365/11.png)
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):
![The secondary horizontal axis in Excel for Microsoft 365 The secondary horizontal axis in Excel 365](/images/tips/838_365/12.png)
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 for Microsoft 365 Format Axes in popup Excel 365](/images/tips/charts365/format_axis.png)
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 for Microsoft 365 Format Axes - On tick marks in popup Excel 365](/images/tips/charts365/axis-tick_marks.png)
4.4. Delete the secondary horizontal axis:
![Formatted new data series in Excel for Microsoft 365 Formatted new data series in Excel 365](/images/tips/838_365/13.png)
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:
![Format Error Bars in Excel for Microsoft 365 Format Error Bars in Excel 365](/images/tips/206_365/4.png)
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 for Microsoft 365 Custom Error Bars in Excel 365](/images/tips/838_365/14.png)
Excel adds the error bars:
![Error bars for new data series in Excel for Microsoft 365 Error bars for new data series in Excel 365](/images/tips/838_365/15.png)
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:
![Format Data Labels in the popup menu Excel for Microsoft 365 Format Data Labels in the popup menu Excel 365](/images/tips/charts365/format_data_labels.png)
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:
![Side by side comparison bar chart in Excel for Microsoft 365 Side by side comparison bar chart in Excel 365](/images/tips/838_365/16.png)
Make any other adjustments you desire.