Microsoft Office Tutorials and References
In Depth Information
3. Remove the check mark from Select All. All
items become unselected.
Searching for Blank Cells
Suppose you are a coin collector and you have your
existing coins and the coins you’d like to obtain all
in an Excel database list. One of your columns is
for the date you obtained the coins, so for the
coins you don’t yet have, the date obtained field
would be blank. By using the AutoFilter, you can
advise Excel to locate only the records where a
particular field (whether the house contains a
fireplace, in this example) is blank.
4. Click the entry or entries you want to filter
and then click OK. Excel displays only the
records that match your choice. In Figure
11-30, for example, you see only the Condos.
Other record rows are hidden. Also notice
that the filter arrows on filtered columns
take on a different appearance to indicate
that a filter is in use.
Make sure the AutoFilter option is on and your
database columns contain filter arrows. Click the
arrow in the column heading where you want to
find a blank cell. Remove the check mark from
Select All and then scroll to the bottom of the list
and check the Blanks entry. The Blanks entry only
appears if the selected field actually has any blank
entries. It should be the only one selected. Click
OK, and Excel displays only the records with blank
cells in the column you selected, which in Figure
11-31 is the Fireplace column.
To sort any column, click the column filter
arrow and choose a sort option.
Locating records with blank data.
Filter Non-Blanks Only
From the column containing the filtering you want
removed, click the column filter arrow and choose
Clear Filter from “field name.” If you want to turn
off the AutoFilter, choose Data>Sort &
Filter>Filter. You can turn the AutoFilter on and
off as often as you need to.
To filter for only non-blank values, make
sure Select All is chosen in the AutoFilter
menu at the top of the list of values. Then,
at the bottom of the list, remove the check
mark from Blanks.