Large numbers are often used in an Excel spreadsheet, and this can make them difficult to read if they are not formatted properly. A number in millions or even billions can be difficult to assess and could easily be misread by a factor of 10. Fortunately, Excel provides several easy ways to format numbers with comma separators to help the user with readability. If you have ever viewed financial spreadsheets, the numbers are frequently rounded to the nearest thousand because the numbers are often so large. They may even be shown in rounded to the nearest million if the numbers are extremely large. Not only can displaying numbers as thousands or millions help with readability, but it can also help to fit everything onto a single page. In this post, I’ll show you 5 easy ways to show your large numbers as thousands or millions. Download the example workbook to follow along!
Use a Simple Formula to Format Numbers as Thousands, Millions, or Billions
An easy way to show numbers in thousands or millions is to use a simple formula to divide the number by a thousand or million. To get a number in the thousand units you can use the above formula. Cell B3 contains the original number and this formula will calculate the number of thousands, showing the remainder as a decimal number. To get a number in the million units you can use the above formula. Again this will show anything less than a million as a decimal place. One of the problems here might be making sure that you entered the correct number of zeros. This is easy to make a mistake with. A way to get around this is to use the above formula. The carat character (^) raises the value of 10 to the power of 6, which is 1 million. You can use this same trick when wanting to show values in the billions since 10^9 is equal to 1 billion. You could also use the ROUND function to remove any excess decimal points from the result. The above formula will result in only one digit after the decimal place. Instead, if you want to keep the precision but only show one decimal place you can change the format.
Use Paste Special to Format Numbers as Thousands, Millions, or Billions
This is another way to divide your numbers by a thousand or a million, but without using a formula. You can use a paste special option to divide a number by a thousand or million. The disadvantage is that it will overwrite the original number, and the process is a bit more complicated. You might want to store the numbers elsewhere in your spreadsheet before using this technique! This will open up the Paste Special menu. This will overwrite your large numbers with values that have been divided by 1000. You can use the exact same process to divide your values by a million, a billion, or whatever value is needed.
Use the TEXT Function to Format Numbers as Thousands, Millions, or Billions
The TEXT function in Excel is extremely useful for formatting both numbers and dates to particular formats. In this case, you can use it to format your numbers as thousands, millions, or billions. The result will be converted to a text value, so this is not an option you’ll want to use if you need to perform any further calculations with the numbers.
Syntax for the TEXT Function
value is the numerical value you want to format.format is a text string that represents the format rule for the output.
The TEXT function takes a number and will return a text value that’s been formatted.
Example with the TEXT Function
In this post, the TEXT function will be used to add comma separators, hide any thousand digits, and add a k to the end to signify the result is in thousands. In this example, the large number is in cell B3, and the TEXT formula is used in cell C3. The above formula will show the number from cell B3 as a text value in thousands along with a k to indicate the number is in thousands. Notice the results are left-aligned? This indicates the results are text values and not numbers. The hash symbol (#) is used to provide placeholders for the digits of the numbers in the formatting string. The comma symbol (,) is used to provide a divider symbol between the groups of 3 digits. The zero is included so that should the number be less than 1000, this will show a 0 instead of nothing. If this was just a placeholder (#), then the cell would show nothing instead of a 0 when the number in B3 is less than 1000. The last comma (,) is needed to indicate that numbers below 1000 will remain hidden as there are no placeholders after this comma. Note that even when the number in cell B3 is over a million, the placeholders and commas only need to be provided for one group of 3 digits. The number in cell C3 is far easier to read than the raw number in cell B3. Use the above formula if you want to display the numbers in millions. Use the above formula if you want to display the numbers in billions. An extra comma is all that is needed to hide each set of 3 digits in the results and any character(s) can be added to the end as needed.
Use a Custom Format to Show Numbers as Thousands, Millions, or Billions
It is also possible to format your numbers as thousands or millions using a custom number format. This way you don’t need to alter the original number or use a formula to show the numbers in your desired format. You also don’t lose any precision in your numbers as they remain intact and only appear differently. To add a custom format follow these steps. If need to show your numbers in millions, use the above format string in step 5 instead. If you need to show your numbers in billions, you can use the above format string. As with the TEXT function, the hash (#) symbols act as placeholders for numbers, and the zero ensures that if the number is zero, a zero will still be displayed rather than a blank. To display any characters at the end of your numbers, you will need to insert them between two double-quote characters. Below the Type field, you will notice there is a large set of predefined custom formats. You can select any of these and modify them in the same way as the above thousand or millions formats. This can be an easy way to create a thousand or million format that also allows for negative numbers. For example, the above will format numbers in millions but also show negative numbers in red with surrounding parentheses. Any new formats you create will be listed along with all the other predefined custom formats and you can use the Delete button to remove any of the custom formats you have created. The best thing about using a custom format is you can still do further calculations on the formatted numbers. The original numbers remain unchanged. It’s only the appearance of the numbers that have changed.
Use Conditional Formatting to Format Numbers as Thousands, Millions, or Billions
Can also format numbers according to their value. This can be done by creating a conditional formatting rule for a cell or a range of cells such that the following happens.
Shows the number as normal if less than 1kShows the number as thousands when between 1k and 1MShows the number as millions when over 1M
Follow these steps to create this conditional format. This will open a lighter version of the Format Cells menu with only a Number, Font, Border, and Fill tab. This will close the Format Cells menu and the New Formatting Rule menu will be back in focus. Now you should see a preview of the formatting which will be applied when the formula is true. Now you will see any numbers in your selection appear normal when they are less than 1000 and appear in thousands when they are above 1000. Now you repeat the above steps to add another conditional formatting rule to take care of numbers that are over 1000000. The process is the exact same as before with slight changes to the Format only cells with input and the Custom format used. In step 6 enter the value 1000000 into the input box. In step 10 use the above custom format. Now you will see your numbers appear with a mix of formatting depending on the value. Numbers under 1k appear normal, while numbers over 1k but under 1M are shown in thousands, and numbers over 1M are shown in millions. For numbers less than 1k the default general format is adequate since there is no need for comma separators. If you are expecting negative numbers, you will need to repeat the process to add rules for numbers less than or equal to -1000 and less than or equal to -1000000.
Conclusions
There are many ways in Excel to present large numbers to the user. These all make the numbers easier to read and prevent mistakes from being made when someone is reading a number off of a report. If the report is large in width, it may be very necessary to show the numbers in thousands, millions, or even billions so that they can fit on the page. Using custom formatting of numbers and also conditional formatting, there are many ways to highlight specific values of numbers to draw them to the attention of the user. Do you use any of these tips for showing large numbers in thousands, millions, or billions? Do you know any other methods? Let me know in the comments below!
