The ISBLANK function is quite interesting and helps users save a lot of time when working with Excel spreadsheets. This article will help you understand that in more detail.
What does the ISBLANK function do in Excel?
According to the latest update, Excel has over 500 functions to assist users in spreadsheet processing, and ISBLANK is a commonly used function. The ISBLANK function in Excel helps users check if the current position contains content or is just an empty cell.
The syntax of the ISBLANK function is: =ISBLANK(Value), where Value is the position to be examined for whether it is an empty cell or a cell with data. In practice, the ISBLANK function can be used in the following real-life situations:
- Used to check empty cells in a column containing multiple data.
- Combined with other functions to produce desired results.
To understand how to use the ISBLANK function in Excel, let’s consider some simple and understandable examples.
See also: How to use the ISTEXT function in Excel
How to use the ISBLANK function through practical examples
To have a thorough understanding and skillfully apply Excel functions, nothing is better than regular practice. The following examples will guide you on using the ISBLANK function through simple examples that you can practice on your own:
1. Using the ISBLANK function to find empty cells in a column
To use the ISBLANK function in Excel to identify empty cells in a column with multiple data, let’s take a look at the following example:
Requirement: Find the names of candidates with an empty “Test Score” cell in the example table below and fill in the “Check” column:
Steps: To fulfill the above requirement, follow these steps:
- Step 1: At cell C2, enter the formula: =ISBLANK(B2), and press Enter to execute the function.
- Step 2: The result will return FALSE to indicate that cell B2 is not empty (it contains data). Place the mouse pointer at the square in the lower-right corner of cell C2, hold the left mouse button, and drag down to cell C5 before releasing the mouse button.
- Step 3: The result will be displayed, indicating that the answer in cell C4 is TRUE, indicating that the referenced cell B4 is empty (no test score).
- Step 4: Compare with the “Full Name” column to find out that the candidate with an empty test score is named Trần Văn Tân.
2. Using the ISBLANK function in Excel to highlight empty cells
In addition to returning TRUE (empty cell) or FALSE (cell with data) results, the ISBLANK function can also be used to highlight empty cells according to the user’s convention. Let’s explore how to use the ISBLANK function in Excel to highlight empty cells in the following example:
Requirement: Using the same spreadsheet, highlight the “Check” column with red color for candidates with an empty “Test Score” cell.
Steps: To fulfill this requirement, you can follow these steps:
- Step 1: Select the entire data range where the results will be displayed at column C (In this case, we are using a simple example, so the selected data range will be from cell C2 to C5).
- Step 2: After selecting the data range, click on the Home tab and choose Conditional Formatting => New Rule as shown in the image.
- Step 3: A dialog box will appear. Choose “Use a formula to determine which cells to format” and enter the command “=ISBLANK(B2)” in the next row as shown in the image.
Explanation: Selecting “Use a formula to determine which cells to format” allows us to use the formula that we will use to format the cell, and the line afterwards is to check the values from row B2 downward corresponding to the data range for which we want to display the results. Excel will automatically scan from B2 to B5 corresponding to the data range from C2 to C5 that we selected at the beginning.
- Step 4: Next, click the Format button in the dialog box and choose the red color as required by the task, then click OK.
- Step 5: Excel will instantly display the result, with only the candidate Trần Văn Tân having a RED result as the data in the “Test Score” cell for this candidate is empty.
3. Using the ISBLANK function in Excel combined with the IF function
In the examples above, we have explored the use of the ISBLANK function on its own. In the following example, we will combine the ISBLANK function with the IF function in Excel:
Requirement: Based on the given spreadsheet, fill in the “Result” cell with “Fail” for candidates with an empty “Test Score” cell and “Pass” for candidates with a score.
Steps: To fulfill this requirement, you can follow these steps:
- Step 1: At cell D2, enter the formula: =IF(ISBLANK(B2),”Fail”,”Pass”) and press Enter.
- Step 2: The result will instantly return “Pass” in cell D2. Click on the square in the lower-right corner of cell D2 and drag down to the end of the data range where you want to display the results (In our example, it is cell D5).
- Step 3: Excel will quickly produce results, in which only the candidate Trần Văn Tân has the result “Fail” because the “Test Score” cell for this candidate is empty.
Conclusion
So, we have just learned about the ISBLANK function and how to use it in Excel through simple, visual, easy-to-understand examples. If used correctly, the ISBLANK function will save you a lot of time when working with spreadsheets in Excel.
5 Essential Excel Functions for Beginners
Master the fundamentals of Excel with these 5 basic functions! Learn how to use SUM, AVERAGE, COUNT, MIN, and MAX in order to make calculating and analyzing data easier and more efficient.
How to recover unsaved or overwritten Excel files effectively: a sure-fire method
Knowing how to recover unsaved Excel files can be extremely helpful in cases when your computer unexpectedly shuts down or you accidentally click on “Don’t save” when Excel asks if you want to save your changes before closing. Follow this article to learn the step-by-step process of how to accomplish it.