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

Manage Zeros and Blanks

Explains how to change the way reports display zeros and blanks.

You can control how the report shows or hides rows or columns with no data or zeros. Use User, Workbook or Selection Properties. These settings follow the OfficeConnect rules of precedence

By default, OfficeConnect reports display zeros when: 

  • No one has entered a value in the corresponding sheet cell.
  • 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 the default OfficeConnect workbook property. 

To change the user setting default, click User Settings. Then, clear the checkbox next to the Show zero in cells with no data option. All new workbooks will inherit this default in the workbook properties.

To override the user settings for a specific workbook: click Workbook Properties > Format. Clear or select the checkbox next to the Show zero in cells with no data option.

User Settings toggle and Workbook Properties Toggle

If you select the checkbox, the report displays zeros in all cases when:

  • 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 you define blanks and zeros for the workbook, you can choose to suppress them by clicking Hide Zeros and Blanks SuppressZeroandBlanks.png from the OfficeConnect ribbon.

The Suppress Zeros & Blanks button in 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.
  2. Select or clear the different options under Row Display and Column Display.
  3. Click OK.

 Workbook Properties Display Options

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

Selection Properties display options for hiding zeros and blanks.

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?