Discover how to display Excel numbers as millions, a crucial skill for enhancing the user-friendliness, conciseness, and professionalism of your Excel Dashboards and reports.
This post will guide you through the steps to master this unique custom format, transforming how you present large figures in Excel.
Introduction
In today’s rapidly expanding business world, sales and revenues are soaring to new heights, often resulting in Excel developers encountering huge figures in the millions or even billions and trillions at major tech companies like Google, Apple, Microsoft, or Tesla.
When you need to display Excel numbers as millions, opting for a simplified format becomes crucial. Cluttered displays of large values down to the last cent are not only visually overwhelming but also add little practical value.
By converting these figures into a more manageable ‘Millions (M)’, ‘Billions (B)’, or ‘Trillions (T)’ format, such as displaying 1,501,201 as 1.50M, we enhance clarity and ease of interpretation, making your spreadsheets more user-friendly and efficient.
How to Display Excel Numbers as Millions M
A key aspect to grasp when learning to display Excel numbers as millions is that this process does not alter the actual underlying value in Excel. For instance, if a number is 1,501,201, it retains this value for all calculations.
What we’re effectively doing is modifying the presentation of this number in Excel. By choosing to display Excel numbers as millions, you’re enhancing readability and clarity, while ensuring the integrity and accuracy of the data remain intact for any computational purposes.
Take the following example in Excel of some fantastic sales numbers, in millions:
We decide that while the values are great they are far too detailed for most managers. Instead of 1,501,201 for January it would be much easier on the eye and just as useful to show 1.50M.
The first step is to select the numbers to format, in this example the cells B3 to B14 are selected.
Next right click on the selection to show the options box and select Format Cells from the menu.
The Format Cells options box will appear, from there select the Custom option on the left side of the menu.
At this point we can enter a custom format for the values. Excel provides a range of formats but none of those will change the value into the millions M format so we need to create our own.
In the Type box replace what is currently their with the following:
- #.00,,”M”
Note that above this box the Sample box will show you what this custom format will look like. In this example it shows us that the first value in the selection, 1,501,201, will now show as 1.50M.
If you click on OK the sales values will now show in the millions M format.
Also notice that the underlying value has not changed (look at the formula bar), it’s only how Excel displays the values that has changed.
Understand the Custom Format for Millions M
Understanding this custom format will allow you to change it to suit your individual needs, for example if you want to convert Excel values to Billions B or Trillions T.
Consider the Millions M format again, #.00,,”M”:
- The # represents a numeric value, it tells Excel that this is a number so always start with that.
- Next is .00 and this tells Excel to round the number to 2 decimal places. If you prefer 1.5M instead of 1.50M all you do is remove one of the zeros. Generally the larger the value the fewer decimal places you use.
- Then there are two commas ,, and tells Excel how many commas in the value to shorten. If you are making a custom format for billions there would be three commas, trillions would be four commas.
- Finally “M” is included as that is the text to display at the end of the new format. Again for Billions B you would replace the “M” with “B” or Trillions T would be “T”.
Converting Back to Original Format
To convert the new format back to displaying the original values highlight the cells again, right click and select Format Cells.
From the Format Cells options box select Number on the left side and then click OK, this will revert everything back to the original state.
With the skills acquired from this guide, you are now equipped to adeptly convert values in Excel to Millions (M), Billions (B), or Trillions (T).
This technique prepares you for handling even larger scales of data, potentially up to quadrillions, ensuring your spreadsheets remain precise and clear regardless of the magnitude of the figures you’re working with.
Summary
This post has shown you the effective technique to display Excel numbers as millions, enhancing the clarity and professionalism of your spreadsheets.
We’ve walked through the simple steps of selecting cells, applying a custom format, and fine-tuning it to fit different scales like billions or trillions.
By following this guide, you can transform large, complex numbers into a streamlined ‘Millions (M)’ format, making your Excel data more accessible and easier to interpret.
Keep Excelling,
Excel isn’t just for traditional finance anymore! Dive into our latest guide where we blend the classic with the cutting-edge, teaching you how to seamlessly integrate real-time cryptocurrency prices into your Excel spreadsheets. Check out the guide on How to Get Crypto Prices in Excel.
