Cell format to round off to thousands, millions, billions
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:
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:
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:
- On the Home tab, in the Number group, click the dialog box launcher:
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.
- Click OK.
- Excel displays the details for the type in the Type text box.
- You can add the currency sign to the cell format.
- If you want to show one or two digits in the decimal places, you can add them after point: