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.
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”;$#
- 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:
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.