Microsoft KB Archive/64805

{|
 * width="100%"|

How to Extract Blank Cells in an Excel Database

 * }

-

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 =&quot;<>&quot; in the criteria range where blank cells in a specific field must be ignored for the extract.

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=&quot;&quot;  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:


 * 1) Enter the computed criterion (=B2=&quot;&quot;), and the cell that contains the criterion will return a value of TRUE or FALSE.
 * 2) Delete the cell directly above the computed criterion, and then highlight your criterion.
 * 3) From the Data menu, choose Set Criteria.
 * 4) 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=&quot;&quot;,B2=&quot;&quot;,C2=&quot;&quot;,D2=&quot;&quot;))

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

  E1:  header1 F1:  header2   G1:  header3  H1:  header4 E2: =&quot;<>&quot;   F2:            G2:           H2: E3:         F3:  =&quot;<>&quot;     G3:           H3: E4:         F4:            G4:  =&quot;<>&quot;    H4: E5:         F5:            G5:           H5:  =&quot;<>&quot; 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 :

Technology :