A few weeks ago, I showed you how to use And and Or operators with Excel’s Advanced Filter feature. The examples I used searched for literal values, but what if you’re searching for a literal value in one field and/or empty cells in another. You’d still use the And and Or operators in the same way, only the criteria to find blank cells would change.
To find blank cells using an Advanced Filter, use a single equal sign as your criteria expression. Let’s look at a quick example using the same setup from a few weeks ago. As you can see below, I deleted a Units In Stock value from three records (rows 10, 18, and 22) simply to force the example to work because there were no empty cells. The criteria range contains two criteria expressions using an implicit And operator (because they’re in the same row). This filter will retrieve all records where the Unit Price is equal to or greater than 20 and the Units In Stock value is blank.
Run the filter as follows:
- Click any cell in the data range.
- Click the Data menu, and then click Filter | Advanced Filter. In Excel 2007 and 2010, click the Data tab and then click Advanced Filter in the Sort & Filter group.
- Retain the default setting; Filter the List In-Place.
- Excel automatically fills in the List Range, correctly in this case.
- Specify the Criteria range, A1:F2. You only need to identify the column headings.
- Click OK. The filter found just two records that meet both criteria expressions.
Now, let’s use the same criteria expression with an implicit Or operator. To do so, simply move the Units In Stock criteria expression (the equal sign) from D2 to D3. Then, run the filter again, making sure to update the Criteria Range from $A$1:$F$2 to $A$1:$F$3. The results are quite different this time. Many records satisfy one or the other criteria.
A word to the wise: A single equal sign will not match records where the blank cell isn’t truly blank, but only appears blank as the result of a formula or function. In this case, use the following criteria expression: =””. That’s an equal sign and two double quotation marks with no space characters.
