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 or columns based on refreshed data. The feature also includes an Unhide button, which lets users temporarily view hidden rows and columns.
Benefits
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
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.
The feature is available from the Analysis tab in the workbook. All these properties work based off a selected range. You can select multiple ranges at the same time to apply the functionality. Do to so, click and drag your cursor over the range you want to select. Do not include the row heading and/or column heading in your selection.
When applying the hide zero row/column logic, the Hide Zero Row/Column function only considers cells that contain numbers in them. Note also that if there is more than one range defined within the sheet, all the columns must contain zeros to be successfully hidden.
Hide Zero Functions
| Property | 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. |
Using the auto-hide functionality can impact performance, especially with larger data sets.
Unhide Functions
The Unhide Row/Column feature only applies to rows and columns that are hidden using the Autohide feature.
| Property | Description |
|---|---|
| Unhide Row | Temporarily unhides rows for review before reapplying auto-hide for the ranges found in the Name Manager. |
| Unhide Column | Temporarily unhides rows and columns for review before reapplying auto-hide for the ranges found in the Name Manager. |
| Unhide All | The ranges selected for the automatic rows and/or columns will be stored in the Name Manager. This will delete all of the ranges from the Name Manager. |
| Forget Autohide Ranges | The ranges selected for the automatic rows and/or columns will be stored in the Name Manager. This will delete all of the ranges from the Name Manager. |
Below is a quick demonstration of the Hide Zero feature: