Function Problems

Identify and Filter Rows with Blank Cells in Excel

John WilsonJohn Wilson Nov 18, 2026 868 views

Question details

Identify and filter rows where column AA is blank and column D meets a text requirement.

Product
Microsoft Excel
Device & OS
not provided
Scenario
The user wants to review or summarize pending records on the Active Work worksheet without moving them to another sheet.
Observed behavior
Matching rows should be marked as Pending so they can be filtered or summarized.
Before you start

Open the Active Work worksheet and check which row contains the headers and which row contains the first record. Choose an empty column for the status; the examples below assume headers are in row 1 and data starts in row 2.

Solution 1Recommended

Add a status formula and filter pending rows in Excel

Use a helper column to mark matching records and filter them in place.

1
Add a status heading

On the Active Work worksheet, enter Status in an empty column in row 1.

2
Enter the formula

In the Status cell on row 2, enter =IF(AND(LEN(AA2)=0,LEN(D2)>0),"Pending","Active"). This marks the row as Pending when AA is empty and D is nonempty.

3
Set a text condition if needed

To require an exact value in D, use =IF(AND(LEN(AA2)=0,D2="John and Daisy"),"Pending","Active"). To match that text anywhere within D, use =IF(AND(LEN(AA2)=0,ISNUMBER(SEARCH("John and Daisy",D2))),"Pending","Active"). Replace the example text with your requirement.

4
Fill the formula down

Select the formula cell and drag its fill handle down through the last record, or double-click the fill handle if the adjacent column has continuous data.

5
Filter the status column

Select a cell in the data range, choose Data > Filter, and open the arrow in the Status heading. Select only Pending to display matching rows.

Add a status formula and filter pending rows in Excel
Blank cells and spaces: LEN(AA2)=0 also treats a formula returning an empty string as blank, but it does not treat a cell containing spaces as blank.
Work with pending records in WPS

Filter pending Excel records in WPS Spreadsheet

Open your workbook in WPS Spreadsheet, add a status formula, and filter the matching rows without moving them to another worksheet.

  1. 1. Open the workbook: In WPS Spreadsheet, open the Excel workbook and select the Active Work worksheet.
  2. 2. Create a status column: Enter Status in an empty header cell. In the first data row, enter =IF(AND(LEN(AA2)=0,LEN(D2)>0),"Pending","Active"), adjusting row 2 if your data starts elsewhere.
  3. 3. Apply the formula: Drag the formula cell's fill handle down to apply the status check to the remaining records.
  4. 4. Filter pending records: Select the data range, choose Data > Filter, and use the Status column's filter arrow to show Pending.
Mark and filter pending rows using a helper column.Open and save Microsoft Excel XLSX and XLS files.Use familiar spreadsheet formulas and filter controls.Work with a free, easy-to-use office suite.
microsoft office alternative - wps office

Frequently Asked Questions

How do I require a specific value in column D?

Replace LEN(D2)>0 in the formula with D2="John and Daisy", substituting your required value. This checks the entire cell rather than matching text within it.

How can I find text anywhere in column D?

Use ISNUMBER(SEARCH("John and Daisy",D2)) as the column D condition. SEARCH matches the text within the cell and is not case-sensitive.

Will the formula treat a cell with spaces as blank?

No. LEN(AA2)=0 treats an empty cell or an empty-string formula result as blank, but a cell containing spaces has a positive length.

Can I summarize pending records in a pivot table?

Yes. After filling the Status formula down, select the data range and choose Insert > PivotTable. Add Status to the pivot table and use it to filter or count Pending records.