Hide Zero Rows / Columns

Created by Damien Zwillinger, Modified on Mon, 19 Aug at 6:32 PM by Harry Lewis

Applies to:

Velixo NX
Velixo Classic

Using the Hide Zero Rows (or Columns) button can make your reports tidier and more readable by automatically hiding rows (or columns) that only contain zero values.

See the video for a demonstration of this feature:

Basic Hiding

To use this function, just select a range of rows, an individual column, or multiple columns and click the Hide Zero Rows button.

Velixo will automatically hide all the rows where every selected column in your selection is zero. Empty rows are ignored to ensure the layout of your report is not impacted.

To re-show all rows and columns that are hidden, use Unhide All:

Alternately, to show rows that are hidden, press Ctrl + Shift + 9 or use the Unhide command in the Excel contextual (right-click) menu:

You can also use the Format menu in the Cells section of the Home tab in the ribbon:

In version of Velixo prior to 6.0.376, blanks are *not* treated as zeros. Newer versions of Velixo allow you to choose how to treat blank values.

Auto Hide / Unhide

When using basic hiding, you need to manually unhide the rows before refreshing your report and then re-hide those rows that now contain the zero values you do not want to see.

Auto Hiding allows you to set your hiding criteria and then have Velixo remember that and automatically hide zero rows and un-hide non-zero rows as the values in your report are refreshed.

The Auto Hide/Unhide feature is accessed through the pull-down on the Hide Zero Rows button:

Note: the Velixo Hide features can work substantially faster if you select just the cells you want to consider, instead of selecting entire columns)

Example

Here is an example of the results of using the Auto Hide/Unhide Rows feature:

In the example, the original report shows some zero values in column B. By selecting cells B4 though the last data value in column B, and then clicking Auto Hide / Unhide Rows in Selected Range from the Velixo ribbon, those zero-value rows are hidden. (Velixo will now remember the area to which you want to apply this hiding criteria. This area is referred to as the "Controlled Range")

Next, when the report is refreshed with new data, Velixo automatically adjusts which rows are hidden, based on the new values in the report.

Auto Hide FAQ

When does Velixo auto-hide/unhide the controlled ranges?	Upon every workbook calculation (e.g. when you update a formula) as well as Velixo Refresh.
Is it possible for unhidden cells to remain unhidden?	Assuming you base your auto-hide on a subset of cells (not on entire columns), if you use the unhide feature and then change a filter on your report - but the data in the cells does not change - it is possible for the zero cells to continue to be displayed until you use the Reapply Auto-Hide feature
When we use Excel's filtering feature in a table, Excel hides/unhides rows automatically. How can we get Excel to detect our auto-hide settings?	Select Reapply Auto-Hide Logic* from the Hide Zero Rows pulldown list*
Is it better to select entire columns or just the rows in each column to which I want to apply the Auto-Hide feature?	Because Excel allows over 1 million rows in any column, applying the Auto-Hide feature to all rows in multiple columns can take a while to evaluate. Much faster results can be obtained by selecting only the region of cells in which you can reasonably expect data to be contained.
What is the scope of auto-hiding of the range I select?	The current worksheet
Where can I review if there is any auto-hide range for the worksheet, and if there is, what is it?	From Excel's Formulas* ribbon, select Name Manager and look for VelixoAutoHideZeroRows and AutoHideZeroColumns*
Can I change which cells are being considered by the auto-hide feature?	Yes. Selecting a new range of cells and then clicking the auto-hide menu option will overwrite the previous range.
Can I manually edit the auto-hide range for the worksheet?	Yes. Normally, you would just use "Auto-Hide in Selected Range" button, but for some reasons you might want to edit the range manually. On the Excel toolbar, go to the Formulas* ribbon and select Name Manager and then edit the range* Because after such manual change there will be no calculation event, changes will not be automatically reapplied. To reapply them, you need to manually select just one of the controlled cells, click the pull down arrow on *Hide Zero Rows* and then click Auto-Hide / Unhide Rows in Selected Range. The changes will be reapplied across the entire controlled range, not just the selected cell.
Can I use the Auto-Hide feature on a protected worksheet?	Unfortunately, when a worksheet is protected, this locks Excel's hide feature from being able to modify that worksheet. Thus, Excel's protection feature is not compatible with the Velixo hide features.
How to forget all controlled ranges for the current worksheet?	From the Velixo ribbon, click the pulldown arrow for Unhide All and click Forget Auto Hide/Unhide Ranges
What if I want to temporarily disable auto-hiding while I make changes, but then restore the ranges?	Go to the Formulas* ribbon, select Name Manager and temporarily rename the VelixoAutoHideZeroRows and VelixoAutoHideZeroColumns ranges to something else.* When you're done, rename them back and see above for manually re-applying changes.