Microsoft Excel allows its users to store raw data in a well-structured format in a spreadsheet. The spreadsheets consist of numerous cells in the form of rows and columns in which an individual can store data. An Excel spreadsheet can store different types of data such as numbers, characters, strings, dates, date-time, etc. A lot of the time when we store a large amount of data inside an Excel spreadsheet and when we want to look out for a particular entity within that spreadsheet it becomes quite cumbersome to get information about that in a single glance. In order to manage such situations and avoid such scenarios, a user must always think about keeping the records formatted in such a way that it looks easier to the human eyes and conveys much more information in a glance. Suppose, we have an Excel spreadsheet containing data about the net worth of different persons in a city or sales of a particular company as shown in the below image.
Now, when we give these extremely large raw numbers to any individual, it would be really hard for that person to digest these numbers in one glance. To make it more convenient, we can write these numbers in a particular way like writing the numbers in thousands or millions. If we express Martin’s net worth as 456 K, Miranda’s net worth as 335 K then it becomes much more intuitive and readable to the user.
In this article, we are going to read about how can we format these numbers as thousands and millions in Excel. In this tutorial, we will be formatting the net worth in thousands and company revenue in millions. Microsoft Excel gives its users the power to apply custom formatting using the format cells tool.
Formatting net worth in thousands using the format cells dialog box
Step 1: Select the desired cells that we want to apply the formatting on.
Step 2: Press Ctrl+1 ( command+1 if using macOS) on the keyboard to open the format cells dialog box.
Step 3: Under the Number tab from the left-hand side, in the Category options, select the Custom option.
Step 4: In the Type option, enter #,##0, K, in the Sample, we can preview that the number 456235 will be displayed as 456 K.
Step 5: Now press the OK button to apply the formatting to all the selected cells. The final output will be displayed as shown in the below-given image.
Note: When we click on any of the formatted cells and observe the content in the formula bar original number will be displayed which verifies that only the formatting has been changed and the number has been stored as it is in the backend.
Formatting the company revenue in millions using the format cells dialog box
Step 1: Select the desired cells that we want to apply the formatting on.
Step 2: Press Ctrl+1(command+1 if using macOS) on the keyboard to open the format cells dialog box.
Step 3: Under the Number tab from the left-hand side, in the Category options, select the Custom option.
Step 4: In the Type option, enter #,##0,, “M”, in the Sample, we can preview that the number 253678654 will be displayed as 254 M.
Step 5: Now press the OK button to apply the formatting to all the selected cells. The final output will be displayed as shown in the below-given image.
Note: As seen in the previous case, when we click on any of the formatted cells and observe the content in the formula bar original number will be displayed which verifies that only the formatting has been changed and the number has been stored as it is in the backend.