Hide Zero

Located within the workbook, the Hide Zero tool allows you to hide rows and columns with cells that contain only zeroes. You can do this by specifying the range of rows and columns to be hidden by selecting the Hide Zero tool. This helps users create cleaner and more readable reports. Users can manually hide zero-filled rows and columns or enable the auto-hide function, which will dynamically hide and unhide rows and columns based on refreshed data. The feature also includes an Unhide button, which lets users temporarily view hidden rows and columns.

Why use the Hide Zero feature?

Data clarity: Focus on the data that matters by hiding rows and columns with zero values.
Dynamic updates: Automatically adjust visibility based on refreshed data, reducing manual intervention.
Flexible control: Options to manually hide or auto-hide data allow flexibility in report design.

Hide a Zero Row or Column

To hide a zero row or column:

Click and drag your cursor over the range of cells you want to hide. Do not include the row or column heading in the selection.
From the Analysis tab, select Hide Zero and then:
- Hide Zero Row or Hide Zero Column to manually hide the rows or columns you have selected.
- Autohide Row or Autohide Column to automatically hide rows or columns with zero rows after each data refresh.
To access and view the hidden ranges, go to the toolbar and select Formulas > Name Manager. It is not recommended that you edit or update the hidden ranges using the Name Manager, as this is the reference used to automatically update rows after a refresh.

For a complete list of the functions available in the Hide Zero tool, refer to Hide Zero Functions.

Note that the Hide Zero function only considers cells that contain numbers in them. Also, if there is more than one range defined within the sheet, all the columns must contain zeros to be successfully hidden.

Unhide a Zero Row or Column

The Unhide feature applies only to rows and columns that have been hidden using the Autohide functionality. Refer to Unhide Manually Hidden Zero Rows for steps on this procedure.

To unhide a zero row or column:

Click and drag your cursor over the range of cells you want to unhide.
From the Analysis tab, select Unhide and then select Unhide Row or Unhide Column to temporarily reveal the rows.

For a complete list of the functions available in the Unhide tool, refer to Unhide Functions.

Unhide Manually Hidden Zero Rows

The above procedure only applies to rows and columns hidden using the Autohide functionality. To reveal manually hidden zero rows or columns:

Click and drag over the range you want to reveal.
Right-click your selection and select Unhide.

Hide Zero Properties

The Hide Zero tool includes options for hiding zero rows and columns as well as an "unhiding" feature that temporarily reveals hidden rows and columns.

Hide Zero Functions

Note

Using the auto-hide functionality can impact performance, especially with larger data sets.

Function	Description
Hide Zero Row	Manually hides rows where all values are zero within the selected range.
Hide Zero Column	Manually hides columns where all values are zero within the selected range (on the condition that the other existing ranges also contains zeros).
Hide Zero All	Hides both rows and columns that contain all zero values.
Autohide Row	Automatically hides rows with all zero values after each data refresh. The selected range(s) will be stored in the Name Manager.
Autohide Column	Automatically hides columns with all zero values after each data refresh (on the condition that the other existing ranges also contains zeros). The selected range(s) will be stored within the Name Manager.
Autohide All	Automatically hides both rows and columns with zero values after each data refresh. The selected range(s) will be stored within the Name Manager.
Reapply Autohide	Re-apply the logic to hide rows and/or columns within the sheet after temporarily unhiding rows and/or columns.

Unhide Functions