5 Ways To Format Numbers In Millions In Excel

When working with large numbers in Excel, it can be cumbersome to display them in their entirety. For instance, instead of showing 1,000,000, it's more readable to display 1M. Excel provides several ways to format numbers in millions, making it easier to analyze and present data. In this article, we will explore five ways to format numbers in millions in Excel.

Method 1: Using the Number Formatting Options

One of the simplest ways to format numbers in millions is by using the number formatting options in Excel. To do this, follow these steps:

Select the cells containing the numbers you want to format.
Go to the Home tab in the Excel ribbon.
Click on the Number group dialog launcher (small arrow in the bottom right corner of the Number group).
In the Format Cells dialog box, select the Number tab.
Click on the Category list and select Custom.
In the Type field, enter #,##0,,M (for millions) or #,##0,,K (for thousands).
Click OK to apply the format.

This will display the numbers in millions, with the "M" suffix.

Method 2: Using the TEXT Function

Another way to format numbers in millions is by using the TEXT function in Excel. This function converts a number to text, allowing you to specify a custom format. Here's how to use it:

Select the cell where you want to display the formatted number.
Enter the formula: =TEXT(A1,"#,##0,,M"), assuming the number is in cell A1.
Press Enter to apply the formula.

This will display the number in millions, with the "M" suffix.

Understanding the TEXT Function Syntax

The TEXT function syntax is TEXT(number, format_text), where:

number is the number you want to format.
format_text is the custom format you want to apply.

In the example above, #,##0,,M is the custom format that displays the number in millions.

Method 3: Using the ROUND Function with the TEXT Function

Using the ROUND function with the TEXT function in Excel

If you want to round the number to a specific decimal place before formatting it in millions, you can use the ROUND function with the TEXT function. Here's how:

Select the cell where you want to display the formatted number.
Enter the formula: =TEXT(ROUND(A1,2),"#,##0,,M"), assuming the number is in cell A1 and you want to round it to 2 decimal places.
Press Enter to apply the formula.

This will display the rounded number in millions, with the "M" suffix.

Method 4: Using the FORMAT Function (Excel 2016 and Later)

In Excel 2016 and later, you can use the FORMAT function to format numbers in millions. Here's how:

Select the cell where you want to display the formatted number.
Enter the formula: =FORMAT(A1,"#,##0,,M"), assuming the number is in cell A1.
Press Enter to apply the formula.

This will display the number in millions, with the "M" suffix.

Understanding the FORMAT Function Syntax

The FORMAT function syntax is FORMAT(number, format_text), where:

number is the number you want to format.
format_text is the custom format you want to apply.

In the example above, #,##0,,M is the custom format that displays the number in millions.

Method 5: Using a Custom Number Format with VBA (Excel Macro)

Using a custom number format with VBA in Excel

If you want to create a custom number format that can be applied to multiple cells, you can use VBA (Visual Basic for Applications) to create an Excel macro. Here's an example:

Open the Visual Basic Editor by pressing Alt + F11 or by navigating to Developer > Visual Basic in the Excel ribbon.
In the Visual Basic Editor, click Insert > Module to insert a new module.
Paste the following code: Sub FormatMillions() Range("A1").NumberFormat = "#,##0,,M" End Sub
Close the Visual Basic Editor.
To apply the custom number format, select the cells you want to format and press Alt + F8 to run the macro.