Excel 2013 - Formatting Numeric Axes Labels

[beadedbytes]

I need some help in changing the format of currency used as axes values.

In the source data, the dollar values are:
$17,939,065
$41,418,973
$7,692,284
$2,643,272

The axes values are 0--$10.0M--$20.0M--$30.0M--$40.0M--$50.0M. The Excel 'Format Code' used = $#,##0.0,,"M";$#,##0.0,"M";General.

Now, in the updated source data, the dollar values are:
$447,962
$179,117
$18,290
$12,125

Using the same Format Code, the axes values have changed to:
0--$0.1M--$0.1M--$0.2M--$0.2M--$0.3M--$0.3M--$0.4M--$0.4M--$0.5M--$0.5M

How do I change the Format Code so that the axes reflects the new source data ($12M -$447M) while still using $xxM format?

 

[SkipVought]

Hi,

I'm not sure I understand your explanation.

Originally your data was Millions and tens of millions.

Now your data is Tens & hundreds of thousands.

So your axis scale changes.

You seem to imply that the updated values are something other than thousands???

 

[beadedbytes]

Skip,

In the original chart, Y-axis value of $10.0M = 10 million. However, this is confusing to me since 'M' in my world is thousands, 'MM' means millions.

I would like $447,962 to be represented on the chart between Y-axis values of $400M and $500M.

 

[SkipVought]

The I really don't know if some other formatting can be achieved. Have you changed your regional numeric settings?

 

[SkipVought]

Some with like this might help

http://www.accountingweb.co.uk/article/excel-number-formats-faq/520914

 

[SkipVought]

http://simoncpage.co.uk/blog/2008/09/excel-custom-and-conditional-number-formatting/

 

[SkipVought]

If all else fails, truncate your chart data along with an Axis Title that will be the frustration of a formula in a cell on your chart data sheet that will reflect the scale if the values you are plotting.

 

[beadedbytes]

Thanks, Skip, for always being a resource. Will check out the links.