4

In other words, I want Excel to anchor the tick marks at 0.

I am trying to get an axis that goes from -35 to 35, but with the ticks on multiples of 10:

  • 30, 20, 10, 0, -10, -20, -30

I have set set a "fixed" (custom) major unit to 10, and with my data the max and min would be -40 and 40 automatically, so I have also set "fixed" values for the axis ends (-35 for minimum and 35 for maximum).

...but then the tick marks are at:

  • 35, 25, 15, 5, -5, -15, -25, 35

How can I force the tick marks to be grounded at 0? (This should be the default!)

Edit: This picture pretty much explains the problem.

enter image description here

...and here are some data you can copy and paste into Excel to graph if you can solve this. ;)

x
0
0

0

y
30
-20

-30

niton
  • 1,832
A.M.
  • 1,017
  • 5
  • 17
  • 24

2 Answers2

4

It's easy enough to fake the labels and gridlines, using hidden XY series with data labels and error bars.

First, format both axes to hard code the min to -35, the max to +35, and the tick spacing to 5.

Put {-30,-20,-10,0,10,20,30} into a column and all zeros in the next column. Add two series to the chart. The first should use the values for X and the zeros for Y, the second should use the zeros for X and the values for Y. This adds points along the two axes where you want labels.

Format the added series to use no lines and no markers. Add data labels below the series on the horizontal axis using the category labels (X values). Add data labels to the left of the series on the vertical axis using the Y values.

For gridlines, add plus and minus horizontal error bars of length 35 to the series on the vertical axis. Add plus and minus vertical error bars of length 35 to the series on the horizontal axis. Format the error bars with a light gray, without end caps.

See my step-by-step tutorial at Custom Axis Labels and Gridlines in an Excel Chart

Jon Peltier
  • 4,630
  • 24
  • 27
1

Assuming you are talking about a line chart, here's one way to do it:

  • Add a second data set that is in increments of 10 (-40, -30, etc.)

  • Set its axis to the secondary axis.

  • Set its axis data labels to Low (this will move it next to the primary axis

  • Set the data labels for the primary axis to High (this will move it to the right side axis.

  • Set the primary axis number format to the custom format of ";;;" (this will make them not show

  • Turn off the primary axis tick marks

  • Set the line color of the secondary data series to No line

  • Select the legend for the second data series and delete it

enter image description here

chuff
  • 3,534