How to abbreviate numbers in Excel?


You can increase the usability of your dashboards and reports in Microsoft Excel by formatting your revenue data to display in thousands. This allows you to offer cleaner numbers while avoiding overwhelming your readers with large numbers.

Suppose you have a list of really huge numbers and you want to make them look more organised by formatting them in thousands, millions, or billions respectively.

Let’s take an example and understand how you can abbreviate Numbers in Excel.

Step 1

Format Cells in Excel allows you to create custom formats. In our example, we have some prices in an Excel sheet in columnar format. Refer the following screenshot.

Step 2

Select the numbers to be abbreviated and right-click to open the Format Cells context menu.

Step 3

Under the category list select Custom from the left pane in the Format Cells dialog box, then enter the following format code in the Type text box. Below is the screenshot for the same.

For example −

  • Format numbers in thousand: 0, K

  • Format numbers in millions: 0,,M

  • Format numbers in billions: 0,,,B

Step 4

Then click OK to exit the dialog box, and your prices will be formatted in thousands (millions, or billions), as shown in the screenshot below.

Note − If you only need to abbreviate a huge number, such as a thousand "K" or a million "M," enter 0,K or 0,,M into the textbox. If you want to display a much greater number, such as 15000000000 or 18000000000 as 15B or 18T, use #,,,"B" or #,,,"T." And if you want to show a million and a trillion at the same time, you'll need this [>999999999]. .999] #,,,"B";[>999999.999]#,,"M";#,##0 _M.

Conclusion

In this tutorial, we explained how you can easily format numbers in Excel. We also demonstrated how you can abbreviate numbers in Excel using custom format cell.

Updated on: 10-Sep-2022

5K+ Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements