Format Numbers as Thousands, Millions, or Billions in Excel

You will sometimes need to report some of your figures in thousands, millions, or billions.

This is typically accomplished by dividing the original figures by thousands, millions, or billions, and can be time and/or resource consuming process.

However, Excel also offers an alternative in custom number formats. In Excel, numbers can be formatted to be shown as thousands, millions, or billions without any sort of calculations or modifications to the underlying numbers.

Custom number formats are created (modified from existing formats) under Format Cells, Number, Custom:

Numbers are scaled to thousands, millions, or billions by adding digit group separators to the existing formats.

One digit group separator for thousands, two digit group separators for millions, three digit group separators for billions, etc.

Digit group separators are added as a final item for a part of the format code for positive numbers and the format code for negative numbers.

As my digit group separator is a period [.], I’m adding periods. If your digit group separator is set to a comma [,], add a comma.

Now consider the following example:

This is a table containing all of the available invoices from January. We have to present those income figures in millions of EUR.

For that purpose, we will create a Pivot Chart:

By default, our Pivot Table and Pivot Chart will contain figures in EUR:

However, we can select our data and format it as millions:

Once we apply our new number format, all of the figures are shown in millions, including the ones on the chart:

Note again that no formulas or calculations were involved, and underlying values were not modified.

Dig deeper:

Custom Number Formats in Excel