Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    'Rounded' chart labels (Excel 2002 SP3)

    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.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: 'Rounded' chart labels (Excel 2002 SP3)

    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?

  3. #3
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 'Rounded' chart labels (Excel 2002 SP3)

    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.

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: 'Rounded' chart labels (Excel 2002 SP3)

    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

  5. #5
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 'Rounded' chart labels (Excel 2002 SP3)

    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!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •