Method 1 – Apply a Predefined Format to Abbreviate Numbers
Steps:
- Select column D to format it.
- Right-click on the selected range.
- A context menu bar pops up.
- Tap the Format Cells option.
- The Format Cells options menu opens up.
- Select Number, then Custom options.
- In the Type box, scroll down to tap [>999999]#,,”M”;#,”K”. You can also copy & paste it.
[>999999]#,,”M”;#,”K”
- Hit OK.
- The abbreviated number format appears.
Method 2 – Compress Numbers Using a Custom Format for Cells in Excel
Steps:
- Select the desired range you want to format.
- Right-click on it and then select the Format Cells option.
- The Format Cells menu will open.
- Go to Number > Custom > Type.
- In the Type box, insert the format 0,K.
- Press OK.
- Here’s the result.
Method 3 – Insert the ROUND Function to Abbreviate Numbers
Steps:
- Insert the following formula in cell D5:
=ROUND(C5/1000000,1)& "M"
- Press Enter and use the autofill feature to fill the other cells.
How Does the Formula Work?
- C5/1000000
This part of the formula divides the number in cell C by 1000000.
- ROUND(C5/1000000,1)
The ROUND function syntax returns 2 arguments. C5/1000000 represents the number syntax that rounds the number. 1 indicates num_digits syntax which returns the number of digits to round in.
- ROUND(C5/1000000,1)& “M”
The & “M” adds the text M.
Download the Practice Workbook
<< Go Back to Excel Abbreviation | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
