Cell format to round off to thousands, millions, billions

Excel 365
Many of us live in the information space with the calculations performed with precise data expressed in cents or even more granular amounts. Still, it is advisable to present them in thousands or even millions in presentations and reports.

You can work in Excel with already rounded data, bearing in mind that the data in such columns represent thousands or millions of units. Moreover, you can specify the rounding unit in the column name. For example:

Example for round off in Excel 365

However, for precise calculations, the data must be in cents. Excel provides a very convenient mechanism for storing data in cents or even smaller values but displaying it in a format convenient for you. For example:

Example of round off in Excel 365

To create a custom format for displaying (not storing!) your data, do the following:

   1.   Select the cell or cell range to round off.

   2.   Do one of the following:

  • Right-click on the selection and choose Format Cells... in the popup menu:
    Format cells in popup menu Excel 365
  • On the Home tab, in the Number group, click the dialog box launcher:
    Cells Format in Excel 365

   3.   In the Format Cells dialog box:

  • On the Number tab, in the Category list, select the Custom item.
  • In the Type list box:
    • Type #0,"K" or #0,"M" for thousands,
    • Type #0,,"M" or #0,,"MM" for millions,
    • Type #0,,,"B" or #0,,,"MMM" for billions.

    For example:

    Format cells for thousands in Excel 365
  • Click OK.


  1. Excel displays the details for the type in the Type text box.
  2. You can add the currency sign to the cell format.
  3. If you want to show one or two digits in the decimal places, you can add them after point:
    Format cells for millions in Excel 365

Please, disable AdBlock and reload the page to continue

Today, 30% of our visitors use Ad-Block to block ads.We understand your pain with ads, but without ads, we won't be able to provide you with free content soon. If you need our content for work or study, please support our efforts and disable AdBlock for our site. As you will see, we have a lot of helpful information to share.