If you need to display large numbers on an Excel Dashboard or Excel report then learning how to use the Millions (M) format can really help your report to be more concise and professional looking. Read on to understand this custom format and how easy it is to implement…
A common question is how to change numerical values, sales values for example, into a “millions” format. What this means is rather than showing a full length number of 1,500,000 you would just show 1.5M in the Excel cell. This not only makes Excel reports containing large numbers easier to read and more professional looking but it can be a valuable format when developing Excel dashboards where viewing space is usually at a premium.
So how do you change the below:
Into a more space saving and visually easier this:
First off the bat it is important to understand that we do not change the actual values, we are changing the appearance of the values in Excel and the full number will be retained if you ever need to convert it back to the original state. I have seen tips posted that suggest creating formulas that divided the original number by a million then added a text “M” on the end or but it is actually so much easier and as most Excel analysts know it is usually best-practice to be able to get the data back to the original state, just in case!
The solution – Excel custom format cells
To convert the data from numeric to a “million” format you can follow these five simple steps and take less than 30-seconds of your time:
1 – Highlight the data you want to change
2 – Right-Click the mouse button and select “Format Cells” from the option box
3 – From the “Format Cells” option box select the “Number” tab at the top then in the category section select “Custom”:
4 – In the “Type” box clear the current text, usually says #,##0, and replace it with the following text
5 – Click OK, and you will see that the format has been changed and the task is complete:
To understand what has been done let me explain why we have entered #.00,, “M” into the type box.
- The # represents the numeric value so it tells Excel that we have a number here.
- This is then followed by a .00 which tells Excel that the number should be displayed two 2 decimal place.
- Next we have two commas ,, which tells Excel how many commas in the numeric to ignore. If you were doing this for billions you would have three commas, for hundreds of thousands you would have one comma.
- Finally we use “M” to include a text M on the end of the numeric which tells the user that the figure they are looking at is a million.
If you ever need to reverse the format all you do is complete steps 1 and 2 and then in step 3 you select Number from the category list and click OK.
Hopefully that shows you some of the basic principals behind the custom formatting of a number. Now you can see how to achieve Millions M you can expand and try Billions B or any other custom format that may be useful in your Excel files.