See all How-To Articles

How to Hide Blank Rows in Excel & Google Sheets

This tutorial demonstrates how to hide blank rows in Excel and Google Sheets.

 

hide blank rows initial data

 

Filter to Hide Blank Rows

You can hide blank rows using filters and a helper column. Say you have the following data set.

 

hide blank rows initial data

 

You want to hide Rows 4 and 7, as they are completely blank. To do this, you’ll need a helper column to indicate if a row is blank using the COUNTA formula.

1. Add a new column (F) which will serve as a helper column for filtering blank rows. In cell F2 enter the formula:

This formula returns the number of non-blank cells in the given range (in this case, in B2:E2). If you get zero in any cell, that means the row is blank, because there are no non-blank values.

hide blank rows 1

 

 

2. Now position the cursor in the bottom right corner of cell F2 until a black cross appears, and drag it through the last row of the data range (Row 8).

 

hide blank rows 2

 

3. Now the formula is in the range F2:F8. Filter out all zero values to hide blank rows. First, turn on the filter for Column F (Blank). Select any cell in the column (in this case, F1), and in the Ribbon, go to Home > Sort & Filter > Filter.

 

hide blank rows 3

 

4. To filter out blank (zero) values, (1) click on the filter icon in the F1 cell, (2) uncheck 0, and (3) click OK.

 

hide blank rows 4

 

As a result, all blank rows (in this case 4 and 7) are hidden. You can see that rows numbers are blue and hidden rows heading are missing. This shows that some rows in the worksheet are hidden.

 

hide blank rows 5

 

Note: You can also use VBA code to hide or delete blank rows.

Hide Blank Rows in Google Sheets

You can also hide all blank rows in Google Sheets.

1. Once you insert the formula (=COUNTA(B2:E2)) in Column F, turn on the filter by going to Data > Create a filter.

 

google sheets hide blank rows

 

2. To filter out zero values, (1) click on the filter icon in the F1 cell, (2) uncheck 0, and (3) click OK.

 

google sheets hide blank rows 2

 

All blank rows are now hidden.

 

google sheets hide blank rows 3