Format Chart Numbers as Thousands or Millions

Recently I have been creating some comparison charts and usually the numbers in my tables have been in a format which would suit.  However there are times when you might want to separate out the numbers between those small numbers and the larger number formats.  In order to get from whole numbers to thousands the following might be what we want to see.

Chart Label for Thousands

Notice that the numbers under 1000 are whole.

Chart Thousands

The chart is a bit cluttered as the numbers take up a large portion of the chart.  I wanted labels in millions as it is space wise more economical.

These are the steps to achieve the result.

If your chart has labels Right click on the label.

Chart Label

Choose Format Data labels.

On the Number format Choose Custom from the drop down.

Chart Label thousands

Type the number format shown above;  [<1000] 0;[>1000] 0.0,k

Chart Label for Thousands and Millions

The following is the solution for when you have thousands and millions and want to format the data so it says k after thousands and m after millions.

Chart Label millions

To achieve the above simply test for below 1m for thousands and test above 1m for millions.

The format for the data label is as follows:

[<1000000] 0,K; [>1000000] 0.0,,”m”

Choose format label either by right clicking on the series or by pressing Ctrl 1 after you select the series.