How to Extract Blank Cells in an Excel Database

ID: Q64805

The information in this article applies to:

Microsoft Excel for Windows, versions 2.x, 3.0, 4.0, 4.0a, 5.0
Microsoft Excel for OS/2, versions 2.2, 3.0
Microsoft Excel for the Macintosh, versions 2.x, 3.0, 4.0, 5.0

SUMMARY

In Microsoft Excel, you can extract blank cells from a database using either comparison criteria or computed criteria.

NOTE: To extract nonblank entries, use ="<>" in the criteria range where blank cells in a specific field must be ignored for the extract.

MORE INFORMATION

To Extract Blank Cells from a Database Using Comparison Criteria

Type = under the appropriate heading for the criteria, as in the following example:


   Database            Criteria             Extract
   ----------------------------------------------------------

   A1: Name   B1: Age  C1: Name  D1:  Age   E1: Name  F1: Age
   A2: Joe    B2:  20  C2:       D2:  =     E2: Mary  F2:
   A3: Mary   B3:      C3:       D3:        E3:       F3:
   A4: Mike   B4:  35  C4:       D4:        E4:       F4:
   A5: John   B5:  40  C5:       D5:        E5:       F5:

   The name Mary is the only extracted name; Mary is the only
   one who doesn't have an Age listed.

To Extract Blank Cells from a Database Using Computed Criteria

Using computed criteria, you must use the cell reference immediately below the field for which you are testing. For example, if the field name that you want to apply the criteria against is in cell B1, the following is the computed criteria:


   Database             Criteria             Extract
   -----------------------------------------------------------

   A1: Name   B1: Age  C1: Name  D1:  Age    E1: Name  F1: Age
   A2: Joe    B2:  20  C2:       D2: =B2=""  E2: Mary  F2:
   A3: Mary   B3:      C3:       D3:         E3:       F3:
   A4: Mike   B4:  35  C4:       D4:         E4:       F4:
   A5: John   B5:  40  C5:       D5:         E5:       F5:

Enter the computed criterion (=B2=""), and the cell that contains the criterion will return a value of TRUE or FALSE.

Delete the cell directly above the computed criterion, and then highlight your criterion.

From the Data menu, choose Set Criteria.

Highlight the extract range and from the Data menu, choose Extract.

Note that the function ISBLANK() does not work correctly when used as a computed criterion.

To Omit Records in Which Every Field Is Blank

The following formulas assume that you are using a database situated in rows A1:D20 with headers in row 1.

If you are using computed criteria, use this formula:


   =NOT(AND(A2="",B2="",C2="",D2=""))

If you are using comparison criteria, set up the criteria range like this:


   E1:  header1 F1:  header2   G1:  header3  H1:  header4
   E2:  ="<>"   F2:            G2:           H2:
   E3:          F3:  ="<>"     G3:           H3:
   E4:          F4:            G4:  ="<>"    H4:
   E5:          F5:            G5:           H5:  ="<>"

Additional query words: 2.0 2.00 2.01 2.1 2.10 2.20 2.21 3.0 4.00a 5.0 autofilter

Keywords :
Version :
Platform :
Issue type :