Hide Zero Rows / Columns

Overview

The Velixo Hide Zero Rows and Hide Zero Columns features make reports tidier and more readable by hiding rows or columns that contain only zero values. You can hide once manually, or set auto-hide criteria that Velixo reapplies on every calculation and refresh.

This article covers:

• Hiding zero rows and columns one time (Basic hiding)

• Setting auto-hide criteria that persist across refreshes (Auto-hide)

• Revealing hidden data with Unhide All

• Permanently clearing auto-hide criteria with Unhide All + Forget Auto-Hide Ranges

• How hidden cells behave during writeback

• Pausing auto-hide while you make changes (Pause Auto-Hide)
  

Basic Hiding

To hide zero rows or columns once, select a range of rows, an individual column, or multiple columns, then click Hide Zero Rows (or Hide Zero Columns). Velixo hides every row where all selected columns are zero. Rows with an empty value are ignored, so the layout of your report is not affected.

To reveal all hidden rows and columns, use Unhide All:

You can also press Ctrl + Shift + 9, use the Unhide command in Excel's right-click menu, or use the Format menu in the Cells group of the Home ribbon tab.

Auto Hide

With basic hiding, you have to unhide rows before refreshing a report, then re-hide the rows that now contain zero values. Auto-hide removes that manual step: you set the hiding criteria once, and Velixo hides zero rows and reveals non-zero rows automatically as your report's values change.

Access auto-hide from the pulldown on the Hide Zero Rows button, then select Auto Hide Rows in Selected Range (or, for columns, Auto Hide Columns in Selected Range from the Hide Zero Columns pulldown). Velixo remembers the area to which you apply the criteria, called the controlled range. On every workbook calculation and every Velixo Refresh, Velixo adjusts which rows are hidden based on the current values.

The scope of an auto-hide range is the active worksheet.

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.

Unhide All

The Velixo Unhide All feature reveals all rows and columns hidden in the active worksheet.

When you have used auto-hide, Unhide All reveals the hidden rows and columns temporarily. When Excel next recalculates the workbook, the rows and columns that match the saved criteria are hidden again.

To keep hidden rows and columns visible while you make several changes, use Pause Auto-Hide instead.

Pause auto-hide

When you use Unhide All on a report with auto-hide, each change you make can trigger a recalculation, and Velixo hides the matching rows and columns again. To make several changes without this, pause auto-hide. Pausing reveals the hidden rows and columns and keeps your auto-hide ranges, so you can resume later without redefining them.

To pause auto-hide:

1. On the Velixo ribbon, click the pulldown arrow on Unhide All.

2. Select Pause Auto-Hide.

Velixo reveals the auto-hidden rows and columns on every worksheet that has an auto-hide range, not only on the active worksheet. While auto-hide is paused, you can edit the report and refresh it without Velixo hiding anything. Pause Auto-Hide is unavailable until you resume auto-hide.

While auto-hide is paused, Hide Zero Rows and Hide Zero Columns still hide rows and columns once when you select them. Unhide All + Forget Auto-Hide Ranges removes the active worksheet's auto-hide ranges but does not resume auto-hide.

The paused state is saved with the workbook, so auto-hide stays paused after you save, close, and reopen the file.

To resume auto-hide, do either of the following:

• Select Reapply Auto-Hide Logic from the Hide Zero Rows or Hide Zero Columns pulldown.

• Set a new auto-hide range on any worksheet with Auto Hide Rows in Selected Range or Auto Hide Columns in Selected Range.

Velixo then reapplies auto-hide on every worksheet that has an auto-hide range.

You can also ask Velixo Intelligence to pause or resume auto-hide.

Forget auto-hide Ranges

To permanently unhide auto-hidden data, select Unhide All + Forget Auto-Hide Ranges from the Unhide All pulldown. This both reveals the hidden rows and columns and removes the saved auto-hide criteria, so they stay visible on later calculations and refreshes.

Unhide All + Forget Auto-Hide Ranges applies to the active worksheet only. If you have applied auto-hide across several worksheets, switch to each one and run Unhide All + Forget Auto-Hide Ranges separately to clear its criteria.

Hidden cells and Writeback

Hidden cells behave in the following way when using the Writeback functionality:

• Current Sheet – uploads all lines in the currently active worksheet, including hidden rows.

• Selected Cells – only uploads data from cells that are selected, hidden rows excluded.

• Multiple Sheets - uploads all lines in the selected worksheets, including hidden rows.

FAQ