Instead of showing big digits in a cell, you can format numbers in thousands, millions, and billions in Excel. Many times, you might not need the exact number to show, and instead, you can use K, M, and B to define Kilo or Thousand, Million, and Billion. For your information, you can do the same thing with Google Sheets as well.
Format numbers in Thousands, Millions, or Billions in Excel
To format numbers in Thousands, Millions, or Billions in Excel, follow these steps:
- Open the Excel spreadsheet and select cells.
- Right-click on the selected cell and choose the Format Cells option.
- Go to the Custom section.
- Enter it to convert numbers in Thousands: #0,”K”
- Enter it to convert numbers in Millions: #0,,”M”
- Enter it to convert numbers in Billions: #0,,,”B”
- Click the OK button.
To learn more about these steps, continue reading.
To get started, open the Excel spreadsheet and select cells. You can choose one or multiple cells at a time. However, there is only one catch.
All the numbers will be converted to either Thousands or Millions or Billions, even if they need to be converted into something else. In other words, if you have a digit called 1234567, it will be converted to 1234K instead of Million.
After selecting the cells, right-click on them, and select the Format Cells option from the context menu.
Alternatively, you can select the cells, head to the Numbers section in the Home tab, expand the drop-down menu, and choose the More Number Formats option as well.
Once the Format Cells window is opened, switch to the Custom tab and click on the Type box. Enter the formatting condition as follows:
- Enter it to convert numbers in Thousands: #0,”K”
- Enter it to convert numbers in Millions: #0,,”M”
- Enter it to convert numbers in Billions: #0,,,”B”
Finally, click the OK button to make the conversion happen.
There are three drawbacks to these formatting conditions:
- These conditions do not convert potential Billions or Millions or Thousands into the way it should be. For example, if there is a number called 1234 and you apply #0,,,”B”, it will show the result as 0B. At times, it could cause issues since some valid numbers might get lost during the conversion.
- You can convert only one type of number – either Billion or Million or Thousand. It doesn’t work dynamically.
- These conditions do not show decimal points.
If you want to overcome these problems, you need to apply the following formatting condition:
[<999999]0.0000,"K";[<999999999]0.0000,,"M";0.0000,,,"B"
It does show all numbers in maximum four decimal points as long as there are digits. Even if there is no digit for the fourth decimal point, it displays 0, and it is for all of them, including Thousands, Millions, and Billions.
You can certainly customize this condition as per your requirements. For that, you need to change the number of zeros after the decimal point accordingly before applying the condition.
If you want to apply these formatting conditions in Google Sheets, select the cells, click on the Format option, and select the Number > Custom number format option.
Next, enter the formatting condition to get the job done.
Read: How to create a Mirror Chart in Excel
How do you show numbers in thousands or millions in Excel?
To show numbers in thousands or millions in Excel, you can use these two formatting conditions: #0,”K” and #0,,”M”. To apply them, select the cell, right-click on it, and select the Format Cells option. Next, go to the Custom tab and enter these formatting conditions.
Read: How to add Prefix or Suffix to a Range of Cells in Excel
How do I show 1000 as 1k in Excel?
To show 1000 as 1K in Excel, you can use the formatting condition called #0,”K”. In order to apply this condition, you need to select the cell and head to the Number section. Then, expand the drop-down list and select the More Number Formats option. Next, switch to the Custom tab and enter the condition.
Read: How to remove formatting in Excel.
