Identify and Filter Rows with Blank Cells in Excel
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.
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.
Add a status formula and filter pending rows in Excel
Use a helper column to mark matching records and filter them in place.
On the Active Work worksheet, enter Status in an empty column in row 1.
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.
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.
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.
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.

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. Open the workbook: In WPS Spreadsheet, open the Excel workbook and select the Active Work worksheet.
- 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. Apply the formula: Drag the formula cell's fill handle down to apply the status check to the remaining records.
- 4. Filter pending records: Select the data range, choose Data > Filter, and use the Status column's filter arrow to show Pending.

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.




