Skip to main content
Documentation
Documentation
Adaptive Planning
Last Updated: 2023-06-23
Steps: Manage Zeros and Blanks

Steps: Manage 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 happens when the account has a coarser time configuration than the column. Example: The account has 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 cell in the worksheet is zero.
You can change the default so that zeros only display because a zero was actually entered.
Within the range of data linked to Adaptive Planning, you can manually hide rows or columns using the Excel, Hide capability. These rows and columns are impacted by the Hide Zeros & Blanks capability
only
if they contain all zeros.

Define Zeros and Blanks for all Workbooks

The user setting defines the default
OfficeConnect
 workbook property.
To change the user setting default:
  1. Select
    User Settings
    .
  2. Clear the checkbox next to the
    Show zero in cells with no data
     option. All new workbooks inherit this default in the workbook properties.

Define Zeros and Blanks for a Specific Workbook

To override the user settings for a specific workbook:
  1. Select
    Workbook Properties
     >
    Format
    .
  2. Clear or select the
    Show zero in cells with no data
     option.
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.

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
    .

Hide or Show Zeros and Blanks

Once you show blanks and zeros for the workbook, you can choose to suppress them by selecting
Hide Zeros and Blanks
 SuppressZeroandBlanks.png from the
OfficeConnect
 ribbon.
The button suppresses rows or columns that display all zeros, blanks or both. To hide zeros and blanks, select the button. Select it again to display zeros and blanks again.
The button works based on:
  • The row display and column display options you select in the workbook properties.
  • Your user setting for
    Show zero in cells with no data
    .

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
    Design
    group, select
    Selection Properties
    .
  3. Select or clear the different options.
  4. Click
    OK
    .
You can use the workbook properties in conjunction with the selection properties.
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.