How to adjust the spacing between columns in Excel's dual coordinate axes, and how to create three vertical axes?

Key points of this article

The two groups of columns are squeezed together. This is mainly solved by adjusting "Series Overlap" and "Gap Width", rather than dragging the coordinate axis itself.
If the series overlap is set to a negative value, a gap will be left between the two sets of columns, which is a commonly used spacing setting for biaxial column charts.
The secondary coordinate axis can only be on both sides of the drawing area. The blank space is achieved by adjusting the boundary of the drawing area and the position of the scale labels.
Excel natively only supports the primary and secondary value axes, and the third vertical axis needs to be simulated with a stacked transparent chart.
After finishing, check the units, scale starting points and legend colors axis by axis to avoid misleading readers in dual-axis diagrams.

When making reports, we often encounter situations where the two sets of data have different units, such as sales using ten thousand yuan and growth rate using percentages. At this time, placing one group on the secondary coordinate axis is a direct method. But after the chart is made, the two sets of columns often block each other, and the numbers on the left and right vertical axes are also crowded together, making it look messy. If you want to add a vertical axis to compare with a third unit, Excel will not directly give you the option.

This article is written for readers who need to make a dual-axis chart but are troubled by the column spacing or the third vertical axis. After reading, you will know how to set the distance between the primary and secondary axes of an Excel chart, adjust the positions of the two sets of columns step by step, and master the alternative method of the third vertical axis.

How to set the distance between the primary and secondary coordinate axes in an Excel chart? One sentence conclusion: adjust "series overlap" and "gap width", do not move the coordinate axis

The secondary axis is fixed to the left and right sides of the drawing area, and Excel does not allow you to drag it to any position. The density between columns is controlled by two parameters: the series overlap determines whether the two groups of columns are staggered, and the gap width determines the thickness of the columns and the distance between categories.

your situation	Recommended practices	reason
The two sets of pillars overlap and block each other.	Place one of the groups on the secondary axis, and adjust the series overlap to a negative value, such as -20% or so	Negative values will leave a gap between the two sets of columns, creating a side-by-side effect
Both sets of posts are too thick or too thin	Adjust the gap width of the two groups separately	Gap Width controls the ratio of column width to category spacing
The left and right axis numbers stick to the edge of the chart	Reduce the plot area slightly, or adjust the axis label position	The position of the axis follows the drawing area, and dragging the axis directly usually has no effect.
A group of columns and a group of polylines	The polyline is placed on the secondary axis, and the columns remain on the main axis.	The polyline does not occupy the width of the column, and there is no problem of column overlap. Usually, you only need to adjust the gap width.

Before you start: Confirm these points first

Confirm that two sets of data indeed require two vertical axes. If the units are the same or close in magnitude, sharing a vertical axis makes it easier to read. Dual-axis graphs can easily cause readers to misjudge the multiple relationship between two sets of data, so they should only be used when the units are indeed different.
Confirm that the data area is a regular table. Each column corresponds to a group of series, and the category names are placed in the same column. When the data structure is chaotic, series splitting will go wrong, and subsequent settings will not be possible.
Determine which group to put on the secondary axis. Usually the group with smaller value range or different units is placed on the secondary axis. When readers see the vertical axis on the right, they will realize that this is another unit.
Think about the titles of the two axes in advance. A common problem with dual-axis charts is that both vertical axes have no unit description, and readers cannot tell which axis corresponds to which set of data.

Steps to adjust the column spacing of dual axes in Excel

Select the data area and insert a histogram to obtain two sets of columns side by side.
Click on the set of data series you want to move to the secondary axis, and in the formatting panel that opens, change its axis option to secondary axis.
At this time, the two sets of pillars will overlap in the same category position. Find the "Series Overlap" option in the same panel, drag left to a negative value, such as -20%, and observe whether a gap appears between the two sets of columns.
Adjust "Gap Width". The smaller the value, the thicker the column; the larger the value, the thinner the column. The width of the two sets of columns can be adjusted to visually coordinate.
If the scale numbers on the left and right axes are still squeezed at the edges, select the plot area and drag its borders inward to make room for the scale labels on both sides.
Select the left and right vertical axes respectively, and manually set the minimum and maximum values. The automatic scale ranges of the two sets of data are often different. After unified settings, the comparison of column heights will be more reliable.
Add unit titles to the two vertical axes and match the colors in the legend to the corresponding columns.
Before placing the chart into the document, preview the overall effect in the spreadsheet software to confirm that there is no obstruction or misalignment.

Check each item after completion

Check items	How to see	What if there's a problem?
Whether the two groups of columns are separated	To see if there is a gap between two pillars in the same category.	Increase the negative value of series overlap or decrease the gap width
Do both vertical axes have units?	Look at the title of the left and right axis	Add units to the axis title
Does the vertical axis start from zero?	Look at the small value of the vertical axis of the bar chart.	It is recommended that the column chart be calculated from 0, otherwise the column height ratio will be distorted.
Does the legend color correspond to each other?	Check the column color group by group against the legend	Reset the fill color of the corresponding series
Is the value consistent with the original table?	Spot check figures in three categories	When you go back to the data table and modify it, the chart will be updated; the data source still needs to be checked manually.

# Core comparison: three ways to separate columns

Practice	Effect.	Suitable for the scene
Series overlap is set to negative	The two pillars of the same category are staggered left and right, well spaced apart.	The comparison of two groups of columns side by side is the first choice of biaxial column chart.
Adjust gap width	If you change the thickness of the column, the overall density will change.	When there are fewer categories or the columns are too wide or too narrow
One group is changed to a broken line.	The broken line and the column are displayed in layers and are not obscured by each other.	When you need to compare trends with quantities

# how to do the third vertical axis?

Excel natively supports only the primary and secondary numerical axes, and the third vertical axis cannot be added directly. A common alternative is to simulate with two stacked charts: the first one holds the first two sets of data, and the transparent background of the second one retains only the third set of data and its own right vertical axis.

The specific steps are as follows:

First, do the biaxial diagram of the first two sets of data, and determine the size and location of the drawing area.

two. Copy this chart, paste it and align it with the original image so that the horizontal ranges of the two are the same.

Delete the first two sets of data from the copied chart, keep only the third set of data, and set it as the secondary axis.
Set the background of the chart area and drawing area of the duplicate drawing to no fill, and delete all gridlines so that it appears to have only the new vertical axis on the right.
Fine-tune the drawing area of the copied map so that the category position of the third set of data is aligned with the original image.
The third set of data is recommended to be displayed with broken lines or dotted lines to avoid being visually stacked with the first two sets of columns.
Set the scale range of the new vertical axis and write down the units in the axis title.
After the two pictures are combined, check the alignment item by item. If you later modify the data or chart size, you need to realign it.

This method tests patience. If the relationships of three sets of data do not need to be compared in one graph at the same time, splitting them into three side-by-side small images is usually clearer and easier to maintain.

# frequently asked questions (FAQ)

1. Can the secondary axis be dragged to the middle of the chart?

No, I can't. The secondary coordinate axis is fixed on the left and right sides of the drawing area. To improve the spacing, you can make room on both sides by narrowing the drawing area and adjusting the position of the tick label.

2. Why are the two sets of pillars still next to each other?

It is usually one of two reasons: one is that both sets of data are still on the principal axis, and the other is that the series overlap is not set to a negative value. First confirm that one of the groups has been placed on the secondary axis, and then adjust the overlap parameters.

3. Why does the scale of the two longitudinal axes of the biaxial diagram look wrong?

Excel automatically calculates the scale range for each axis, so even if the values are similar, the scale position may be different. Manually setting the small and large values of the two axes allows the reader to compare them more intuitively.

4. When there are many categories, what if the columns are too crowded?

Reducing the gap width will make the column thinner, but when there are many categories, the effect of simply fine-tuning the column is limited. A better way is to widen the chart, or to split the data into two diagrams and show them separately.

5. How do I put the chart into the document?

You can copy and paste it directly into the document, or you can save it as a picture and insert it. After insertion, enlarge and check whether the scale numbers and legend text are clear, and adjust the chart size if necessary.

# Summary

The Excel chart sets the spacing between the primary and secondary axes, and the core is to adjust the series overlap and gap width, and leave space for the axes on both sides. The third vertical axis has no native options and can be simulated with a stacked transparent chart, but it takes time to align. After the completion of the axis-by-axis check of the unit and the starting point of the scale, the biaxial diagram can accurately convey the data.

Open OmniDoc and process your form file