What Is ISBLANK In Google Sheets?
The ISBLANK in Google Sheets is used to check if the selected cell in a dataset, is empty or blank, without any entered data or sometimes missing data.
The Google Sheets ISBLANK is a built-in information function, therefore, if the cell is blank, then it will return the output as “TRUE” or else “FALSE”.
For example, we have some random values in the table below. Let us check for blank cells using the ISBLANK function in Google Sheets.
Select cell B2, enter the formula =ISBLANK(A2), press “Enter” and drag the formula from cell B2 to B5 using the fill handle, as shown below.
The output is shown above, i.e., the ISBLANK formula returned “FALSE” if the cell values are there regardless of the cell values and “TRUE” for the blank cells.
Key Takeaways
- The ISBLANK in Google Sheets check the data, in a worksheet, for blank cells. The return value is always the logical values, “TRUE” or “FALSE”, whether the searched cell is blank or not, respectively.
- It is a single argument function, i.e., we can select only one cell at a time and drag the formula for the rest of the cells. However, to select a cell range we can execute the formula as an array formula by selecting the keys “Ctrl+Shift+Enter”.
- To get alternate or spiced up results other than the default “TRUE” or “FALSE”, we can insert the IF function condition in the ISBLANK formula.
- We can select a cell range when we need to apply the “Conditional Formatting” to the dataset and insert the ISBLANK formula.
ISBLANK() Google Sheets Formula
The syntax of the ISBLANK Google Sheets Formula is,
The one and only mandatory argument of the ISBLANK Google Sheets Formula is,
- value – It is the cell we will select to check whether it is blank or not.
How To Use ISBLANK In Google Sheets?
We can use the ISBLANK Google Sheets Function in two ways, as follows:
- Access from the Google Sheets ribbon.
- Enter the formula in the worksheet manually.
Method #1: Access from the Google Sheets ribbon
Step 1: Choose an empty cell for the output – select the “Insert” tab – click the “Function” option right arrow – click the “Info” option right arrow – select the “ISBLANK” function, as shown below.
Step 2: The “ISBLANK” formula appears, as shown below. Enter the argument as cell reference.
Method #2: Enter the formula in the worksheet manually
Step 1: Select an empty cell for the output.
Step 2: Type =ISBLANK( in the cell, as shown below. [Alternatively, type =IS or =ISB and double-click the ISBLANK function from the Google Sheets suggestions.]
Step 3: Enter the arguments as cell values or cell references and close the brackets.
Step 4: Press Enter to view the outcome.
Examples
We will understand the function using the ISBLANK in Google Sheets examples.
Example #1 – ISBLANK on an array of cells
The data table given below consists of smartphones quarterly sales, where some data is missing and their respective cells are empty or blank, as highlighted. Now, we will use the ISBLANK on an array of cells and get the results.
The steps to use ISBLANK on an array of cells are,
Step 1: First, in cells A9:D15, let us create a result table with the same headers and copy only the formatting and not the values, as shown below.
Step 2: Next, in cell B10, enter the formula = ISBLANK(B2:D7), press the “Ctrl+Shift+Enter” keys, because we are executing as an array formula, and press “Enter”.
Now the formula looks like this =ArrayFormula(ISBLANK(B2:D7))
The output is shown above. The highlighted cells in the dataset were blank and their corresponding result cells are TRUE, that indicates the cells are blank. Rest all are FALSE as they are not blank.
Example #2 – ISBLANK on an array of cells, spiced up.
Let us consider Example 1’s dataset again. We will apply the ISBLANK formula, however instead of the default TRUE or FALSE results lets get alternate results, respectively.
The steps to use ISBLANK on an array of cells and get alternate results are,
Step 1: First, in cells F1:I7, let us create a result table with the same headers and copy only the formatting and not the values, as shown below.
Step 2: Next, in cell G2, enter the formula =IF(ISBLANK(B2:D7)=TRUE,“Really BLANK”,“Sales Amount”) press the “Ctrl+Shift+Enter” keys, because we are executing as an array formula, and press “Enter”.
Now the formula looks like this =ArrayFormula(IF(ISBLANK(B2:D7)=TRUE,“Really BLANK”,“Sales Amount”))
The output is shown above. The highlighted cells in the dataset were blank and their corresponding result cells says “Really BLANK”, that indicates the cells are blank. Rest all are “Sales Amount” as entered in the formula for an alternate result.
Example #3 – Using ISBLANK with the IF Function.
We will search for the blank cells from the following data using the Using ISBLANK with the IF Function to get the results entered in the formula, instead of the default results.
The steps to detect the blank cells using the IF() and the ISBLANK() are as follows:
Step 1: Select the cell B2, enter the formula =IF(ISBLANK(A2),”It is blank”,”It has a cell value”) and press “Enter”, as shown below.
Step 2: Drag the formula from cells B2 to B7 using the fill handle, to get the following results.
Example #4 – Using ISBLANK in Data Validation.
The data given below consists of user’s names, their contact numbers and email ids. We will Using ISBLANK in Data Validation and ensure to leave the blank cells as it is, meaning, the rule set will not allow data to be added to the blank selected cell.
The steps to use ISBLANK in Data Validation are as follows:
Step 1: Choose cell B4 – select the “Data” tab – click the “Data validation” option and the “Data validation rules” window appears on the right, as shown below. Here, click the “Add rule” button.
Step 2: Choose the “Custom formula is” option. It is from the “Criteria” drop-down, as shown below.
Step 3: Enter the formula “=ISBLANK(B4)” in the field that appears below the “Criteria” drop-down. Choose the “Reject the input” option from the “If the data is invalid:” option and click the “Done” option, as shown below.
Step 4: Now to check the rule, let us try to enter some value in cell B4, here abc.
Immediately, we get an error, as shown below.
Therefore, as per the set rule, the cell B4 is always blank and does not accept any input.
Example #5 – Using ISBLANK with Conditional Formatting.
The dataset given below has the marks scored by a student in an exam. We must highlight the empty cells where the marks are not found or entered.
The steps to use the ISBLANK() function to apply the Conditional Formatting feature are as follows:
Step 1: First, choose the cell range A1:C9 – select the “Format” tab – click the “Conditional formatting” option. The “Conditional format rules” pane appears on the right side. There, click the “Add another rule” option, as shown below.
Step 2: We see the “Single Color” and the “Color scale” tabs. Here, click the “Single color” tab, select the “Custom formula is” option from the “Format cells if…” drop-down, as shown below.
Step 3: When we select the “Custom formula is” option, an empty field appears below. There, enter the “=ISBLANK(A1)” formula, select the required color and click the “Done” option, as shown below.
We get the output shown above, where the cells with no entry of marks, that are empty, are highlighted.
Important Things To Note
- The ISBLANK function can only be applied to a single cell. We will get the result as “False” if we select multiple values or a cell range, unlike MS Excel, where we get an error message as, “You’ve entered too many arguments for this function.”.
- The ISBLANK function can be combined with other functions like the IF function, the VLOOKUP function, etc.
- We can use the Conditional Formatting and other Google Sheets functions in the ISBLANK formula to highlight the required results.
Frequently Asked Questions
We often forget in which category a function falls, here, the “ISBLANK” function. Then, we can insert the function as follows:
Choose an empty cell – select the “Insert” tab – click the “Function” option right arrow – click the “All” option right arrow – select the “ISBLANK” function, as shown below.
However, as always, entering the function manually is the best way to avoid confusion.
Alternatively, we can find the Functions icon to insert the ISBLANK function in Google Sheets by following the path shown below.
• Choose an empty cell – click the “More” option represented by the three vertical dots at the end of the toolbar, as shown below.
• A list of icons appears when we click the “More” option. Here, click the “Functions” icon, as shown below.
• Here, click the “Functions” option – click the “All” option right arrow – select the “ISBLANK” function, as shown below.
The ISBLANK in Google Sheets may not work due to the following reasons:
• There are extra spaces in between the formulas, then remove the unwanted spaces.
• The ISBLANK in Google Sheets may return “False” even though the result cell is blank. It may be because the ISBLANK function has considered the zero–length strings as blank cells or the cells have some spaces included like spacebar pressed.
a. We can use the ISBLANK in Google Sheets primarily to check for blank cells in large datasets and the function checks the specified cell, returns the output as “TRUE” if the cell is blank or else as “FALSE” if the cell has a value.
b. Since the ISBLANK in Google Sheets is an inbuilt Information function, we can enter it in the worksheet,
• As a “formula” i.e.,=ISBLANK(value), or
• Select it from the “Insert – Functions” menu.
Download Template
This article must help understand ISBLANK in Google Sheets with its formula and examples. You can download the template here to use it instantly.
Recommended Articles
Guide to What Is ISBLANK In Google Sheets. We learn its syntax & how to use it to find if a cell is blank for calculations with examples. You can learn more from the following articles –
100 Stacked Bar Chart In Google Sheets
Leave a Reply