Filter rows with zeros

When presenting data in Excel, sometimes a templated layout is used. To focus on the data, it is useful to be able to hide rows with zero values.

If there is only one column of numbers, a simple filter can be used. It gets more complex if there are multiple columns of numbers.

For added complexity in this example, rows with headings or spacer rows shouldn’t be hidden.

To filter the data a single helper column is added, that returns a one or a zero. Any rows with a zero value will be filtered. Using the sample data, only a single row, (row eight), will be hidden.

Three formulas to test the rows are going to be developed separately and then combined into a single formula.

ABS

The first formula uses ABS that returns the absolute value of a number without its sign. As the formula references a range of cells, it returns an array. The return array will usually spill across multiple cells. To get a single number, the formula is enclosed in a SUM function.

An IF function then checks to see if the SUM returns a zero. If it does, then a zero is returned, to hide the row. If it doesn’t, then a one is returned, to show the row.

=IF(SUM(ABS(D2:G2))=0,0,1)

This formula works for most rows but fails when the row contains text. It returns zero instead of one for empty spacer rows.

ISTEXT

The second formula uses ISTEXT to check if the cells contain text. As the formula references a range of cells, it returns an array of TRUE and FALSE values. To get a single number, the formula is enclosed in a SUM function.

As the array of TRUE and FALSE values cannot be summed by the SUM function, the Boolean values are coerced into 1 and 0 values by adding – before the ISTEXT function.

The double-dash (–) is a double unary operator. It coerces Boolean values to numeric values. TRUE becomes 1 and FALSE becomes zero.

An IF function then checks to see if the SUM returns more than zero. If it does, then a one is returned, to show the row. If it doesn’t, then a zero is returned, to hide the row.

=IF(SUM(--ISTEXT(D2:G2))>0,1,0)

The formula works on text rows but not on any of the other rows.

ISBLANK

The third formula uses ISBLANK to check if the cells are blank. As the formula references a range of cells, it returns an array of TRUE and FALSE values. To get a single number, the formula is enclosed in a SUM function.

As the array of TRUE and FALSE values cannot be added together with SUM, the Boolean values are coerced into 1 and 0 values by adding double-dash (–) before the ISTEXT function.

An IF function then checks to see if the SUM returns four, the number of columns in the range. If it does, then a one is returned, to show the row. If it doesn’t, then a zero is returned, to hide the row.

=IF(SUM(--ISBLANK(D2:G2))=4,1,0)

The formula works on blank rows but not on any of the other rows.

Combined

The three formulas are combined into a single formula.

The order that they are combined in, is important.
The first two IF statments should return a one if true or continue testing if false.
The final IF statement will return a zero if true, otherwise a one.

=IF(SUM(--ISBLANK(D2:G2))=4,1,
   IF(SUM(--ISTEXT(D2:G2))>0,1,
     IF(SUM(ABS(D2:G2))=0,0,1)))

Filter

Add a heading to the helper column.

To add a filter, select the results including the heading.
On the Home menu, select the Sort & Filter dropdown and click on Filter.

A dropdown filter will be added to the heading cell.

Set the print area

Select the area that you want to print.
On the Page Layout menu, click on Print Area and select Set Print area.

Apply the filter

Click on the dropdown in the header row of the helper column and untick the zero value.

Print

On the File menu, click on Print.
Any row in the helper column with a value of zero will not be displayed or printed.

In this example, Row 8 for “Central” will be hidden as it’s values are all zero.
The headings, blank rows and rows with values other than zero are all displayed.