How to use ISBLANK functions in Excel formulas

ISBLANK in Excel is used for the presence of textual, numeric, logical, and other types of data in the specified cell and returns a boolean value of TRUE if the cell is empty. If the specified cell contains any data, the result of executing the ISBLANK function is the logical value FALSE.



Examples of using ISBLANK function in Excel

Example 1. The Excel spreadsheet contains the results (points) for the exam, which was held in an educational institution. In this electronic statement, in front of some students, the grades are not indicated, because they were sent for retake. In the column next to display the text line "Passed" in front of those who were given marks, and "For rerun" - on the contrary, those who did not pass the first time.

Initial data:

Example 1.

Select the cells C3:C18 and write the following formula:

The formula IF validates the returned result of the ISBLANK function for a range of cells B3: B18 and returns one of the options ("For rerun" or "Passed"). The result of the formula:

ISBLANK.

Now part of this formula can be used for conditional formatting:

  1. Select the range of cells C3: C18 and select the tool: “HOME”-“Styles”-“Conditional Formatting”-“New Rule”.
    2. Conditional Formatting.
  2. In the “New Formatting Rule” window that appears, select the option: “Use formulas to determine which cells to format” and enter the following formula:
    3. New Formatting Rule.
  3. Click on the “Format” button (as on the sample), then specify in the “Format of cells” red fill color and click OK on all open windows:
    4. using ISBLANK function.

On against empty cells or twos, we receive a corresponding message “For rerun” and a red fill.



Why do you need to use ISBLANK function when checking empty cells

In the above example, you can change the formula using double quotation marks ("") in place of the function of checking cells for emptiness, and it will also work:

=IF(OR(B3="",B3=2),"For rerun","Passed")

But not always! It all depends on the values that the cells may contain. Pay attention to how double quotes behave differently, and the function is ISBLANK if we have the same specific values in the cells:

need to use ISBLANK function.

As you can see in the picture in the cell is a single quote symbol. The first formula (with double quotes instead of a function) does not see it. Moreover, in the A1 cell itself, the single quote is not displayed because this special character in Excel is intended to display values in text format. This is convenient, for example, when we need to display the formula itself, and not the result of its calculation as done in cells D1 and D2. It is enough just to enter a single quote before the formula and now the formula itself is displayed, and not the returned result. But ISBLANK function sees that in fact the A1 cell is not empty!

Check for empty cell in Excel spreadsheet

Example 2. In the Excel spreadsheet recorded some data. Determine whether all fields are filled, or there is at least one field that is empty.

Source data table:

Example 2.

To determine if there are empty cells, use the following array formula (CTRL + SHIFT + Enter):

The SUM function is used to determine the sum of the values returned by the --ISBLANK function for each cell in the B3: B17 range (numeric values, since double negative is used). If the entry SUM(-BIDO(B3:B17) returns any value> 0, the IF function returns TRUE.

Result of calculations:

Check for empty cell.

That is, in the range B3: B17 there is one or more empty cells.

Note: in the above formula, the characters "-" were used. This type of record is called double negation. In this case, double negation is needed to explicitly convert data of a logical type to numeric. Some Excel functions do not perform automatic data conversion, so the type conversion mechanism has to be started manually. The most common options for converting textual or logical values to a numeric type is multiplication by 1 or adding 0 (for example, = TRUE + 0 returns the number 1, or = "23" * 1 returns the number 23. However, using the record type = -TRUE speeds functions (according to some estimates, productivity gains up to 15%, which is important when processing large amounts of data).

How to count the number of empty cells in Excel

Example 3. Calculate the average age of office workers. If the table does not contain all the fields, display the corresponding message and do not perform the calculation.

Data table:

Example 3.

Formula for calculation (array formula):

The IF function performs a range check for the presence of empty cells (expression SUM(--ISBLANK(B3:B12)). If the SUM returned a value> 0, a message will be displayed containing the number of empty data cells (COUNTBLANK) and the string “fields not filled in”, which are glued together with a “&” (concatenation operation).

Result of calculations:

count number of empty cells.

Features of the use of ISBLANK function in Excel

ISBLANK function in Excel is among the logical functions (performing a check of some condition, for example, IF, ISREF, ISNUMBER, etc., and returning results in the form of data of logical type: TRUE, FALSE). Syntax function recording:

=ISBLANK(value)

A single argument is required and can accept a reference to a cell or to a range of cells in which it is necessary to determine the presence of any data. If the function accepts a range of cells, the function should be used as an array formula.

Download examples of ISBLANK function in Excel

Notes:

  1. If a value was explicitly passed as an argument to the function (for example, =ISBLANK(TRUE), =ISBLANK("text"), =ISBLANK(12)), the result of its execution is FALSE.
  2. If you want the function to return TRUE if the cell is not empty, you can use it with the NOT function. For example, = NOT(ISBLANK (A1)) returns TRUE if A1 is not empty.
  3. A record of type = ISBLANK(ADDRESS(x,y)) will always return false, because the ADDRESS(x,y) function returns a reference to a cell, that is, a non-empty value.
  4. The function returns the value FALSE even in cases where the cell passed in as an argument contains an error or a cell reference. This judgment is also valid for cases when, as a result of the execution of a formula, an empty line is displayed in the cell. For example, the formula =IF(2>1,"", FALSE) was entered in cell A1, which returns the empty string "". In this case, the function =ISBLANK(A1) returns the value FALSE.
  5. If you need to check several cells at once, you can use the function as an array formula (select the required number of empty cells, enter the formula "=ISBLANK(" and pass the range of the studied cells as an argument, use Ctrl + Shift + Enter as an argument)

en ru