Over the years I have had regular requests for a technique to hide zero rows in reports. You can use macros but you can also use filters. Let’s see how you can implement a filter solution.

In the image below we want a hide rows 4 and 6. But we don’t want a hide row 8.

I’ve used helper cells on the left-hand side in column A to determine which rows to hide.

You can download the example file at the bottom of the page.

The formula in cell A2 (copied down) is

=IF(COUNTIF(D2:I2,0)=6,"Hide","Show")

How you create the formula will depend on what rules you want to apply.

In this case I am counting the number of zeros in the row and if there are six zeros then Hide is displayed. Otherwise Show is displayed.

If there were small values like one or two cents in the cells this formula would still display the row even though the value displayed might be zero if the format is rounded to zero decimal places.

You have to be careful using a SUM function to determine whether or not to hide the row because there may be a plus and minus that add to 0, but you would still need to display the row as you can see in row 8.

By applying a filter to column A you can hide the required rows, see image below.

The problem with this solution is that it is not dynamic.

For example if we adjust row eight and we get rid of the plus and the minus values the filtering doesn’t automatically update. See image below.

There is an icon or keyboard shortcut you can use to reapply the filter. In the Data ribbon tab there is a Reapply icon which has the keyboard shortcut Ctrl + Alt + L.

This reapplies the current filter on the current sheet. See image below.

If the values change in your report simply press Ctrl + Alt + L to reapply the filter.

Download Example File