Remove blank rows

In this example, the goal is to remove empty rows from a range with a formula. One approach is to use the BYROW function to identify all non-empty rows in the range and pass this result into the FILTER function as the include argument. This is the approach used in the worksheet shown, where the formula in cell G5 is:

=FILTER(B5:E16,BYROW(B5:E16,LAMBDA(row,SUM(--(row<>""))>0)))

Working from the inside out, the BYROW function is used to check for non-blank rows like this:

BYROW(B5:E16,LAMBDA(row,SUM(--(row<>""))>0))

The purpose of BYROW is to process data in an array or range in a "by row" fashion. At each row, BYROW applies a custom LAMBDA function that contains the calculation needed to achieve the desired result. In this case, we want to identify non-blank rows. In other words, we want to check for rows that contain content in any cell. This is done in the following snippet:

LAMBDA(row,SUM(--(row<>""))>0)

The BYROW function delivers the range B5:E16 row-by-row to the LAMBA above as the row argument. The LAMBDA then runs this calculation on each row:

SUM(--(row<>""))>0

The <> operator means "not equal to", so <>"" means "not empty". The result is an array of TRUE and FALSE values. In row 5, the result is an array like this:

=SUM(--({TRUE,TRUE,TRUE,TRUE}))>0

Next, the double negative (--) above converts TRUE to 1 and FALSE to 0:

=SUM({1,1,1,1})>0 // returns TRUE

The SUM function then returns 4, and the expression returns TRUE since 4 is greater than 0. Each row is processed in the same way, and BYROW returns all results in a single array like this:

{TRUE;TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE}

Notice the array contains 12 results because the original range contains 12 rows. In this array, TRUE indicates non-empty rows and FALSE indicates empty rows. The array is returned directly to the FILTER function as the include argument:

=FILTER(B5:E16,{TRUE;TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE})

FILTER returns the 9 rows that correspond to TRUE as a final result.

Simple option

The formula above is more complex because it tests every cell in a row to make sure it is empty. If you only need to test a single column to determine if a row is blank you can use a simpler formula like this:

FILTER(range,CHOOSECOLS(range,1)<>"")

In this formula, we use the FILTER formula as before. However, for the include argument we use the CHOOSECOLS function instead of BYROW:

CHOOSECOLS(range,1)<>"" // column 1 only

CHOOSECOLS runs first and extracts just the first column from the range. Then we simply test each cell in the column with <>"". Because each cell corresponds to a row, the result is a single array of TRUE and FALSE values that are used to filter the range. Adapting this formula to the example above, we have:

=FILTER(B5:E16,CHOOSECOLS(B5:E16,1)<>"")

After CHOOSECOLS is evaluated, we have the following:

=FILTER(B5:E16,{TRUE;TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE})

The final result is the same as the FILTER + BYROW formula above. Note however that we are only testing column 1, so rows that contain data in other columns will be discarded.

More details

• To simply identify blank and non-blank rows, see: Row is blank.
• For more on the Boolean logic used above, see Boolean operations in array formulas.