To align zero on an Excel chart’s secondary axis with zero on its primary axis, set fixed minimum and maximum bounds for both vertical axes so zero occupies the same fraction of the plot height. In Excel for Microsoft 365 on Windows, right-click each vertical axis, choose Format Axis, and enter bounds that give both scales the same zero position; Excel has no automatic “align zero” command.
What zero alignment means
A chart with a secondary vertical axis uses two independent scales, usually shown on the left and right. Excel scales each axis to suit its own data. As a result, the horizontal level that represents zero on one scale may not match the level that represents zero on the other. A bar or line that appears to cross zero can therefore look out of alignment with the other series, even though both data values are zero.
Aligning zero means making the zero tick marks fall at the same vertical position in the plot area. It does not mean making the two axes use the same minimum, maximum, interval, or units. For example, one axis might run from -50 to 150 and the other from -30 to 90. Those ranges differ, but they place zero at the same height because zero is one-quarter of the way up each scale from its minimum.
This is useful when a chart compares measures with different units, such as revenue and a percentage rate, or actual results and a variance. If one series includes negative values, a shared zero level can make positive and negative values easier to compare visually. It is a visual scaling choice, however, not a calculation that changes the source data.
How Excel positions zero
For a vertical value axis, Excel distributes the chart’s plot height across the interval from its minimum to its maximum. The zero position is determined by the distance from the minimum to zero compared with the full range. In plain terms, calculate the distance from the minimum to zero, then divide it by the distance from the minimum to the maximum. The result is the fraction of the plot height above the bottom where zero appears.
For example, with bounds of -50 and 150, the full range is 200 and zero is 50 units above the minimum. That puts zero 25% of the way up from the bottom. To make another axis line up, choose its bounds so zero is also 25% of the way up. You can use different bounds and still get the same zero position.
Both axes must include zero for this method to produce a zero line on each scale. If an axis has a minimum above zero or a maximum below zero, zero is outside its displayed range and cannot align visibly with zero on the other axis.
Set bounds in Excel for Microsoft 365 on Windows
First make sure the chart has a secondary vertical axis. Select the chart, then use Chart Design > Change Chart Type if you need to make a series a combo chart or assign it to a secondary axis. The exact chart types available depend on the data and chart. Once the secondary axis is present, set the two scales deliberately:
- Click the chart, then right-click the primary vertical axis on the left and select Format Axis.
- In the Format Axis pane, open Axis Options. Under Bounds, note or set the Minimum and Maximum values for the primary scale. If those fields show Auto, enter suitable numeric values.
- Right-click the secondary vertical axis, usually on the right, and select Format Axis.
- Under Axis Options > Bounds, enter a minimum and maximum for the secondary scale that put zero at the same fraction of the plot height as on the primary scale.
- Check that all plotted values remain within their respective bounds. If a point or column is cut off, widen that axis’s range while preserving the same zero position.
For instance, suppose the primary axis runs from -50 to 150 and the secondary series has values from -25 to 85. Set the secondary bounds to -30 and 90. Its total range is 120, and zero is 30 units above the minimum, so zero sits 25% of the way up—matching the primary axis. After entering the values, click elsewhere to apply them and inspect the chart.
On Mac, select or Control-click the axis and open Format Axis; use its axis bounds controls in the formatting pane. The pane’s appearance can vary by Excel release. Excel for the web may not expose every axis-formatting control available in the desktop application. If you cannot set both bounds there, open the workbook in desktop Excel to make the adjustment.
Calculate suitable secondary-axis bounds
There are many valid pairs of bounds that align zero. Start with the primary axis, whose minimum is A and maximum is B. Find the zero fraction by dividing the absolute value of A by B minus A, assuming A is negative and B is positive. For the secondary axis, choose a total range that is large enough to contain its data. Multiply that range by the zero fraction to get how far the secondary minimum should be below zero; the remaining part of the range becomes the maximum above zero.
For a primary range of -40 to 160, zero is 40 out of 200 units from the bottom, or 20% up. If the secondary series needs a range of 100 units, put its minimum 20 units below zero and its maximum 80 above zero: -20 to 80. That keeps zero at 20% of the height while providing more room above zero than below it.
In practice, choose bounds that are both mathematically aligned and sensible for the data. Round to readable numbers when possible, but recalculate or check the zero fraction after rounding. If the secondary data runs from -22 to 76, bounds of -20 to 80 would clip the negative value; use a wider range that still places zero at the required fraction, or reconsider the primary bounds. Avoid changing one bound alone after alignment, because that generally moves zero.
Choose bounds that preserve a useful comparison
Alignment improves one visual relationship, but it does not make the scales equivalent. A vertical distance of one inch might represent 50 units on the left and 30 percentage points on the right. Readers can compare the direction of movement and the shared zero position, but should not assume that equal-looking slopes or heights represent equal magnitudes.
Before fixing bounds, consider the purpose and audience of the chart:
- Keep every relevant minimum and maximum visible. Do not clip a value merely to make the axes line up.
- Choose readable bounds and tick intervals. A carefully aligned axis with awkward labels can be harder to interpret.
- Make clear which series uses which scale. Keep axis titles and units visible, especially when the measures differ.
- Decide whether the comparison is worth a secondary axis at all. The second scale can exaggerate visual relationships.
Bounds may also need attention when the data changes. Fixed bounds preserve the chosen alignment when new points are added, but a new extreme value can fall outside the chart. Review the chart after refreshing or extending the data range. If you return bounds to Auto, Excel may choose new limits and the zero positions may no longer match.
Zero alignment versus the axis crossing setting
A frequent source of confusion is the difference between matching zero positions and choosing where the horizontal axis crosses a vertical axis. In Format Axis, the Horizontal axis crosses setting controls where the horizontal axis is drawn relative to a value axis. It can affect whether the horizontal axis appears at zero, at an axis value, or near an edge. It does not make the primary and secondary scales share a zero height.
Changing the crossing setting alone therefore will not solve misaligned zeros. First set the bounds so zero has the same relative position on both vertical scales. Then, if needed, adjust the crossing option to control the placement of the horizontal axis line. These are related presentation settings, but they do different jobs.
Some chart types use category axes rather than numeric horizontal axes, and their crossing options may not behave like those of an XY scatter chart. The zero-alignment calculation concerns the vertical value scales. Do not assume that changing the category-axis position changes either vertical scale.
Chart types and cases where alignment helps
Secondary axes are common in combo charts, such as columns for monthly sales and a line for profit margin. Sales might be measured in dollars and margin in percent, so a second scale can keep both series legible. If either measure includes negative values, aligning the two zeros can make the sign change easier to read. The same technique applies to other chart types that display two vertical value axes.
For an XY scatter chart, both horizontal and vertical axes are numeric, and a secondary axis can introduce additional axis and crossing choices. Confirm which vertical axis belongs to each series before setting bounds. For a chart with only positive values on both scales, there may be no zero visible in either plotted range; forcing zero into the range can waste chart space. In that situation, use bounds chosen for the data rather than aligning an off-screen zero.
Secondary axes are not automatically the best choice for every mixed-unit chart. A separate chart, indexed values, or a clearly labelled table may communicate the comparison more honestly. Use a second scale when it answers a real comparison question, not simply because one series looks small next to another.
Common problems and how to fix them
- Zero still looks slightly off: Check the actual bounds shown for both axes, not just the tick labels. A rounded or mistyped bound changes the zero fraction. Also check that you selected the intended secondary vertical axis.
- A value disappears: One of the fixed bounds is too narrow. Expand the range to include the data, then choose a new pair of bounds that keeps the zero fraction matched.
- The chart changes after data is added: Excel may be using Auto bounds, or new values may exceed the fixed limits. Recheck the axis settings and the data range after updating the chart.
- The zero line is hard to see: Alignment places zero at the same height but does not guarantee a prominent line. Use chart gridlines or axis formatting to make the reference easier to follow, without confusing the line with a data series.
- The chart looks misleading: A secondary axis can make unrelated changes appear tightly connected. Add clear axis titles and units, and consider separate panels if readers might infer a relationship that the data does not establish.
Frequently Asked Questions
Does Excel have an automatic align-zero option?
No. Excel does not provide a built-in command that synchronizes zero across primary and secondary vertical axes. Set the axis bounds manually so each zero is at the same fraction of the plot height.
Do the two axes need the same minimum and maximum?
No. They need matching zero positions, not matching numerical bounds. Different bounds are expected when the series use different units or ranges.
Can I align zero if one axis has only positive values?
Only if zero is included in that axis’s displayed range. If its minimum is greater than zero, zero is not visible. You can extend the range to include zero, but that may compress the positive data and reduce readability.
Why did the alignment change when I updated the chart?
Automatic bounds can change as the data changes, and fixed bounds can become unsuitable when a new value falls outside them. Check the Minimum and Maximum settings on both axes after updating the source data.
Does setting Horizontal axis crosses to zero align the scales?
No. That setting controls where the horizontal axis is drawn. Align the vertical-axis bounds first; then set the crossing option separately if you want to change the horizontal axis’s position.
When to use another chart design
If a secondary axis makes the chart difficult to interpret, the clearest fix may be to redesign it rather than keep adjusting bounds. Two small charts with aligned time periods can show each measure on its own scale without implying that their heights are directly comparable. An indexed chart can compare relative change by expressing each series as a percentage of its own starting value, when that comparison is appropriate. A table may be better when exact values matter more than visual trends.
When you do retain a secondary axis, label both axes with units, use a legend that maps series to scales, and explain unusual scaling choices in a note if readers may question them. Aligned zero is a useful visual aid, but the bounds, units, and chart design together determine whether the chart communicates the comparison fairly.
Related
- Combine Excel Files into One: Methods and Best Practices
- How to Combine Excel Files: Methods and Common Issues
- Combine Multiple XML Files Into One Excel Workbook
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Excel Pie Chart: Exclude Zero Values and Empty SlicesExcel pie charts automatically omit zero values as slices, so you usually do not need to change a…
- How to Edit the Chart Area in ExcelTo edit a chart area in Excel, select the chart, open the contextual Format tab, choose Chart Area…
- Excel Chart Stop Line Dropping to Zero: Causes and FixesTo stop an Excel line chart from dropping to zero when data is missing, select the chart and…
- Inserting an Excel Chart Into PowerPoint: Steps and OptionsTo insert an Excel chart into PowerPoint, select the chart in Excel, press Ctrl+C, then in PowerPoint choose…
- Excel Bar of Pie Chart: How It Works and When to Use ItAn Excel Bar of Pie chart separates smaller categories from a pie chart and displays those categories as…
- 24-Hour Gantt Chart Excel: 24 Setup Ideas That WorkTo make a 24-hour Gantt chart in Excel, put 24 hourly time values across the columns, list tasks…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.