How do I filter out if all cells, or we can say, the entire row is blank in Google Sheets? I don’t want to use any helper column for this.
Usually, we decide a row is blank if the first cell in that row is blank. Because, most of the time, the first column in the table may contain timestamps or IDs.
But in our case, we don’t want to follow that. We want to check if any cell in a row has a value. Then return that row.
For example, I have a sample table filled with my birding data in Google Sheets.
The fields are “Scientific Name,” “Common Name,” “Locale,” “State,” “Number of Occurrences,” and “Date.”
The range is A1:G.
We usually use one of the following FILTER formulas to filter out rows if “Common Name” is blank.
LEN Approach:
=filter(A1:G,len(A1:A))ISBLANK Approach:
=filter(A1:G,isblank(A1:A)=false)Comparison Operator Approach:
=filter(A1:F,A1:A<>"")We want to filter out rows in the table if the entire row is blank. There are two methods: Dynamic and Non-Dynamic.
Filter Out If All Cells in a Row Is Blank: Non Dynamic Formula
Here is my sample birding data.
In this, we can use the following FILTER formula in cell I1 to filter out if the entire row is blank in a range. There are two such rows in the above table: A5:G5 and A7:G7.
=filter(A1:G,(A1:A<>"")+(B1:B<>"")+(C1:C<>"")+(D1:D<>"")+(E1:E<>"")+(F1:F<>"")+(G1:G<>""))
We can also implement the LEN and ISBLANK approach quite similarly above. I know you can do that easily.
But here is an interesting one that you can also try.
=ArrayFormula(filter(A1:G,len(A1:A&B1:B&C1:C&D1:D&E1:E&F1:F&G1:G)))None of the above are dynamic formulas to filter out if all cells in a row are blank because we require to specify the columns individually.
A non-dynamic formula has two disadvantages.
- You should specify 26 columns in the formula individually if your range is A1:Z. Do you like that?
- Specifying columns naturally won’t work if you want to remove blank rows if the range (table) is an expression (the output of another function).
Can you explain it (point # 2) with an example?
Yep! Since we don’t have an output of another formula, let’s make the range A1:G an expression by wrapping it with open clause curly brackets.
=filter({A1:G},(CHOOSECOLS(A1:G,1)<>"")+(CHOOSECOLS(A1:G,2)<>"")+(CHOOSECOLS(A1:G,3)<>"")+(CHOOSECOLS(A1:G,4)<>"")+(CHOOSECOLS(A1:G,5)<>"")+(CHOOSECOLS(A1:G,6)<>"")+(CHOOSECOLS(A1:G,7)<>""))We have used CHOOSECOLS to take out columns individually for the test. It requires a bit of patience when the number of columns is large.
Filter Out If the Entire Row Is Blank: Dynamic Formula
Whether it’s an expression or range, we can use the following formula to filter out a row if the entire row is blank.
=filter(A1:G,len(trim(transpose(query(transpose(A1:G),,9^9)))))It’s a dynamic formula since we can refer to the range directly; no need to take out columns using CHOOSECOLS or some other function and test for blank.
Replace A1:G with {A1:G} in the above formula. It won’t have any issues.
That means the formula works equally well with a physical range and a formula output (expression).
How Does It Work (Especially the Query Part)?
In the above example, I used QUERY to join columns dynamically. So I could test using LEN whether the joined columns have any value.
It works like this.
QUERY Syntax: QUERY(data, query, [headers])
Where;
data: transpose(A1:G)
query: nil
headers: 9^9
By specifying 9^9 (an arbitrarily large number) in the headers, we tell the QUERY function that all the rows in the data are headers. So it combines them into one row.
While doing so, we must use transposed data. Otherwise, QUERY will combine values in columns, not in rows.
You can learn it by applying query(A1:G9,,9^9) in your Sheet.
You May Like: Filter Out Blank Columns in Google Sheets Using Query Formula.
