Hide Zero

The Hide Zero tool hides rows and columns that contain only zeros. This helps you create cleaner, easier-to-read reports.

You can apply the feature manually or enable auto-hide, which dynamically hides and unhides rows or columns as data changes. The tool also includes an Unhide button to temporarily show hidden content. All actions apply to a selected range, and you can select multiple ranges at once. The function only evaluates numeric cells. If multiple ranges are selected, every column in a given range must contain only zeros to be hidden.

You can access Hide Zero from the Analysis tab of a workbook.

When to use Hide Zero

• Clarifying data: Hide rows and columns with zero values to focus on meaningful content.
• Automating updates: Let the workbook adjust visibility automatically after data refreshes.
• Controlling layout: Choose between manual or auto-hide options for flexible report design.

Hide a zero row or column

Hiding zero-filled rows and columns can improve readability by removing unnecessary visual clutter.

To hide a zero row or column:

1. Without selecting the row or column header, drag your cursor over the cell range you want to hide. You can select multiple ranges at once.
2. On the Analysis tab, select Hide Zero, then choose one of these options:
   • Hide Zero Row or Hide Zero Column: Manually hide rows or columns that contain only zeros.
   • Autohide Row or Autohide Column: Automatically hide zero-filled rows or columns after each data refresh.
3. To view hidden ranges, select the Formulas tab, then Name Manager.
   Note: Edit the hidden ranges in the Name Manager is not recommended, as they are used for refresh logic.

note

The Hide Zero function only evaluates numeric values. If multiple ranges are defined, all columns in each range must contain only zeros to be hidden.

Unhide a zero row or column

Unhide with Autohide

Use this method for rows and columns hidden using the Autohide option:

1. Click and drag your cursor over the cell ranges you want to unhide.
2. On the Analysis tab, select Unhide, then choose Unhide Row or Unhide Column to temporarily reveal the hidden content.

Unhide manually

Use this method for rows and columns hidden manually:

1. Click and drag over the ranges you want to reveal. You must select the entire row or column.
2. Right-click the selected row or column headers and choose Unhide.

Hide Zero properties

The Hide Zero tool includes options for hiding zero rows and columns, as well as unhiding them temporarily.

Hide Zero functions

Auto-hide may impact performance when used with larger datasets.

Property	Description
Hide Zero Row	Manually hides rows with only zero values in the selected range.
Hide Zero Column	Manually hides columns with only zero values in the selected range. All other defined ranges must also contain zeros.
Hide Zero All	Hides both rows and columns that contain only zero values.
Autohide Row	Automatically hides rows with only zero values after each data refresh. The selected ranges are stored in the Name Manager.
Autohide Column	Automatically hides columns with only zero values after each data refresh. All other defined ranges must also contain zeros. The selected ranges are stored in the Name Manager.
Autohide All	Automatically hides both rows and columns with only zero values after each data refresh. The selected ranges are stored in the Name Manager.
Re-apply Autohide	Reapplies the hiding logic after temporarily unhiding rows or columns.

Unhide functions

These options only apply to rows and columns hidden using the Autohide feature.

Property	Description
Unhide Row	Temporarily reveals autohidden rows for review before reapplying the hide logic.
Unhide Column	Temporarily reveals autohidden columns for review before reapplying the hide logic.
Unhide All	Temporarily reveals all autohidden rows and columns.
Forget Autohide Ranges	Deletes all stored ranges from the Name Manager to stop auto-hide from being applied.