'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Create a Side-by-Side Horizontal Bar Chart with Two X-Axes in Excel
Question details
The user needs to create an unstacked side-by-side horizontal bar chart with two independent horizontal X-axes in Excel.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Visualizing two sets of data with different numeric scales side-by-side in a horizontal bar format for accurate comparison.
- Observed behavior
- Excel does not offer a native, single-click chart type for side-by-side horizontal bars with two independent X-axes, requiring manual adjustments of secondary axes and series overlap.
Ensure your data is organized into clear, separate columns for categories and the two distinct numeric series you plan to plot. It is highly recommended to sketch out your expected result or create a dummy dataset to test the axis scales before applying them to your primary spreadsheet.
Create Using a Secondary Axis and Overlap Adjustment
Manually configure a 2D Bar Chart by enabling a secondary axis and adjusting series overlap to force bars side-by-side.
Because Excel naturally overlaps bars when a secondary axis is added, you must manually adjust the series overlap and gap width to place them side-by-side.
Select your data (categories and both data series), go to the Insert tab, and click the 'Insert Column or Bar Chart' icon. Choose the 2D Clustered Bar option.
Right-click one of the data series bars in the chart and select 'Format Data Series'. In the pane that appears, under 'Series Options', check the box for 'Secondary Axis'.
With the chart selected, click the '+' (Chart Elements) button. Go to Axes, click the arrow next to it, and check 'Secondary Horizontal' to display the second X-axis at the top of the chart.
In the 'Format Data Series' pane for each series, adjust the 'Series Overlap' and 'Gap Width' sliders. You may need to introduce a blank 'dummy' series in your data to help offset the bars so they sit next to each other rather than directly on top.
Click on the primary and secondary horizontal axes to format them. Adjust the 'Minimum' and 'Maximum' Bounds under Axis Options so that both datasets visually compare as intended.
Easily Create Advanced Charts with WPS Spreadsheets
WPS Spreadsheets provides an intuitive chart editing interface, making it simple to manage secondary axes, series overlap, and complex data visualizations. It is highly compatible with Microsoft Excel file formats.
- 1. Open Data in WPS: Launch WPS Spreadsheets and open your workbook containing the data series.
- 2. Insert Bar Chart: Highlight your data, navigate to the Insert tab, and choose a 2D Clustered Bar Chart.
- 3. Format Secondary Axis: Double-click the series you want on the new axis. In the right-hand properties panel, select 'Secondary Axis'. Go to Chart Elements to toggle the secondary horizontal axis display.
- 4. Adjust Overlap: Use the Series Options panel to change Series Overlap and Gap Width to position your bars perfectly side-by-side.
Frequently Asked Questions
Why do my bars overlap completely when I add a secondary axis?
When you assign a series to a secondary axis, the spreadsheet plots it on a completely separate visual layer over the primary axis. To fix this, you must adjust the Series Overlap settings or insert a blank data column to force physical spacing.
Can I align the zero points of two different X-axes?
Yes, you can align the zero points by manually setting the Minimum and Maximum bounds for both the primary and secondary X-axes in the Format Axis properties pane to perfectly proportional scales.
Is there a built-in template for a two X-axis bar chart?
No, most spreadsheet software does not feature a single-click template for this specific axis combination. You must build it using a standard clustered bar chart, manually adding a secondary axis, and tweaking the series overlap.
