How-to Format Chart Axis for Thousands or Millions

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:

ChartAxisLabelsThousandsNoFormatChartAxisLabelsMillionsNoFormat

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:

ChartAxisLabelsThousandsChartAxisLabelsMillions

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

FormatAxisNumberDialogBoxThousandsMillions

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

3 years ago by in Chart , Dashboard , Dashboard Design , Dynamic , Formatting , How-To | You can follow any responses to this entry through the RSS feed. You can leave a response, or trackback from your own site.
12 Comments to How-to Format Chart Axis for Thousands or Millions
  1. Pingback: Elektrische Zahnbuerste

    • dayl
    • 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!

      • admin
      • 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

        • dayl
        • 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 :)

          • admin
          • 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.

            • SteveEqualsTrue
            • 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

Leave A Response

* Required