Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts
    I have several charts referring to original data in the millions. The spreadsheet is large and I want to avoid creating additional columns to ROUND the results that are to be charted.

    Is there a Custom Axis Format (Format Axis - Number - Format Code) that can round the values so that I display millions as rounded data?

  2. #2
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    I would be inclined to use Format Axis > Scale; but be careful, as the data changes data points may go off scale. If there is a risk of this you may want to automate the scaling with a macro.
    Regards
    Don

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts
    I'm hoping to find a specific Custom Format Code that would solve this.

    In the Format Axis dialog box on the Number tab there is an option for Custom Format Code.
    In my spreadsheet, the code currently selected appears like this: _(* #,##0_);_(* (#,##0);_(* "-"??_);_(@_)

    Is there a Custom Format Code code that can round millions for me so that the Axis displays the truncated values.
    Example, one chart's axis values range from 2,000,000 to 22,000,000. Is there a Custom Format Code that can round for me so that the chart displays 2 to 22, or perhaps 2.0 to 22.0.

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Yes - you can use:
    0.0,,
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Thanks Rory. That is exactly what I was looking for.

    I also tried a variation of eliminating the decimal point by using 0,, (zero comma comma) instead of 0.0,, (zero dot zero comma comma)

  6. #6
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    Thank you Rory.

    I have hunted unsuccessfully for that solution in the past.
    Regards
    Don

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    My pleasure, Don!
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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