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 launchpad icon:
    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.

Notes:

  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

If you have any questions or suggestions, please feel free to ask OfficeToolTips team.

We use cookies to personalise content and ads, to provide social media features and to analyse our traffic. We also share information about your use of our site with our social media, advertising and analytics partners who may combine it with other information you’ve provided to them or they’ve collected from your use of their services.

Learn more