A new workbook defaults to thousands for rounding. In cells, 100,000 displays as 100 and 1,000 displays as 1 and 100 displays as .1. Use rounding to make the data easier to read. Set the rounding in User Settings, Workbook Properties, or Selection Properties. These settings follow the OfficeConnect rules of precedence.
Change the Rounding Default in User Settings
Use the User Settings to change the rounding default for new workbooks. The active workbook does not change. To change the rounding default for the active workbook, use Workbook Properties.
- Click User Settings from the OfficeConnect ribbon tab.
- Click in the Round to dropdown and select the rounding magnitude. If you don’t want to apply any rounding to the values in the workbook, click No Rounding
- Click OK.
The rounding default you select applies to all new workbooks you create until you modify the workbook or selection properties.
Change the Rounding in Workbooks Properties
Use the Workbook Properties to change the rounding default for the current workbook. Your user settings do not change.
Click Workbook Properties from the OfficeConnect ribbon tab.
On the Format tab, click in the Round to dropdown.
Click the rounding magnitude. If you don’t want to apply any rounding to the values in the workbook, click No Rounding.
Click Refresh from the OfficeConnect tab.
If you have multiple worksheets in your workbook, click Refresh > All Sheets. The rounding magnitude setting applies to the entire workbook. If you have multiple worksheets, it’s best to refresh all worksheets at the same time.
Suppress Rounding for Columns, Rows, or Cells
Use the Selection Properties to override workbook settings for specific columns, rows, and cells. For example, create exceptions if you want all the numbers in your report to be rounded except for certain types of numbers like Earnings Per Share.
Select the row, column, or cell in which you want to make an exception to the workbook rounding magnitude setting.
Click Selection Properties.
Select Suppress rounding.
Click OK. The data in the row, column or cell will not be rounded.
If necessary, add a label to the un-rounded numbers to clarify that these values are exceptions to the report’s general rounding setting.