MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Move X-Axis Labels to Bottom of Chart


Posted by Martin Hanson on April 13, 2001 8:18 AM

I have data that is both positive and negative. The default Excel charting puts
the X-Axis labes on the "zero" line in the middle of the chart. If I use the
Format Y-Axis and set the value of Y for the X-Axis, the labels do move to the
bottom of the chart, but the colums now extend from the bottom so that they all
look positive.

Is there a way to matain the "zero" line in the middel of a chart, data plots
correclty (up and down bars), AND have the X-Axis lables at the bottom of the
chart (without deleting the labels and putting in text boxes manually).

I hope this has come up before, but a quick search through my online refences has
turned up nothing. Thanks in advance for your assistance.
.


Posted by Mark W. on April 13, 2001 9:49 AM

Right-click the x-axis, and choose the Format Axis...
command. On the Patterns tab choose the Low Tick
mark labels radio button.

Posted by Martin Hanson on April 13, 2001 9:56 AM

Your solution works for most chart types, but definately NOT for column and stacked column charts. The column graphic extends from the bottom of the chart to the value--so a negative 10 is a bar from the bottom of the chart up to -10, not from the zero line down to -10.

Posted by Mark W. on April 13, 2001 11:05 AM

I haven't been able to reproduce your observed
behavior. Let's say that cells A1:B8 contains
{"X","Y";-3,-3;-2,-2;-1,-1;0,0;1,1;2,2;3,3}.
When this is graphed as a column chart all bars
originate from the X-axis which crosses the Y-axis
at x=0. The X-axis labels can then be moved to
the bottom of the chart area as previously described.
What are you doing differently?

Posted by Martin Hanson on April 13, 2001 11:11 AM

Ok, try this...

A1-A12 Jan, Feb, Mar, etc.
B1-B12 10, -10, -20, -30, 10, 30, 40 (any mix of +/- 12 numbers
C1-C12 20, 30, -10, -30, -50, 20, 40 (any mix of +/- 12 numbers

Create a side-by-side or stacked column graph with the X-series data A1..A12, Series 1 B1..B12, Series 2 C1..C12

Now, format the Y-axis setting the value for X-Axis crossing at -50--the bars extend form the bottom of the chart to the value at eat month.

(Let me know if you want me to send you an example file.)

BTW, thanks for your interest and help. I am using Excel 2000

Posted by Martin Hanson on April 13, 2001 11:17 AM

Use your data and a second series of data--choose side-by-side column type graph and see what you get---stacked bar now seems to be working. I haven't been able to reproduce your observed

Posted by Mark W. on April 13, 2001 12:38 PM

My data does have 2 series, X & Y. The X-values
default to {1,2,3,4,5,6,7}. I charted this data
as both a stacked and standard column chart without
a problem.

Posted by Mark W. on April 13, 2001 12:47 PM

Why are you formatting the Y-axis to cross at a
value other than 0? You don't have to change this
setting to get your labels to print at the bottom
of the print area. Just change the X-axis Patterns
Low Tick mark labels as described below, and leave
the Y-axis with it's default setting.

: I haven't been able to reproduce your observed

Posted by Martin Hanson on April 16, 2001 12:50 PM


THANK YOU-- the "LOW" option is what I couldn't find--does just what I wanted.

Why are you formatting the Y-axis to cross at a value other than 0? You don't have to change this setting to get your labels to print at the bottom of the print area. Just change the X-axis Patterns Low Tick mark labels as described below, and leave the Y-axis with it's default setting. : Ok, try this... : A1-A12 Jan, Feb, Mar, etc. : B1-B12 10, -10, -20, -30, 10, 30, 40 (any mix of +/- 12 numbers : C1-C12 20, 30, -10, -30, -50, 20, 40 (any mix of +/- 12 numbers : Create a side-by-side or stacked column graph with the X-series data A1..A12, Series 1 B1..B12, Series 2 C1..C12 : Now, format the Y-axis setting the value for X-Axis crossing at -50--the bars extend form the bottom of the chart to the value at eat month. : (Let me know if you want me to send you an example file.) : BTW, thanks for your interest and help. I am using Excel 2000 :