FILTER and ISBLANK Function in Google Sheets (Example)

The FILTER and ISBLANK function in Google Sheets work well as a pair when a list has gaps and you only want the complete rows. FILTER returns every row that meets a condition, and ISBLANK tells it which cells are empty. Wrap ISBLANK in NOT and you get a live, clean copy of your data that skips any row with a missing value.

This guide uses a small sales list with Date, Cust . Name, P .ID, Product and Price columns, where four rows have no date. You will see the exact formula, how each part works, the result it returns, five useful variations and the errors people usually hit. It is written for anyone who cleans exported data, sales logs or form responses in Google Sheets.

Make a Copy of the Free Practice File

The practice file uses the same layout as the screenshots below, with a different set of sample names and dates, so you can test every formula yourself.

The Sample Data

The data sits in A3:E13. Row 3 holds the headers and rows 4 to 13 hold ten orders. The Date column is empty for Ariya, Reiss, Reegan and Lleyton, and those are the rows we want to drop.

Google Sheets sales list in A3:E13 with Date, Cust . Name, P .ID, Product and Price, four rows missing a date — Source data: ten orders, four of them without a date.

FILTER and ISBLANK Syntax

Here is what each function does on its own before we combine them.

Function / argument	What it means
`FILTER(range, condition1, [condition2, …])`	Returns the rows of range where every condition is TRUE.
`range`	The block of data to return, here `A4:E13` (no header row).
`condition1`	A column of TRUE/FALSE values the same height as range.
`ISBLANK(value)`	Returns TRUE if the cell is completely empty, FALSE otherwise. Given a range inside FILTER, it checks every cell.
`NOT(logical)`	Flips TRUE to FALSE and FALSE to TRUE, so “is blank” becomes “is not blank”.

The Formula That Removes Rows Without a Date

Click an empty cell below the data (the example uses A16, with headers copied into row 15) and enter:

=FILTER(A4:E13, NOT(ISBLANK(A4:A13)))

How it works, step by step

ISBLANK(A4:A13) checks each date cell and builds a list of ten TRUE/FALSE values. Rows 5, 8, 10 and 12 return TRUE because they are empty.
NOT(…) flips that list, so rows with a date become TRUE and empty rows become FALSE.
FILTER(A4:E13, …) keeps only the rows marked TRUE and spills them into the cells below and to the right of A16.

The Result

The formula returns six rows: Pheobe (4-Nov-2024, Mouse, 26767), Eliza (13-Jun-2024, Laptop, 26040), Perry (28-Nov-2024, Mobile, 27116), Alvin (29-Oct-2024, Moniter, 25035), Milena (30-Jun-2024, Mobile, 28986) and Miranda (16-Apr-2024, Camera, 27995). The four undated rows are gone, and the output updates automatically if you add a date to one of them later.

FILTER with NOT ISBLANK formula in cell A16 returning six dated orders in rows 16 to 21 — Output in A16:E21: only the six orders that have a date.

5 Practical Variations

1. Show only the rows that are missing a date

Drop the NOT to list the incomplete records you need to fix:

=FILTER(A4:E13, ISBLANK(A4:A13))

With the sample data this returns Ariya, Reiss, Reegan and Lleyton.

2. Use <>”” instead of NOT(ISBLANK())

This shorter version also treats empty text returned by a formula as blank, which ISBLANK does not:

=FILTER(A4:E13, A4:A13<>"")

3. Combine “has a date” with a second condition

Add more conditions after the first one. FILTER treats each extra condition as AND:

=FILTER(A4:E13, NOT(ISBLANK(A4:A13)), E4:E13>27000)

Result: Perry (27116), Milena (28986) and Miranda (27995).

4. Require two columns to be filled

Multiply the checks when several columns must contain a value:

=FILTER(A4:E13, (A4:A13<>"")*(D4:D13<>""))

5. Sort the clean list by date

Wrap the result in SORT to get the dated orders in order, oldest first:

=SORT(FILTER(A4:E13, NOT(ISBLANK(A4:A13))), 1, TRUE)

Miranda (16-Apr-2024) comes first and Perry (28-Nov-2024) comes last. For more on this pairing, see our guide to SORT and FILTER functions in Google Sheets.

Common Errors and How to Fix Them

Problem	Why it happens	Fix
#N/A – No matches are found in FILTER evaluation	Every row failed the condition.	Wrap it: `=IFERROR(FILTER(…), "No dated rows")`.
#REF! – Array result was not expanded	Cells where the result needs to spill already contain data.	Clear the area below and to the right of the formula cell.
#VALUE! – mismatched range size	The condition range has a different number of rows than the data range (for example A4:E13 with A4:A12).	Make both ranges start and end on the same rows.
A row looks empty but is not removed	The cell holds a space, an apostrophe or “” from a formula, so ISBLANK returns FALSE.	Use `TRIM(A4:A13)<>""` as the condition.

Tips for Using FILTER and ISBLANK

Leave the header row out of the FILTER range and type the headers above the output yourself, as in row 15 of the example.
Use open-ended ranges such as A4:E and A4:A when new rows are added often, so the formula picks them up automatically.
Count instead of listing: =COUNTA(A4:A13) returns 6 dated rows and =COUNTBLANK(A4:A13) returns 4.
FILTER results are read-only. To edit the cleaned list, copy it and use Edit > Paste special > Values only.
If you only need to hide rows on screen, a filter view may be enough. Our post on Filter and Filter View in Google Sheets explains the difference.

When to Use This Formula

The FILTER and ISBLANK combination is handy for cleaning order exports before a pivot table, removing unanswered form responses, preparing a mailing list that only includes rows with an email address, and building a “to fix” list of incomplete records for a colleague. The same pattern works with other checks too: our guides on FILTER and ISTEXT and FILTER and MONTH show two more conditions you can drop into FILTER.

If you want to build this kind of formula skill step by step, the 100 formulas course on NextGenTemplates Academy covers lookup, logical and filtering functions with practice files.

Frequently Asked Questions

What does ISBLANK return for a cell that contains a formula?

FALSE. Even if the formula returns an empty string (“”), the cell is not truly empty, so ISBLANK treats it as filled. Use A4:A13<>"" when your column contains formulas.

Can I use FILTER with ISBLANK on more than one column?

Yes. Add each check as a separate condition, for example =FILTER(A4:E13, NOT(ISBLANK(A4:A13)), NOT(ISBLANK(E4:E13))). Every condition must be TRUE for a row to appear.

How do I keep rows where either column is filled?

Add the checks together instead of listing them separately: =FILTER(A4:E13, (A4:A13<>"")+(E4:E13<>"")). The plus sign works as OR.

Does the FILTER result update automatically?

Yes. As soon as you type a date into one of the empty cells, that row appears in the output, and deleting a date removes it.

Is there a FILTER function in Excel too?

Yes, Excel 365 and Excel 2021 have FILTER, and =FILTER(A4:E13, A4:A13<>"") works the same way. Excel’s FILTER takes one condition argument, so combine several conditions with * or +.

Wrapping Up

With one formula, =FILTER(A4:E13, NOT(ISBLANK(A4:A13))), you turned a list with gaps into a clean, live table of six dated orders. Make a copy of the practice file, try the five variations above, then apply the same pattern to your own exports.