How to Check If a Cell Is Empty in Excel Program?

Written by kate bluest
  • Share
  • Tweet
  • Share
  • Pin
  • Email
How to Check If a Cell Is Empty in Excel Program?
Just a few clicks on the keyboard using the right Excel function can save you hours of time. (Manos en teclado 40409 image by pablo from Fotolia.com)

In large complex spreadsheets, locating empty cells requires more than a glance. There are many methods available to make the task of identifying empty cells easier. They include the three function methods called ISBLANK, LEN and IF.

The ISBLANK function returns the logical value of TRUE if the cell is empty. The LEN function returns a value containing the number of characters in a string or the number of bytes required to store a variable in a cell. If a cell is blank, that value will be zero according to Microsoft. You can use the IF function with empty quotes ("") to test for empty cells.

To clarify the formula examples, "B2" represents the cell containing the data you want to test. "E2" represents the cell in which you enter the formula. Use the values of your data in place of these cell addresses. To keep your formulas simple, If your headers are on a row, the row numbers should match, if your headers are on a column, the column letters should match.

Skill level:
Moderate

Other People Are Reading

Instructions

  1. 1

    Open the Excel worksheet with the data you want to check.

  2. 2

    Enter an appropriate header in the first empty column.

  3. 3

    Input "=isblank(B2)" on the row corresponding to the first data you want to check and press "Enter." This formula says, "Cell B2 is empty." If that statement is true, it returns a value of "TRUE." Otherwise, it returns a value of "FALSE."

  4. 4

    Copy the cell containing the formula. Select all the cells corresponding to the data you want to check. Paste the formula.

  5. 5

    Select the "Data" tab on the menu and select the "Filter" button.

  6. 6

    Click the filter arrow at the top of the column containing the formula. Select "TRUE" to see the rows or columns with empty cells.

  1. 1

    Open the Excel worksheet you want to check.

  2. 2

    Enter an appropriate header in the first empty column.

  3. 3

    Input---on the row corresponding to the first data you want to check---the following:

    =if(LEN(B2)<1, "EMPTY", "NOT EMPTY")

    Press "Enter."

    This formula says:

    If the value of LEN is less than 1 (O), then input "EMPTY," if the value of LEN is not less than 1(O) then input "NOT EMPTY."

  4. 4

    Copy the cell containing the formula. Select all the cells corresponding to the data you want to check. Paste the formula.

  5. 5

    Select the "Data" tab on the menu and select the "Filter" button.

  6. 6

    Click the filter arrow at the top of the column containing the formula. Select "TRUE" to see the rows or columns with empty cells.

  1. 1

    Open the Excel worksheet you want to check.

  2. 2

    Enter an appropriate header in the first empty column.

  3. 3

    Input---on the row corresponding to the first data you want to check---the following:

    =if(B2="", "EMPTY", "NOT EMPTY")

    Press "Enter."

  4. 4

    Copy the cell containing the formula. Select all the cells corresponding to the data you want to check. Paste the formula.

  5. 5

    Select the "Data" tab on the menu and select the "Filter" button.

  6. 6

    Click the filter arrow at the top of the column containing the formula. Select "TRUE" to see the rows or columns with empty cells.

Tips and warnings

  • Testing to see if a cell is empty is not the same as testing for a zero value. If a cell has a zero value, it is not empty. To test for zero values, use "=if(b2="0",0,1). This formula says if cell B2 equals zero, enter 0, else, enter 1.

Don't Miss

Filter:
  • All types
  • Articles
  • Slideshows
  • Videos
Sort:
  • Most relevant
  • Most popular
  • Most recent

No articles available

No slideshows available

No videos available

By using the eHow.co.uk site, you consent to the use of cookies. For more information, please see our Cookie policy.