MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Change values of X axis tic marks


Posted by Joan on June 21, 2001 2:33 PM

I'd like to change the values of my X axis tic marks from 1, 2, 3, 4, 5 to 5, 10, 15, 20, 25. Any help?


Posted by Mark W. on June 21, 2001 2:43 PM

What's your chart type?

Posted by Joan on June 22, 2001 7:37 AM

It's a histogram. I created this from a simple table of values, one column of the table has the cell values, 0-5, 5-10, etc, and the secong column has the quantity for each column, 0, 2,7,11,4,etc.

Posted by Mark W. on June 22, 2001 7:48 AM

Joan, I suspect that your histogram's SERIES()
function looks something like this...

=SERIES(,,Sheet2!$B$1:$B$5,1)

where your tallies listed below are in column B.
Let's say that your bins (intervals) are in column A.
You could change this SERIES() function to...

=SERIES(,Sheet2!$A$1:$A$5,Sheet2!$B$1:$B$5,1)

...and then your X-axis would display the bins.
If you only want the upper limit of each bin to
be displayed consider enter the formula,
=RIGHT(A1,LEN(A1)-FIND("-",A1)), into cell C1
and the Copy down. Now, you can change your
SERIES() function to...

=SERIES(,Sheet2!$C$1:$C$5,Sheet2!$B$1:$B$5,1)

...and get what you requested.

Posted by Joan on June 22, 2001 9:26 AM

Mark, I have not used any formulas to compile my tables or histogram. I simply have the table I described, 16 lines long. I used the chart wizard to create a chart. It does not let me chose my X axis values. The Data Analysis - Histogram tool is not working on my computer. I was able to use it to create my table but it gives me an error message when it tries to create my histogram. This was going to be my next problem to post here, but I don't think I have time to solve it right now. I simply need to change my cell values in the chart created by the chart wizard. I can chang the Y axis, but not the X axis. Any more info you have wuld be appreciated.

Posted by Mark W. on June 22, 2001 10:04 AM

Joan, the SERIES() function is used exclusively by
Excel's charting facility. It's not a worksheet
function. To view it click on one of your histogram
bars and examine the formula bar. Everything I
stated earlier still applies.

Posted by Joan on June 22, 2001 12:25 PM

Thanks, Mark

Thanks, Mark. After I worked out some bugs yuor advice worked great! Joan, the SERIES() function is used exclusively by