When working on large datasets, you may want to run a quick scan on the cells to ensure that all cells are filled up with values and empty cells are detected.
If you also want to run general calculations on your worksheet but want to quickly check if these cells are filled, the ISBLANK function is what you need.
It is a straightforward function that can be used irrespective of the value types and format.
The ISBLANK function helps to check if the cell is empty or not. In this tutorial, we are going to discuss how to use ISBLANK function in google sheets efficiently.
The formula of ISBLANK Function In Google Sheets.
Whenever the ISBLANK function detects an empty cell, a TRUE statement is returned. If the ISBLANK function detects a full cell, a FALSE statement is returned.
The ISBLANK function works with all sorts of value types. It could be a text string, number, special characters, apostrophes or extra spaces.
As long as any of these special characters are detected, a FALSE statement is returned.
So sometimes, the cell might look empty but may include extra space or an apostrophe, which may cause the wrong returned statement.
To avoid this ensure the cells being checked are free from any added space and apostrophe. The ISBLANK function takes the syntax below.
=ISBLANK(value)
Where;
- Value- this is the cell reference or range where you want to see if the cell is empty or not.
How To Use the ISBLANK Function?
The ISBLANK function can be used in different ways. These ways include.
- In an individual or single cell.
- In an array of cells.
- In combination with other functions.
In an Individual or Single Cell.
This method is straightforward. In this sample, we are going to check a cell which is cell A2, if it is empty or filled.
Step 1: Input the formula =ISBLANK(A2) in cell B2.
Step 2: Hit the Enter key on the keyboard. The TRUE Statement is returned in cell B2. This proves that the cell is empty.
Let’s add an apostrophe into cell A2.
Hit the Enter key on your keyboard and a FALSE statement is returned. This proves that the cell is not empty although it looks empty.
In an Array Of Cells.
Now, we have a full data set in multiple cells in column B. In column B, we want to see if this array of cells is completely empty or not.
In the sample worksheet, there are some entered ages of some students. Some students’ ages aren’t entered into the dataset yet.
So we want to check if the cells are empty or not. We make use of a combination formula which involves the ARRAYFORMULA and ISBLANK functions. The formula takes the syntax below.
=ARRAYFORMULA(ISBLANK(cell range)
Step 1: Select an empty cell as the display or result cell. In this case, we chose cell C2.
Step 2: Input the formula into the result cell.
=ARRAYFORMULA (ISBLANK( B2:B10).
Where B2:B10 is the cell range of the ages of the students.
Step 3: Hit the Enter key on your keyboard and the ISBLANK function scans the selected cell range and automatically displays the TRUE and FALSE statements according to the condition of the cells (if they are empty or not).
Alternatively, if you are not confident in the array formula method, you can scan the first cell using this original ISBLANK formula, that is, =ISBLANK(B2).
Then, drag down the formula by clicking and dragging the cube box which is the fill icon to copy and paste the statements into their respective cells.
Sometimes, Google Sheets gives a suggested autofill to automatically copy and paste the statements into the cells.
Either way, the two means of using the ISBLANK function in an array of cells are completely accurate.
Combinations with other Functions.
Another valid method for using the ISBLANK function is with the IF function. This broadens the ISBLANK functions capability and makes its use more dynamic.
The IF and ISBLANK functions combine to form a formula that scans the cells and gives a condition if the ISBLANK function returns a TRUE or FALSE statement.
For example, we have a list of students’ names, their ages, and their grades in English, Mathematics and History tests.
Some details of the students are incomplete and we want to use the SUM function to add up the grades of each student. To avoid errors, we would use the ISBLANK function to check for empty and full cells.
We’ve provided a separate dataset where the returned statements would be displayed.
Step 1: In the separate dataset, enter the combination formula in cell B14 which is,
=IF(ISBLANK(B2)=TRUE, “Empty”, ” Not Empty”).
This means that if ISBLANK returns a TRUE statement, “Empty” is displayed in the cell and if a FALSE statement is returned, “Not Empty” is displayed in the cell.
Press the Enter key and the statement in entered into column B of the separate dataset and copy or select the autofill icon to paste the statements.
Fill the formula into the other columns to get the returned statements.
The ISBLANK function also works in combination with the IFS function if you want to apply multiple conditions to the empty or non-empty cells.
Frequently Asked Questions on ISBLANK Functions (FAQs).
Why Do We Use the ISBLANK Function in Google Sheets?
The ISBLANK function is used to validate your data by scanning and detecting stray spaces and apostrophes to avoid errors in further calculations.
Why does The ISBLANK Function return a FALSE Statement when the Cell Reference is Empty?
This is due to the presence of an extra backspace, beeline or apostrophe in the cell. It may look empty but there is a special character in the cell. To avoid errors, thoroughly scan your worksheet and remove such characters.
How To Test Multiple Blank Cells Using ISBLANK Functions in Google Sheets?
To test multiple blank cells, we use the ISBLANK and ARRAYFORMULA combination formula.
=ARRAYFORMULA(ISBLANK(cell range)
It returns FALSE and TRUE statements according to the condition of the other cells. Multiple cells are included in the cell range. This formula is very useful when working with a large dataset. It is efficient and a time saver.
Final Thoughts
Individually, the ISBLANK function helps to scan and detect cells that may seem empty even though they are not. This prevents future errors when other arithmetic functions are entered into your worksheet.
When combined with other functions, it becomes dynamic and also acts as a filter on your worksheet. It is left to you to correct the detect blank cells if you want to. Now you know How To Use ISBLANK Function In Google Sheets.
Do you know any other functions that work well with the ISBLANK function? If you do, let us. Thanks for reading.
