What is ISBLANK Function?
ISBLANK function used to check whether a cell is empty or not. Since this is an information function, it always returns a Boolean value, true or false. If the cell contains a value it will return false and true will be returned if it is not empty.
ISBLANK function in excel is grouped under information function. Information functions help to take a decision based on their results. You may come across a situation where you want to find the blank cells in an excel cell.
In this tutorial, you will learn:
- How to use the ISBLANK function?
- Example 1 How to use ISBLANK?
- Example 2: How to use ISBLANK with different excel functions?
- Example 3: How to use ISBLANK function with conditional formatting?
How to use the ISBLANK function?
Within a large range of cells when you want to find the blank cell ISBLANK function is the better option.
It is also used along with other functions and some formatting methods in Excel.
The formula for ISBLANK function
This is a simple function in excel, and the format is.
=ISBLANK(Value)
Where Value can be a cell reference
Example 1 How to use ISBLANK?
In following excel, given is the status of some orders. Order number and its delivery date are given. Let’s find the orders which are not yet delivered.
| S# | Order_Number | Delivery_ Date |
|---|---|---|
| 1 | 127504425 | 24-Nov-16 |
| 2 | 125387159 | 22-Dec-16 |
| 3 | 118531884 | |
| 4 | 115178509 | 29-Dec-16 |
| 5 | 120962192 | 13-Jan-17 |
| 6 | 118427223 | 10-Feb-17 |
| 7 | 119727660 | 14-Feb-17 |
| 8 | 119787660 | 16-Feb-17 |
| 9 | 126387159 | |
| 10 | 157504425 | 15-Jun-16 |
Here you can consider the orders which do not have a delivery date marked can be considered as not yet delivered. So can apply the formula ISBLANK to find the blank cells in the column delivery_date.
The format is ‘=ISBLANK(value)’ for the value you can select the column delivery date corresponding to each order numbers.
So, the formula will be as given in the formula bar that is ‘ISBLANK(C2)‘ where C2 refers to the delivery date of the first order.
And the value returned as ‘FALSE’ since the delivery date is given which is a non-empty cell. You apply the same formula for the rest of the cells. For the order ‘118531884’ delivery date is not given and the formula returns the result as ‘TRUE.’
To find the undelivered orders applying the formula to each cell. For the orders ‘118531884, 126387159’ delivery date is not given and is an empty cell. So, the ISBLANK function returns true. The delivery date which is true is the order not yet delivered.
Example 2: How to use ISBLANK with different excel functions?
In the above example, the ISBLANK function result gives TRUE or FALSE. The data is given below with order numbers and delivery date. In the status column, you want to get the result as ‘complete’ for orders which are delivered and ‘No’ for which are not delivered.
| S# | Order_Number | Delivery_ Date | Status |
|---|---|---|---|
| 1 | 127504425 | 24-Nov-16 | |
| 2 | 125387159 | 22-Dec-16 | |
| 3 | 118531884 | ||
| 4 | 115178509 | 29-Dec-16 | |
| 5 | 120962192 | 13-Jan-17 | |
| 6 | 118427223 | 10-Feb-17 | |
| 7 | 119727660 | 14-Feb-17 | |
| 8 | 119787660 | 16-Feb-17 | |
| 9 | 126387159 | ||
| 10 | 157504425 | 15-Jun-16 |
To get the results in the way you want, have to use some another function along with ISBLANK. IF function is used along with ISBLANK, to give result according to the two different conditions. If the cell is blank, it will return ‘No’ otherwise ‘Complete.’
The formula applied is
=IF(ISBLANK(C2), "No", "Complete")
Here,
- the ISBLANK function will check the cell of delivery date, and according to the value, it will result in the true or false.
- This True/False is fed to the IF function that returns a ‘No’ if the cell is empty and statement ‘Complete’ if a value is present.
Here is the complete output
After applying the formula to the status of each order will get which are orders delivered and not delivered yet. Here the two orders are not completed the delivery rest are delivered.
Also Check:- Excel Formulas & Functions: Learn with Basic EXAMPLES
Example 3: How to use ISBLANK function with conditional formatting?
ISBLANK function can be associated with conditional formatting to find blank cells and format the cells accordingly.
Step 1) Consider the following dataset.
Consider the following dataset that consists of data order_number, bill amount, delivery status. And you want to highlight the bill amount for which delivery is not completed.
| S# | Order_Number | Delivery_ Date | Bill_Amount | Status |
|---|---|---|---|---|
| 1 | 127504425 | 24-Nov-16 | $500 | Complete |
| 2 | 125387159 | 22-Dec-16 | $120 | Complete |
| 3 | 118531884 | $130 | No | |
| 4 | 115178509 | 29-Dec-16 | $100 | Complete |
| 5 | 120962192 | 13-Jan-17 | $78 | Complete |
| 6 | 118427223 | 10-Feb-17 | $460 | Complete |
| 7 | 119727660 | 14-Feb-17 | $321 | Complete |
| 8 | 119787660 | 16-Feb-17 | $12 | Complete |
| 9 | 126387159 | $100 | No | |
| 10 | 157504425 | 15-Jun-16 | $741 | Complete |
- What Is the Msvcr110.dll Missing Error and How Do You Fix It?
- Making the most of Office 365 Calendar
- APU Vs CPU – What’s the Difference?
- [How to] Open and Edit WebP Images on Windows 11
- How to Fix Windows Update Error 0x8007371c in Windows 10/11
Read also:
Step 3) Select option ‘Use a formula to determine which cells to format’
Select the option ‘Use a formula to determine which cells to format.’ This will allow you to insert a formula for a range of cells.
Give the formula ‘=ISBLANK($A$1:$E$11)’ within the space.
Step 4) Select the format.
Select the format which you want to apply to the cells from the format button.
- By hitting the format button, you will get a dialogue window to select the format of the cells where the formula is applied.
- Here, select the fill option to high light the formula applied cells where the condition matches.
- Select the color you want to show
- Hit the ‘OK’ button.
Step 5) Click ok button.
Format will appear in the preview, click ‘OK’ button to apply.
Step 6) Apply ISBLANK formula.
It will high light the blank cells after applying the ISBLANK formula with conditional formatting. Since the range value didn’t work here, you have to apply the same rule for the entire column to get the result as below.
Download the Excel used in this Tutorial
Summary
- ISBLANK function will return a true or false value. True indicates the cell is blank.
- ISBLANK function can be used for conditional formatting as well as with other excel functions.
- An easy method to find blank cells within a large data set.
- Range of cells will not work with ISBLANK.
Also Check:- Microsoft Excel Tutorial for Beginners
— Update: 20-03-2023 — us.suanoncolosence.com found an additional article Excel ISBLANK function to check if cell is empty or not from the website www.ablebits.com for the keyword use the isblank function in microsoft excel.
How to use ISBLANK in Excel
To gain more understanding of what the ISBLANK function is capable of, let’s take a look at some practical examples.
Excel formula: if cell is blank then
Since Microsoft Excel does not have a built-in IFBLANK kind of function, you need to use IF and ISBLANK together to test a cell and perform an action if the cell is empty.
Here’s the generic version:
IF(ISBLANK(cell), “if blank“, “if not blank“)
To see it in action, let’s check if a cell in column B (delivery date) has any value in it. If the cell is blank, then output “Open”; if the cell is not blank, then output “Completed”.
=IF(ISBLANK(B2), "Open", "Completed")
Please remember that the ISBLANK function only determines absolutely blank cells. If a cell contains something invisible to the human eye such as a zero-length string, ISBLANK would return FALSE. To illustrate this, please have a look at the screenshot below. The dates in column B are pulled from another sheet with this formula:
As the result, B4 and B6 contain empty strings (“”). For these cells, our IF ISBLANK formula yields “Completed” because in terms of ISBLANK the cells are not empty.
If your classification of “blanks” includes cells containing a formula that results in an empty string, then use =”” for the logical test:
=IF(B2="", "Open", "Completed")
The screenshot below shows the difference:
Excel formula: if cell is not blank then
If you’ve closely followed the previous example and understood the formula’s logic, you should have no difficulties with modifying it for a specific case when an action shall only be taken when the cell is not empty.
Based on your definition of “blanks”, choose one of the following approaches.
To identify only truly non-blank cells, reverse the logical value returned by ISBLANK by wrapping it into NOT:
IF(NOT(ISBLANK(cell)), “if not blank“, “”)
Or use the already familiar IF ISBLANK formula (please notice that compared to the previous one, the value_if_true and value_if_false values are swapped):
IF(ISBLANK(cell), “”, if not blank“) IF(cell “”, “if not blank“, “”)
For our sample table, any of the below formulas will work a treat. They all will return “Completed” in column C if a cell in column B is not empty:
=IF(NOT(ISBLANK(B2)), "Completed", "")
=IF(ISBLANK(B2), "", "Completed")
If cell is blank, then leave blank
In certain scenarios, you may need a formula of this kind: If cell is blank do nothing, otherwise take some action. In fact, it’s nothing else but a variation of the generic IF ISBLANK formula discussed above, in which you supply an empty string (“”) for the value_if_true argument and the desired value/formula/expression for value_if_false.
For absolutely blank cells:
IF(ISBLANK(cell), “”, if not blank“)
To regard empty strings as blanks:
- Top 6 Ways to Fix DNS Server Not Responding on Windows 11
- Feeds showing High Memory Usage in Windows 11/10
- Gmail Labels: Complete Guide to Keeping Your Inbox Organized
- Command Prompt closes immediately after opening batch file
- How to Fix Dxgi.dll Not Found or Missing Errors
Read also:
IF(cell=””, “”, if not blank“)
In the table below, suppose you want to do the following:
- If column B is empty, leave column C empty.
- If column B contains a sales number, calculate the 10% commission.
To have it done, we multiply the amount in B2 by percentage and put the expression in the third argument of IF:
=IF(ISBLANK(B2), "", B2*10%)
Or
=IF(B2="", "", B2*10%)
After copying the formula through column C, the result looks as follows:
If any cell in range is blank, then do something
In Microsoft Excel, there are a few different ways to check a range for empty cells. We will be using an IF statement to output one value if there is at least one empty cell in the range and another value if there are no empty cells at all. In the logical test, we calculate the total number of empty cells in the range, and then check if the count is greater than zero. This can be done with either COUNTBLANK or COUNTIF function:
COUNTBLANK()>0 COUNTIF(,””)>0
Or a little bit more complex SUMPRODUCT formula:
SUMPRODUCT(–(=””))>0
For example, to assign the “Open” status to any project that has one or more blanks in columns B through D, you can use any of the below formulas:
Note. All these formulas treat empty strings as blanks.
If all cells in range are blank, then do something
To check if all cells in the range are empty, we will be using the same approach as in the above example. The difference is in the logical test of IF. This time, we count cells that are not empty. If the result is greater than zero (i.e. the logical test evaluates to TRUE), we know that not every cell in the range is blank. If the logical test is FALSE, that means all cells in the range are blank. So, we supply the desired value/expression/formula in the 3rd argument of IF (value_if_false).
In this example, we will return “Not Started” for projects that have blanks for all the milestones in columns B through D.
The easiest way to count non-empty cells in Excel is by using the COUNTA function:
Or the SUMPRODUCT function with the same logic:
ISBLANK can also be used, but only as an array formula, which should be completed by pressing Ctrl + Shift + Enter, and in combination with the AND function. AND is needed for the logical test to evaluate to TRUE only when the result of ISBLANK for each cell is TRUE.
=IF(AND(ISBLANK(B2:D2)), "Not Started", "")
Excel formula: if cell is not blank, then sum
To sum certain cells when other cells are not blank, use the SUMIF function, which is especially designed for conditional sum.
In the table below, supposing you wish to find the total amount for the items that are already delivered and those that are not yet delivered.
If not blank then sum
To get the total of delivered items, check if the Delivery date in column B is not blank and if it isn’t, then sum the value in column C:
If blank then sum
To get the total of undelivered items, sum if the Delivery date in column B is blank:
=SUMIF(B2:B6, "", C2:C6)
Sum if all cells in range are not blank
To sum cells or perform some other calculation only when all cells in a given range are not blank, you can again use the IF function with the appropriate logical test.
For example, COUNTBLANK can bring us the total number of blanks in the range B2:B6. If the count is zero, we run the SUM formula; otherwise do nothing:
=IF(COUNTBLANK(B2:B6)=0, SUM(B2:B6), "")
The same result can be achieved with an array IF ISBLANK SUM formula (please remember to press Ctrl + Shift + Enter to complete it correctly):
=IF(OR(ISBLANK(B2:B6)), "", SUM(B2:B6))
In this case, we use ISBLANK in combination with the OR function, so the logical test is TRUE if there is at least one blank cell in the range. Consequently, the SUM function goes to the value_if_false argument.
Excel formula: count if cell is not blank
As you probably know, Excel has a special function to count non-empty cells, the COUNTA function. Please be aware that the function counts cells containing any type of data, including the logical values of TRUE and FALSE, error, spaces, empty strings, etc.
For example, to count non-blank cells in the range B2:B6, this is the formula to use:
=COUNTA(B2:B6)
To count blank cells, use the COUNTBLANK function:
=COUNTBLANK(B2:B6)
Source: https://www.guru99.com/excel-isblank-function.html