How to Fix Clustered Bars When Using a Secondary Axis in Excel
Question details
The user needs to prevent clustered bars from overlapping when assigning data series to a secondary axis in a chart.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Creating a chart with data series on very different scales that require the use of a secondary axis.
- Observed behavior
- Excel plots the primary and secondary axis column/bar series independently, causing the bars to overlap instead of clustering side-by-side.
Before applying a workaround, organize your dataset into clear columns with headers and identify exactly which data series need to be plotted on the secondary axis due to differences in scale.
Use a Dummy Series Workaround to Force Clustering
By inserting blank data columns, you can trick the charting engine into reserving space, forcing the primary and secondary bars to cluster side-by-side.
Excel handles primary and secondary axes as two separate visual layers that sit on top of each other. To make the columns cluster instead of overlap, you must artificially create empty space in both layers using "dummy" or blank data series.
In your spreadsheet, insert empty columns between your actual data. Add one blank column to represent the gap for the primary axis, and another for the secondary axis gap.
Select the newly expanded data range (including the blank columns) and insert a standard Clustered Column Chart.
Right-click the data series you want on the new scale, select "Format Data Series", and choose "Secondary Axis". Be sure to also assign one of the blank dummy series to the secondary axis.
The invisible blank series will act as placeholders. The visible columns will be shifted, making them appear side-by-side instead of overlapping directly.
Adjust Gap Width for Layered Bars
Modify the thickness of the columns so that the front series is narrower, allowing the back series to remain visible behind it.
Use a Combination Chart
Convert the secondary axis series into a line chart to completely eliminate the problem of overlapping columns.
Create Clear Combination Charts in WPS Spreadsheet
Struggling with complex charts and overlapping columns? WPS Spreadsheet offers an intuitive charting tool to easily create Combination Charts. With just a few clicks, you can display data on a secondary axis using clear, non-overlapping visuals like lines and clustered bars.
- 1. Highlight your dataset: Select the data you want to visualize, including all headers.
- 2. Insert a chart: Go to the "Insert" tab on the top ribbon and click the "Chart" icon.
- 3. Select Combo Chart: Choose the "Combo" chart type and pick the template that places a line on the secondary axis alongside clustered columns.
- 4. Customize appearance: Use the chart elements menu to easily add axis titles, a legend, and data labels to make your varying scales easy to understand.
Frequently Asked Questions
Why do my clustered columns overlap when I add a secondary axis?
When a secondary axis is added, the charting engine plots the new series on a separate visual layer. Because the primary and secondary layers share the exact same horizontal alignment points, the columns are drawn directly on top of each other instead of clustering side-by-side.
Can I have a clustered bar and a stacked bar on the same chart?
Yes, but similar to the secondary axis issue, it requires formatting workarounds. You will need to create dummy series with zero values and arrange your data strategically in your worksheet so that the charting engine leaves appropriate spaces for both the stacked and clustered elements.
What is the easiest alternative to using clustered bars on two axes?
The simplest and most universally understood alternative is a Combination Chart. By keeping the primary data as columns and changing the secondary data to a line chart, you eliminate overlap completely while clearly showing the relationship between vastly different numerical scales.
