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.
- Select User Settings from the OfficeConnect ribbon tab.
- From the Round to drop-down, select the rounding magnitude. If you don’t want to apply any rounding to the values in the workbook, select 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 or active workbook. Your user settings do not change.
Select Workbook Properties from the OfficeConnect ribbon tab.
On the Format tab, click in the Round to drop-down.
Select the rounding magnitude. If you don’t want to apply any rounding to the values in the workbook, select No Rounding.
If you have multiple worksheets in your workbook, select 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.
Select Selection Properties.
Select Suppress rounding.
Select 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.