5 Ways To Format Excel In Millions

Formatting numbers in Excel can be a game-changer for anyone who works with large datasets, especially those dealing with financial data. When working with large numbers, it can be difficult to quickly understand the scale of the values. To improve readability and make your data more digestible, you can format numbers in Excel to display in millions.

Why Format Numbers in Millions?

Formatting numbers in millions has several benefits:

• Improved readability: Large numbers can be overwhelming, making it difficult to quickly understand the scale of the values. By formatting numbers in millions, you can make your data more digestible.
• Enhanced data analysis: When numbers are displayed in millions, it becomes easier to compare and analyze data, allowing you to make more informed decisions.
• Better reporting: Formatted numbers in millions can make your reports more visually appealing and easier to understand, which is especially important when presenting data to stakeholders.

Method 1: Using Custom Number Formatting

One way to format numbers in millions is by using custom number formatting. Here's how:

1. Select the cells containing the numbers you want to format.
2. Right-click on the selected cells and choose "Format Cells" from the context menu.
3. In the Format Cells dialog box, click on the "Number" tab.
4. Select "Custom" from the Category list.
5. In the Type field, enter #,##0,, "M" (without quotes).
6. Click "OK" to apply the formatting.

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

Method 2: Using the " Accounting" Format

Another way to format numbers in millions is by using the "Accounting" format:

1. Select the cells containing the numbers you want to format.
2. Go to the Home tab in the Excel ribbon.
3. Click on the "Number" group and select "Accounting" from the drop-down list.
4. In the Accounting format, you can choose to display numbers in millions by selecting the "Millions" option from the drop-down list.

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

Method 3: Using a Formula

You can also use a formula to format numbers in millions. Here's an example:

1. Assume you have a number in cell A1 that you want to format in millions.
2. In cell B1, enter the formula =A1/1000000.
3. Format the result in cell B1 to display in millions by using the custom number formatting method described earlier.

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

Method 4: Using VBA Macro

If you want to automate the process of formatting numbers in millions, you can use a VBA macro:

1. Press "Alt + F11" to open the Visual Basic Editor.

2. In the Editor, insert a new module by clicking "Insert" > "Module".

3. Paste the following code into the module:

Sub FormatNumbersInMillions() Selection.NumberFormat = "#,##0,, ""M""" End Sub

4.  Save the module and close the Editor.
5.  Select the cells containing the numbers you want to format.
6.  Press "Alt + F8" to open the Macro dialog box.
7.  Select the "FormatNumbersInMillions" macro and click "Run".

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

### Method 5: Using Power Query

If you're using Excel 2013 or later, you can also use Power Query to format numbers in millions:

1.  Select the cells containing the numbers you want to format.
2.  Go to the "Data" tab in the Excel ribbon.
3.  Click on "From Table/Range" in the "Get & Transform Data" group.
4.  In the Power Query Editor, click on the "Transform" tab.
5.  Select the column containing the numbers you want to format.
6.  Click on "Divide" in the "Standard" group.
7.  Enter `1000000` as the divisor.
8.  Click "OK" to apply the transformation.

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

### Gallery of Formatting Excel in Millions


Formatting Excel in Millions Image Gallery

  

    
  
  

     
  
  

    
  
  

    
  
  

    
  
  

    
  



### Final Thoughts

Formatting numbers in millions can make your data more readable and easier to analyze. By using one of the methods described in this article, you can improve the presentation of your data and make it more accessible to others. Whether you're using custom number formatting, the accounting format, a formula, VBA macro, or Power Query, there's a method that suits your needs.