‘Rounded’ chart labels (Excel 2002 SP3)

Topic

New Reply

I often use auto-scaling for the major unit and minor unit of the y-axis onmy charts. Usually, Excel’s wisdom figures out units that work just fine. However, when the range of values is small relative to the number of decimal places in the number specification for the tic labels, I get seemingly duplicated (but probably just rounded) numeric tic labels. For example, if the range of values is 0 to 5 and there are 10 tics and 0 decimal places are specified for the number format, the tic labels are “0, 1, 1, 2, 2, 3, 3, …”. I could specify 1 decimal place to get (0, 0.5, 1.0, 1.5, 2.0, …), but my values are in units of discrete items (# of tests performed), so it seems inappropriate to have fractional axis labels.

So the question is: Is there any way to get Excel to reduce the number of tics so that the displayed labels don’t repeat? I suspect I may have to resort to VBA coding to format the myself, but since Excel is so “smart”, I’m hoping there’s a way to do it “automatically” that I’ve overlooked.

Thanks.

Reply | Quote

Replies

Does it help if you double click the y axis and set the major or minor unit to 1 in the Scale tab of the Format Axis dialog?

Reply | Quote

Hans,

I’ll try that — I’m worried that by doing so (turning off the auto option in the process) will give undesired results when the source cell range is populated with values with a much wider range (e.g. 0 to 100). I’m trying to avoid “fixing” the axis scale every time I bring in a new set of data.

Thanks for the suggestion.

Reply | Quote

As you “fear”, wIthout Auto set, if you have data 0-100, and have major axis set to 1 excel will put1,2,3,…99,100 and have too “many values”.

The only way around it (and this is not perfect) is to use a macro (based on some action) to automatically change it based on “your own algorithm” determined by the Max and min.

You would have to write some code (we could help) to determine the major axos and have a way to trigger it based on somechange in your worksheet.

Steve

Reply | Quote

Thanks for your input, Steve. I’ll try the macro/VBA route. I just wanted to make sure I wasn’t going to be reinventing the wheel when the thing can already roll.

If I get stuck, you’ll be hearing from me!

Reply | Quote

Thanks for your input, Steve. I’ll try the macro/VBA route. I just wanted to make sure I wasn’t going to be reinventing the wheel when the thing can already roll.

If I get stuck, you’ll be hearing from me!

Reply | Quote

As you “fear”, wIthout Auto set, if you have data 0-100, and have major axis set to 1 excel will put1,2,3,…99,100 and have too “many values”.

The only way around it (and this is not perfect) is to use a macro (based on some action) to automatically change it based on “your own algorithm” determined by the Max and min.

You would have to write some code (we could help) to determine the major axos and have a way to trigger it based on somechange in your worksheet.

Steve

Reply | Quote

Hans,

I’ll try that — I’m worried that by doing so (turning off the auto option in the process) will give undesired results when the source cell range is populated with values with a much wider range (e.g. 0 to 100). I’m trying to avoid “fixing” the axis scale every time I bring in a new set of data.

Thanks for the suggestion.

Reply | Quote

Does it help if you double click the y axis and set the major or minor unit to 1 in the Scale tab of the Format Axis dialog?

Reply | Quote