Hide Zero Columns
Harry Lewis avatar
Written by Harry Lewis
Updated over a week ago


Using Velixo's Hide Zero Columns feature makes your reports tidier and more readable by automatically hiding columns that only have zero values.

Basic Hiding

To use this function, just select a range of columns, an individual row or multiple rows and click Hide Zero Columns:

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

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

To show columns that are hidden, press Ctrl + Shift + 0 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 columns before refreshing your report and then re-hide those columns 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 columns and un-hide non-zero columns when your report is refreshed.

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


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


In the example, the original report show some zero values in row 8. By selecting cells D8 through N8 and then clicking Auto Hide / Unhide Columns in Selected Range from the Velixo ribbon, the zero-value columns in that range 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 changed with new data (as shown by the change in column K), Velixo automatically adjusts which columns are hidden, based on the new values in the report.

Auto Hide FAQ:

When does Velixo auto-hide/unhide controlled ranges?

Upon every workbook calculation (e.g. when you update a formula) as well as Velixo Refresh

What is the scope of auto-hiding of the range I select?

The current worksheet

Is it better to select entire rows or just the cells in each row to which I want to apply the Auto-Hide feature?

Because Excel allows over 16000 columns in any row, applying the Auto-Hide feature to all columns in multiple rows can take a while to evaluate. Must faster results can be obtained by selecting only the region of cells in which you can reasonably expect data to be contained.

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

Does "Auto-Hide in Selected Range" overwrite the previous range I selected for auto-hiding?

No, it combines the newly selected range with the already controlled ranges, now controlling auto-hiding / unhiding in the new united range

Can I manually edit the auto-hide range for the worksheet?


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 do 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 back and see above for manually re-applying changes.

Did this answer your question?