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 automatically hidden rows and columns.

The Hide Zero feature is located under the Analysis tab of the workbook.

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.

Manual vs. automatic hiding/unhiding

• You can choose to manually hide zero rows and zero columns using Hide Zero Row, Hide Zero Column, or Hide Zero All. If you hide rows or columns using these options, they will not reappear unless you manually unhide them.

• If you want a more dynamic approach to hiding and unhiding zero rows and zero columns, you can use the autohide features, namely Autohide Row, Autohide Column, and Autohide All. It is important to note that using the autohide features can impact performance, especially with larger data sets.

Hide Zero	Unhide

Manually hide and unhide zero rows/columns

If you need to quickly remove a row or column that contains only zeroes, you can select the ranges you need to and hide them easily. If you need the row or column back, you can just as quickly unhide it in a few clicks.

Manually hiding and unhiding zero rows/columns is useful if you need to avoid performance issues. For example, you might want to use the manual hide/unhide features after you have finished working on a report that contains static values (there is no refresh of the data).

Manually hide a zero row or column

To manually hide a zero row or column:

1. Click and drag your cursor over the range of cells you want to hide. Do not include the row/column heading or number in the selection.

2. From the Analysis tab, select Hide Zero and then choose from the following options:

   • Hide Zero Row to manually hide zero rows

   • Hide Zero Column to manually hide zero columns

   • Hide Zero All to manually hide all the zero rows and zero columns in your selection

You can also manually hide zero rows or columns by selecting your range and right clicking the selected row/column numbers and clicking Hide.

Manually unhide a zero row or column

1. Click and drag over the range you want to unhide. You must select the entire row/column (click the number of the row or column to select it entirely).

2. Right click on any of the row/column numbers you selected and click Unhide.

Automatically hide and unhide zero rows/columns

If you don’t want to worry about manually hiding and unhiding zero rows/columns every time they appear or disappear, you can use the automatic version of these tools, called Autohide.

Automatically hide a zero row or column

To automatically hide a zero row or column:

1. Click and drag your cursor over the range of cells you want to hide. Do not include the row/column heading or number in the selection.

2. From the Analysis tab, click Hide Zero and then choose from the following options:

   • Autohide Row to automatically hide zero rows

   • Autohide Column to automatically hide zero columns

   • Autohide All to automatically hide all zero rows or zero columns in your selection

3. The selected rows or columns will automatically hide all zero values after each data refresh.

4. To access and view the selected ranges hidden using autohide, go to the toolbar and select Formulas > Name Manager.

Important

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.

Automatically unhide a zero row or column

To unhide a zero row or column that was hidden with autohide:

1. Click and drag your cursor over the range of cells you want to unhide.

2. From the Analysis tab, select Unhide and then choose from the following options:

   • Unhide Row to temporarily reveal the hidden rows

   • Unhide Column to temporarily reveal the hidden columns

   • Unhide All to temporarily reveal all hidden zero rows or zero columns

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. This section includes the properties for both the manual and automatic Hide/Unhide Zero functions.

Hide Zero functions

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

Function	Description
Unhide Row	Temporarily unhides rows for review before reapplying auto-hide for the ranges found in the Name Manager.
Unhide Column	Temporarily unhides columns for review before reapplying auto-hide for the ranges found in the Name Manager.
Unhide All	Temporarily unhides the rows and columns based off of the ranges found in 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.

Example