CODEWITHSUNDEEP
excelformattingnumber-formatting
jradich1234
jradich1234

Reputation: 1425

Excel Custom Number Format on chart Axis

I'm trying to set a Custom format for the Y-Axis of an Excel Chart and I'm not getting the result I need. I've referenced the Peltier webpage as a good source of information on creating custom Excel formats. I've come up with the following.

[>=1000000]0,,"M";[>=1000]0,"K";0

It works well until I need the rage to show values like 1 million, 1.2 million, 1.4 million, ect... I've tried

[>=1000000]0.#,,"M";[>=1000]0,"K";0

It does display 1.2M and 1.4M as needed but it has a period on the 1 million line "1. M". How do I modify my format to only show the period when the number to right of the decimal point is not zero?

Upvotes: 1

Views: 4085

Answers (2)

jradich1234
jradich1234

Reputation: 1425

So it looks like the best approach to allow the Axis to scale normally is to show a Zero on the right hand side of the decimal point, therefore always showing decimal point.

So, using [>=1000000]0.0,,"M";[>=1000]0,"K";0

produces 800K, 1.0M, 1.2M, 1.4M, 1.6M, 1.8M, 2M, 2.2M

Upvotes: 1

user4039065
user4039065

Reputation:

Without sample data together with expected results this may require some tweaking but you can add a specific circumstance to remove the decimal point. One of these might be appropriate.

[>1000000]0.0,,\M;[=1000000]0,,\M;0.0,K
[>1000999]0.0,,\M;[>999000]0,,\M;0.0,K

Upvotes: 0

Related Questions