Here is a great way to make your Dashboard Charts in Excel more appealing and your boss will love you and your friends will envy your Extreme Spreadsheet knowledge.

When you have really large numbers like Hundreds of Thousands or Millions, Excel doesn’t’ format these Chart Axis values in a visually friendly way.

Here is how Excel typically will format an axis for Thousands or Millions:

This can be fine in most cases, however, this is not very Dashboard Reader friendly. Especially with the millions format showing so many Zeros. It gets hard for users to comprehend is this millions or billions or trillions. So lets make it easier for our Executives and Management when they are reading Key Performance Indicators on the Company Dashboard. Lets make the format for our Dashboard Chart Axis look more like this:

I think that you and your Executives will both agree that a format of $20K or $20M is much easier on the eyes and easier to understand and comprehend in any Dashboard Design.

**The Breakdown:**

So how do we tell excel to format in this fashion?

- Essentially, we are going to format the Axis Numbers of the Chart with a Custom setting of: [>999999]$#,,”M”;[>999]$#,”K”;$#
**Step-by-Step:**- Lets do a walkthrough.

1) Click on the Excel Dashboard Chart and Right Click on the Vertical Axis and then Click on Format Axis

2) Select Number from the Format Axis Dialog Box

3) Select Custom and Replace the current Excel Format Code with: [>999999]$#,,”M”;[>999]$#,”K”;$#

4) Click on Add Button5) Click on Close Button

**What did we do?**

We have now created a chart format for the Vertical Axis such that:

[>999999]$#,,”M”;[>999]$#,”K”;$#

If the Major Axis Label is >999999 then it will format the axis for currency in millions.

Then if it is not Greater $1Million and if it is >999 then format it for currency in thousands.

And finally, if it does not meet either criteria, then just format it for currency.

Steve=True

Nice Tip. Helpful. Thank you!

Thanks for the feedback. Glad it helped you!

Which version of Excel did you try on? I tried in Excel 2007 and it does not allow me to set such a format: “Microsoft Office Excel cannot use the number format you tried” 🙁 I removed the “M” and the “K” and now I don’t have an error, but I need to view my numbers in millions. So… is there an alternative or a solution for this problem? Thank you in advance!

Dayl,

So sorry it is not working for you. I am pretty sure I did this in 2007. Unfortunately, I am running on an Excel 2010 machine until Saturday, so I can’t try it until then. However, I did create a chart in 2010 and saved it as 2003 xls and when I opened it up again, my number format changed from this: [>999999]$#,,”M”;[>999]$#,”K”;$# to this: [>999999]\$#,,”M”;[>999]\$#,”K”;\$#

Also, make sure you don’t have spaces in the front or end or in between. Perhaps that is the problem.

Let me know if this helps.

Steve=True

Hello,

thank you for the reply. No, there are no spaces. I tried both variants from your answer and receive the same message. I’ll keep looking 🙂

Have you tried to build it one piece at a time? i.e. just the first part before the first semi-colon, then see if it works, then the next part up to the next semi-colon.

dayl, I think the issue may be that if you copy and paste from the website, then Excel may convert your quotation marks in the formula to a different quotation mark that Excel doesn’t recognize. If you copy and paste, I suggest that you delete and replace the quotation marks around the M and the K. Notice here that a copy and paste of the formula, the quotation marks look like they are in italics. I had a similar problem and after I paste the formula in the custom number format area, I replaced the quotation marks by deleting them and typing them in again and it worked. [>999999]$#,,”M”;[>999]$#,”K”;$# Hope you were able to figure it out. Steve=True

Thanks a lot ‘SteveEqualsTrue’. It worked fine after replacing the quotation mark.

Awesome. So glad it worked for you. Steve=True

I’ve been looking for how to do this everywhere! Thank you for sharing this!

Thanks Paul for the great comment. You are very welcome. Steve=True

How can you adapt this for negative numbers? I tried

[>-999999]($#,,”M”);[>-999]($#,”K”);[>999999]$#,,”M”;[>999]$#,”K”;$#

and

[>999999]$#,,”M”;[>999]$#,”K”;[>-999]$#;[>-999999]($#,”K”);($#,,”M”)

but excel likes neither

Hi Sean,

The Excel custom conditional number format is a If, Then, Else then type of format separated by the “;” . So you can do >0, 0, <0 and apply formats to those groupings, but you cannot add additional layers like if >0 and >100k and >1M in conjunction with negative formatting when below zero and <-100k. Sorry, but hope this helps!Alternately, you would have to use VBA to set the number format when the number is positive or negative.Steve=True

How do you use this formula without the dollar sign? I am using different units than dollars

Hi Matt,

What currency?

What you want to do is to copy the format and then simply replace the symbol.

One issue from copying this format from any site is that Excel typically replaces the ” symbol with a different one that doesn’t work as expected. So you will also want to edit and replace the ” quote symbols in the format before using it.

Here is one for the Indian Rupee

[>999999]र#,,”M”;[>999]र#,”K”;र#

Here is one for the British Pound

[>999999]£#,,”M”;[>999]£#,”K”;£#

Here is one with a music symbol 🙂

[>999999]♫#,,”M”;[>999]♫#,”K”;♫#

Because of your interesting question, I am going to do a post on this month.

Hope this helps in the mean time.

Steve=True

Hi Steve ,

Great article, I am also struggling with negative values. Since you already suggested that we can not do this for Both negative and positive in conjunction. I am struggling to understand how can we apply VBA to it.

Thanks

Hi Yatharth, sorry, I am not a VBA expert. Perhaps I can get a fellow fan with this skill to help. Stay tuned. Steve=True