How to Abbreviate Billions in Excel (6 Easy Methods)

When you have a large number as a cell value for monetary purposes, then you may need to abbreviate Billions in Excel. So, if you are looking for how to abbreviate Billions in Excel, then you have come to the right place. Today, in this article, I’m going to explain how to abbreviate Billions in Excel.

Furthermore, for conducting the session, I will use Microsoft 365 version.

How to Abbreviate Billions in Excel: 6 Methods

Here, I will demonstrate 6 suitable methods with detailed steps on how to abbreviate Billions in Excel. For your better understanding, I am going to use the following dataset. Which contains three columns. Those are Years, Sales, and Profit. The dataset is given below.

1. Employing Custom Format to Abbreviate Billions in Excel

Here, you can use the Custom Format option from the Format Cells feature to abbreviate billions in Excel. So, follow the steps given below.

Steps:

First, select the data range >> go to the Home tab.
Then, from the drop-down arrow of the Number feature >> choose the More Number Formats command.

Also, you can use the Context Menu Bar to go to the Format Cells command.

In the case of using the Context Menu Bar, select the data range >> Right-Click on the data >> choose the Format Cells option.

As a result, a dialog box named Format Cells will appear.

Now, from that dialog box, you have to make sure that you are on the Number command.
Then, go to the Custom option.
After that, write the following format in the Type box. Here, you can see the sample immediately in the Sample box so you can modify your format according to your preference.

0.000,,," B"

Finally, you have to press OK to get the changes.

Lastly, you will see the monetary unit as Billion (B).

2. Applying Keyboard Shortcuts to Shorten Billions in Excel

Here, you may apply keyboard shortcuts to abbreviate billions in Excel. Basically, this method is similar to the previous one. Actually, I will use keyboard shortcuts to open the Format Cells dialog box directly. So, follow the steps given below.

Steps:

Now, you need to press the CTRL+1 keys to open the Format Cells dialog box directly.

At this time, a dialog box named Format Cells will appear.

First, from that dialog box, you have to make sure that you are on the Number command.
Then, go to the Custom option.
Then, write the following format in the Type box. Here, you can see the sample immediately in the Sample box so you can modify your format according to your preference.

#.000,,," B"

Lastly, you have to press OK to get the changes.

Finally, you will see the monetary unit as Billion (B).

3. Abbreviate Billions with Commas Using TEXT Function

You can apply the TEXT function to abbreviate billions in Excel. Now, follow the steps given below.

Steps:

Firstly, you must select a new cell E5 where you want to keep the Sales with unit (B).
Secondly, you should use the formula given below in the E5 cell.

=TEXT(C5,"#,##0,,, ")&" B"

Thirdly, press ENTER to get the format.

Formula Breakdown

Here, in this formula, I have used only the TEXT function. This function will return a particular number with a specified format. Where C5 is that number and “#,##0,,, ” is that format. The three commas denote the number should be divided by 1000000000.
Lastly, the Ampersand (&) operator will join “ B” with the format.

Then, you can drag the Fill Handle icon to the right for the same format in the F5 cell.

Then, select both the cells E5:F5 and drag the Fill Handle icon down to AutoFill the corresponding data in the rest of the cells E6:F14.

Finally, you will see the monetary unit as Billion (B). Here, if you notice then you will find some values become zero (0 B). Actually, those values are less than 1000000000.

At this time, I will modify my format to these cells which become zero.

Firstly, you go to cell E12.
Secondly, use the formula given below in the E12 cell.

=TEXT(C12,"#,##0.000,,, ")&" B"

Thirdly, press ENTER to get the format.

Formula Breakdown

Similarly, in this formula, I have used only the TEXT function. This function will return a particular number with a specified format. Where C12 is that number and “#,##0.000,,, ” is that format. The three commas denote the number should be divided by 1000000000. Furthermore, .000 denotes it will take decimal values up to 3 terms.
Then, the Ampersand (&) operator will join “ B” with the format.

Then, you can drag the Fill Handle icon to change the other cells.

Lastly, you will get the following result.

4. Employing Generic Formula to Abbreviate Billions in Excel

You can employ a generic formula to abbreviate billions in Excel. Follow the steps given below.

Steps:

Firstly, you must select a new cell D5 where you want to keep the Sales with unit (B).
Secondly, you should use the formula given below in the D5 cell.

=(C5/10^9) & " B"

Thirdly, press ENTER to get the format.

Now, you can drag the Fill Handle icon to AutoFill the corresponding data in the rest of the cells D6:D14.

As a result, you will find all the abbreviated formats.

5. Using Excel Paste Special Options to Abbreviate Billions

You can use the Paste Special option to abbreviate billions in Excel. But, to do so, at first you have to modify your dataset. So, follow the steps given below.

Steps:

Firstly, write 1000000000 in the F5 cell. As to convert a monetary amount into Billion you have to divide that by 1000000000.

Then, copy the F5 cell by pressing CTRL+C.
Consequently, select the data up to which you want to abbreviate billions.
After that, right-click on the Mouse.
Then, from the Context Menu Bar >> choose Paste Special option.

As a result, you will see a new dialog box named Paste Special.

First, choose Values from the Paste option.
Then, select Divide from the Operation option.
Subsequently, press OK.

Finally, you will see the monetary unit as Billion (B). Here, if you notice then you will find some values become zero (0 B). Actually, those values are less than 1000000000.

6. Applying Conditional Formatting to Abbreviate Billions with Commas

Here, you can use Conditional Formatting to abbreviate billions in Excel. The steps are given below.

Steps:

Firstly, you should select the data range for which you want to apply the Conditional Formatting. Here, I have selected the data range C5:D14.
Secondly, from the Home tab >> you need to go to the Conditional Formatting feature.
Thirdly, select the New Rule… option.

At this time, a dialog box named New Formatting Rule will appear.

Now, from that dialog box >> you have to select Format only cells that contain.
First chosen Cell Value.
Next, greater than.
Then, 1000000000
After that, go to the Format menu.

At this time, a dialog box named Format Cells will appear.

Now, from the Number feature >> go to the Custom option.
After that, write the following format in the Type box. Here, you can see the sample immediately in the Sample box so you can modify your format according to your preference.

#,###0.000,,," B"

Then, you must press OK to apply the formation.

After that, you have to press OK on the New Formatting Rule dialog box. Here, you can see the sample instantly in the Preview box.

As a result, you will find only the abbreviated formats in billions.

How to Convert Numbers into Millions in Excel

In this section, I will explain how to convert numbers into millions in Excel. This is actually similar to the abbreviation billions. Just you have to use a little bit different format.

First, follow the steps of method-1 or you can also follow the steps of method-2 to open the Format Cells dialog box.

After popping up the dialog box named Format Cells, you may follow the below steps.

Firstly, from that dialog box, you have to make sure that you are on the Number command.
Secondly, go to the Custom option.
Thirdly, write the following format in the Type box. Here, you can see the sample immediately in the Sample box so you can modify your format according to your preference.

0,," M"

Lastly, you have to press OK to get the changes.

As a result, you will find all the abbreviated formats.

Practice Section

Now, you can practice the explained method by yourself.

Download Practice Workbook

You can download the practice workbook from here:

Abbreviating Billions.xlsx

Conclusion

I hope you found this article helpful. Here, I have explained 6 suitable methods to abbreviate billions in Excel. Please, drop comments, suggestions, or queries if you have any in the comment section below.

<< Go Back to Excel Abbreviation | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

2 Comments

ASIF MAHMOOD Dec 12, 2023 at 4:56 PM

Now I need to know how to the reverse of it?

Like for example if we have been provided with the data in which the figures are written like

Sales 159.3b and we have to convert the figures to show it as 1,593,000,000.

The long way I know is to edit the cell and delete b from it and multiply 159.3 with 1,000,000,000-

1. Can you explain how to do it in short?
2. I that possible that we set the cells to put 159.3 and on entering it automatically turns the figure to 1,593,000,000-

I will really appreciate the quick reply
- Reply
  Lutfor Rahman Shimanto Dec 13, 2023 at 2:55 PM
  Hello ASIF MAHMOOD
  
  Thanks for reaching out and sharing your requirements with such clarity. You wanted to know how to convert a string like 159.3 B into a regular unit format (for example, 159300000000.00).
  
  I am delighted to inform you that I have developed an Excel VBA Event Procedure that will trigger when a value is inserted in a format like 13 B in column B and convert the values into a regular unit. Besides, I have developed an Excel formula to fulfil your goal using the TEXT and SUBSTITUTE functions.
  
  OUTPUT OVERVIEW (Excel VBA Event Procedure):
  
  Follow these steps:
  
  Step 1: Right-click on the sheet name tab => Click on View Code.
  
  Step 2: Hover over Insert => Click on Module => Paste the following code in the module => Save.
```
Private Sub Worksheet_Change(ByVal Target As Range)

    Dim ws As Worksheet

    Set ws = ThisWorkbook.Sheets("Sheet2")

    Set IntersectRange = Intersect(Target, ws.Columns("B"))

    If Not Intersect(Target, ws.Columns("B")) Is Nothing Then
        
        Application.EnableEvents = False
        
        On Error Resume Next
    
        If Not IsEmpty(Target.Value) Then
            Target.Value = Application.WorksheetFunction.Text(Application.WorksheetFunction.Substitute(Target.Value, " B", "") * 1000000000, "0.00")
            Target.NumberFormat = "0.00"
        End If

        Application.EnableEvents = True
    
    End If

End Sub
```
  OUTPUT OVERVIEW (Using Excel TEXT and SUBSTITUTE Functions):
  
  Follow these steps:
  
  Step 1: Select cell C5 => Insert the following formula => Hit Enter.
  
  =TEXT(SUBSTITUTE(B5, ” B”, “”) * 1000000000, “0.00”)
  
  Step 2: Drag the Fill Handle icon to cell C14 to copy the formula down.
  
  I am also attaching the solution workbook for better understanding. Good luck.
  
  DOWNLOAD WORKBOOK
  
  Regards
  Lutfor Rahman Shimanto
  ExcelDemy

How to Abbreviate Billions in Excel (6 Easy Methods)

How to Abbreviate Billions in Excel: 6 Methods

1. Employing Custom Format to Abbreviate Billions in Excel

2. Applying Keyboard Shortcuts to Shorten Billions in Excel

3. Abbreviate Billions with Commas Using TEXT Function

4. Employing Generic Formula to Abbreviate Billions in Excel

5. Using Excel Paste Special Options to Abbreviate Billions

6. Applying Conditional Formatting to Abbreviate Billions with Commas

How to Convert Numbers into Millions in Excel

Practice Section

Conclusion

Leave a reply Cancel reply