Skip to main content


Adaptive Insights LLC.

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, select User Settings. Then, clear the checkbox next to the Show zero in cells with no data option. All new workbooks inherit this default in the workbook properties.

To override the user settings for a specific workbook, select 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 is entered. 
  • No data exists at the intersection.

If the checkbox is cleared, the report displays blanks when

  • No value is entered. 
  • No data exists at the 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.

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, select Workbook Properties > Format.
  2. Select or clear the different options under Row Display and Column Display.
  3. Select 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

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?