How to Extract Blank Cells in an Excel Database

Last reviewed: May 9, 1995
Article 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 "=" (without the quotation marks) 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:  ="<>"

KBCategory: kbusage
KBSubcategory:
Additional reference words: 2.0 2.00 2.01 2.1 2.10 2.20 2.21 3.0 3.00
4.0 4.00 4.00a 5.0 5.00 autofilter

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: May 9, 1995
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.