Skip to main content
Adaptive Insights
Knowledge and Support - Adaptive Insights

Format Reports

Explains how to format OfficeConnect reports.


By default, Adaptive numbers throughout your OfficeConnect report are rounded to a magnitude of thousands. If your raw number is $1,222,333,444.5555, the rounded number is $1,222,333.44 to mean $1,222.333.44 thousands. If you specify a magnitude of millions, this same number would be rounded to $1,222.33 to be read as 1,222.33 millions. Specifying the magnitude of the numbers in your report can make the numbers easier to read and understand.

Specify Rounding Default in User Settings

  1. Click User Settings  from the OfficeConnect ribbon tab.
  2. 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
  3. Click OK.

The rounding default you select applies to all new workbooks you create until you modify the workbook's workbook or selection properties. 

Specify Rounding for Workbooks

  1. Click Workbook Properties WoekbookProperties.png from the OfficeConnect ribbon tab.

  2. On the Format tab, click in the Round to dropdown.

  3. Click the rounding magnitude. If you don’t want to apply any rounding to the values in the workbook, click No Rounding.

  4. Click OK.

  1. 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.

Specify Rounding for Report Elements

Override workbook settings for specific elements by using the Selection Properties. 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.

  1. Select the row, column, or cell in which you want to make an exception to the workbook rounding magnitude setting.

  2.  Click Selection Properties .

  3. Select Suppress rounding.

  4.  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.

Auto-fit Columns

OfficeConnect automatically widens columns to accommodate large numbers. To change this:

Click Workbook Properties WoekbookProperties.png.  On the Format tab, under Column Display, clear the Auto-fit columns on refresh checkbox and click OK

Manage Zeros and Blanks

You can control how rows or columns with no data or zeros are shown or hidden by defining User, Workbook or Selection Properties.

By default, OfficeConnect reports display zeros because: 

  • The value has not been entered on the corresponding sheet cell yet.
  • The cross sections of data do not exist. This could happen because the account has a coarser time configuration than the column. For example, the account may have monthly data when the column is displaying weekly data. Because there is no weekly data for the account, it displays a blank.  
  • The value entered on the corresponding sheet cell is zero.

You can change this default so that zeros only display because a zero was actually entered.

Define Zeros and Blanks through User Settings or Workbook Properties

The User Setting defines how the new OfficeConnect workbooks you create display blanks or zeros. The Workbook Properties are specific to the workbook and can be defined once a workbook is created.

From the OfficeConnect workbook, click User Settings UserSettings1.png  or Workbook Properties WoekbookProperties.png. The Show zero in cells with no data checkbox is selected by default. Select or clear the the checkbox. 

If the checkbox is selected, the report displays zeros in all cases:

  • The value entered is zero.
  • No value has been entered. 
  • The data cannot exist at that cell's defined intersection.

If the checkbox is cleared, the report displays blanks when

  • No value has been entered. 
  • The data cannot exist at that cell's defined intersection.

Hide or Show Zeros and Blanks 

Once blanks and zeros are defined for the workbook, you can choose to suppress them by clicking Hide Zeros and Blanks SuppressZeroandBlanks.png from the OfficeConnect ribbon.

This button suppresses rows or columns that display all zeros, blanks or both. To hide zeros and blanks, click the button to activate it. Click it again to deactivate it and the report displays zeros and blanks again.

The button works according to the Row and Column Display options selected in the Workbook and Selection Properties, which you can define.

Define Display Options for the Workbook

To view and change how the report hides zeros and blanks for the workbook:

  1. From the OfficeConnect ribbon, click the Workbook Properties WoekbookProperties.png .
  2. Select or clear the different options under Row Display and Column Display.
  3. Click OK.


Define Display Options for Rows and Columns

The Selection Properties for specific rows and columns on the report override the Workbook Properties. To view or change how the report hides zeros and blanks:

  1. Select one or more rows or columns.
  2. From the OfficeConnect ribbon, click Selection Properties 
  3. Select or clear the different options. 
  4. Click OK

You can use the workbook properties and selection properties in conjunction with each other if you want. For example, you can set the workbook property to hide all columns with all zeros, but use the selection properties to specify exceptions and show certain selected columns with all zeros.


  • Was this article helpful?