In my above topic, get Rows excluding rows with any blank cells, the word “Any” has a special significance. What I meant by the topic is as below.

Suppose I have a data range A1:Z5. That means there are 5 rows. The first row is A1:Z1. If all the cells in this row are filled, I want to copy that row.

If any of the cells, for example, cell B1 has no value, I don’t want to get that row.

To make it clear to you here is one example.

In this dataset, there are total 4 rows that excluding the title row. In that only in row # 3 and 5 have values in all the columns.

Then how to get the Rows excluding rows with any blank Cells in Google Sheets. I mean how to get the row # 3 and 5 in this example.

There are different solutions. If the number of columns is limited, you can use the Query function in Google Sheets.

If you are looking for a more flexible solution that can include any number of columns, there is one combination formula.

You can get both the formulas here.

The Formulas to Get Rows Excluding Rows with Any Blank Cells in Google Sheets

Here is the Query formula that can filter out any rows with blank cells in it.

Formula 1 to Copy Rows without Any Blank Cells:

=query(A1:E,”Select * where A is not null and B is not null and C is not null and D is not null and E is not null”)

This formula has a drawback. When the number of columns is more, this formula becomes very long.

Right now there are only 5 columns in my sample data from A to E. So this formula is OK. But I have an alternative formula, that can use on any number of columns.

Before going to that awesome Google Sheets formula, that only copy/extract rows with full values, below is the screenshot of the above formula result.

Formula 2 to Copy Rows without Any Blank Cells:

This is a combo formula that can effectively exclude rows with any blank cells in it. In this MMULT is the main function in use.

=ArrayFormula(query({(mmult((if(len(A2:E),1,0)),transpose(column(A2:E2)^0))),A2:E},”Select * where Col1=5“))

How to use this Google Sheets Combo formula on my sheet?

If you want to use the above formula to get rows excluding rows with any blank cells in Google Sheets, you can tweak it as below.

Just change the column Heading, here it is “E”, to the last column in your range.

Change the number 5 to the number of columns in your range.

If your data range is A1:Z100, the formula would be as follows.

=ArrayFormula(query({(mmult((if(len(A2:Z),1,0)),transpose(column(A2:Z2)^0))),A2:Z},”Select * where Col1=26″))

Just see the difference in both the formulas to understand it.

The Real Life Use of Excluding Rows with Blank Cells

If you have landed on this page via search, then you may be satisfied with the above tips. But if you are one of my regular readers and learning new things in Google Sheets, you may want to know the purpose.

I can give you one real-life use of the above formula.

In Column A I have the name of four candidates. In adjoining columns, that’s from column B to F, against each candidate you can see some tick boxes.

Among them, some are ticked and some are not. If a tick box is ticked in any column, that means the candidate has attended that specific test. There are total 5 tests.

I know you can easily understand it from the screenshot itself.

The challenge here is I want to lookup the name of the candidates who have completed all the tests. How?

It’s easy if you know the logic behind the Tick Boxes that you can insert in Google Sheets either via Insert Menu or Data > Data Validation.

Must Read: Insert CheckBox and Tick Mark in Google Sheets

When you click a tick box, the value in that cell become the Boolean TRUE else it’s FALSE. You can make some adjustment to our earlier formulas to use them for this use.

Earlier what we have done is, we copied the rows where there are no blank cells in that row. Here, the Boolean FALSE replaces the blank cells.

We want to copy all the rows, that’s clicked. That means all the rows with the tick boxes are ticked.

Formula 1 to Copy the Rows If All the Tick Boxes are Ticked (No FALSE values)

Query Formula.

=query(A1:F,”Select A where B = TRUE and C = TRUE and D = TRUE and E = TRUE and F = TRUE”,1)

This formula would return the candidate name “Annie” as she is the only one candidate who attended all the 5 tests.

Formula 2 to Copy the Rows If All the Tick Boxes are Ticked (No FALSE values)

=ArrayFormula(query({A2:A,mmult((if(B2:F=TRUE,1,0)),transpose(column(B2:F2)^0))},”Select Col1 where Col2=5“))

This combo formula is also in line with my earlier formula 2 above. But I know I should explain how to use it on any range.

Here also change the column heading F with the last column heading in your data range. But there is one difference in the value 5.

This time the column count to be started from B to F. Because you only should consider the columns with Tick Boxes in the count.

So if your data range is A2:Z100, then you should replace the F with Z and 5 with 25.

You May Also Like: Assign Values to Tick Box and Total It in Google Sheets

Conclusion

Hope you have enjoyed this tutorial on how to get Rows excluding rows with any blank Cells in Google Sheets. Pay special attention to the tick box section which is interesting and useful.