Contents
If the user uses an Excel table with a small amount of data, then there are no problems with finding empty cells. But when there is a lot of information, it would be good to automate this process. After all, it takes a lot of time to manually look through thousands of cells, which could be used for other work tasks or leisure. This is especially important when working with documents where most of the data is numeric. It’s important not to get confused there.
Also, automating the search for empty cells is important in situations where, based on the received data, another formula performs certain calculations. Then it allows you to save time on several stages at once.
With the help of built-in Excel tools, this task will not be difficult to realize. At the same time, there are as many as three ways to check a cell for whether there is some information in it or not, each of which should be used depending on the situation.
How to determine if a cell is empty or not (methods)
This can be done using the function ISBLANK, which can be used for a wide variety of data types, including not only numbers and text, but even boolean functions. Let’s take a closer look at the features of working with this formula.
This function has a very simple usage pattern. If there is no information in a particular cell, then it returns TRUE. If it contains certain information, then, accordingly, the value is FALSE.
There are other ways to determine whether there is information in all cells of a certain range or in a particular cell. Also in some situations it is reasonable to use the usual function IFfamiliar to almost every active user. Let’s also look at the rules for their use.
VBA procedures
VBA is a programming language that is used to write macros. These are small executable modules that perform a certain sequence of actions, including checking whether there is any information inside the cell or not.
Code for a specific cell
If we are faced with the task of specifying a specific cell in order to check it for the presence of some information, then we need to write down such a code.
Sub example()
If IsEmpty(Range(«A1»)) = True Then
MsgBox “Cell is empty”
else
MsgBox “Cell is not empty”
End If
End Sub
This piece of code checks cell A1 to see if it contains any data. And the result is displayed in the appropriate window.
Code for active cell
If we are faced with the task of writing a macro that analyzes the selected cell for the content of some information in it, then we need to use such a code fragment.
Sub example()
If IsEmpty(ActiveCell) = True Then
MsgBox “Cell is empty”
else
MsgBox “Cell is not empty”
End If
End Sub
ISBLANK function to check for empty cells
As we have already understood, the function ISBLANK makes it possible to determine whether a particular cell is empty. Let’s look at some practical aspects of using this feature.
Examples of using the IMPLAND function
Let’s describe some ways to use the function ISBLANK on practice. Let’s say you work as a teacher and you have a table open with student grades for the last test. At the same time, some of the points were not recorded, since a number of students did not pass it well and went to retake it. And suppose we are faced with the task of writing in the third column a list of those who passed the exam the first time.
In order to achieve our goals, we need to enter the following formula after selecting the range C3:C18.
=IF(OR(ISBLANK(B3),B3=2),”Retake”,”Passed”)
If the user understands at least a little how working with formulas works, then, based on the syntax of the above line, he will understand that if there is a score in the cell, then “Passed” will be written in the cell. If there is no grade, then the result “To retake” will appear.
After we enter this formula, the result will be as follows:
Next, let’s try to do some special formatting depending on whether the person has a score or not. To do this, you need to find the “Styles” option on the “Home” tab. There is an item “Conditional Formatting”. After we select it, we need to click the “Create Rule” button.
A window will appear in which we need to select the last option, which is about using a formula to determine the formatting options. In the line below you need to write the following formula, which is shown in the screenshot.
After that, we need to click on the “Format” button and select the red background color. Actions are confirmed by pressing the OK button. Now we have all the cells with the inscription “For retake” are highlighted in red.
Why should I use the IMPLAND function when checking for empty cells?
The person who is already a little versed in Excel may ask the question: why can’t you use the banal function IF for this purpose? Yes, you can. It is enough just to write down the following formula:
=IF(OR(B3=””;B3=2);”Retake”;”Passed”)
We see that we used quotes here to define cells without any values. And in the case described above, this formula would also work. But this is not possible in all cases. If certain value types are contained, this trick will not work. Let’s take a look at this screenshot.
We see that the first cell contains the sign ‘. This character does not appear in the sheet, and it also cannot be detected by the function IF. This is because it is a special character in Excel that is needed in order to display textual information if the cell is in numeric format, for example. This is often used when you need to insert a plus sign into a cell. If you do not use a single quote, the program will think that we are going to enter a formula. This is the main difference.
If you use the function ISBLANK for this purpose, the user gets the opportunity to know the real picture, which cells are really empty, and not just displayed as empty.
An example of checking for an empty cell
We have a table that contains certain information. We are faced with the task of checking the fields. Our original table looks like this.
To understand if there are empty cells, you need to use the array formula. Its main difference is that it works simultaneously with a large amount of data. To make an array formula, you need to press the combination Ctrl + Shift + Enter after the formula has been entered in the appropriate line.
=IF(SUM(—ISBLANK(B3:B17)),TRUE,FALSE)
In the example we are describing now, the function SUM used to quantify all values returned by a function ISBLANK. If, after calculating this formula, there is a value greater than zero, then the value “True” will be returned.
As a consequence, we get the following table.
We see that in the example under consideration there are those cells that do not contain any values.
Attention. The symbols “–” have been used in the formula above. It is necessary to pay special attention to this. These symbols mean double negative. In our example, it needs to be used to turn the boolean data type into a numeric one.
A number of Excel functions do not support automatic conversion of data types, so you need to do everything yourself. The most commonly used for this purpose is multiplying by one or adding zero to the resulting value. But you can also use any other arithmetic operation that will return the same value.
But practice shows that it is most convenient to use a double minus. This is not only more convenient at the typing stage, but also speeds up the spreadsheet. Moreover, the performance gain can reach 15 percent, which is a lot when there are a large number of functions.
How to count the number of blank cells in Excel
Now let’s look at another example that best suits our situation. Suppose we have a list of office workers, each of which is designated by a certain code, as well as their age. But not all fields are listed in the table yet. Our task is to check if all the fields are filled in and display a message with the number of fields that were not filled.
The table itself looks like this.
To implement the task, we need such a formula.
=IF(SUM(—ISBLANK(B3:B12));COUNTINGBLANK(B3:B12)&” empty fields”,AVERAGE(B3:B12))
Function IF makes it possible to check a set of cells to see if there are empty values among them (for this, the function – EPUSTO with appropriate arguments). If a positive value was returned as a result of the summation, then a text will be shown in which it is written how many cells do not have a value. For this, the function is used COUNTBLANK. And with the help of the sign & we connect several values.
It turns out such a document.
In this case, you need to remember about all the features and limitations of using the function. IFdescribed above.
Peculiarities of using the IBUMP function in Excel
There are several types of functions, and ISBLANK belongs to the logical category. In simple words, it checks some expression for compliance with a certain criterion. There are still a huge number of such functions, and this is not only the above IF. Also, such functions include operators such as IFLINK, NUMBER and so on. They all return one of two possible values: true or false.
If you write a function, its syntax is as follows.
=EPUSTO(value)
This function has only one required argument. There are many types of data that can be entered into it. It can be as numbers, text and any other values. In this case, the user can specify both a single cell and an entire range. If a range of cells is used, then you need to use an array formula instead of the usual one.
There are a number of features that are taken into account when the user is going to work with this function:
- You cannot specify a specific value as a parameter, because then the value “FALSE” will be automatically returned. The thing is that such a value a priori cannot contain any values.
- If we are faced with the task of making sure that “TRUE” is returned if the cell is filled with any information, then we need to use two functions at once: NOT и ISBLANK. Then you first need to use the first, and then the second as an argument.
- Using the function ADDRESS, as a function parameter ISBLANK. useless. Then the result will always be returned that the value does not match the criterion. All because the function ADDRESS returns a reference that is itself a kind of information, even if there is no data in the cell it refers to.
- The value “FALSE” will be as a result even in the situation where there is an error. This is because some information is contained in it in any case, even if it is incorrect. And if we are talking about information about the error, then this is also useful information. A link can already be considered a full-fledged data type.
- Very often, users forget about the array formula in order to check a large number of cells at once. Otherwise, the value returned by the function will be incorrect.
Conclusions
In simple words, finding empty cells in a document or a specific sheet is not so difficult. This is done in several ways, each of which has its own characteristics. Therefore, you need to use those that are best suited for a particular situation.
In addition to the two functions, it is also possible to use macros in order to understand whether there is information in a particular cell or not. above were code examples that allow you to write such a subroutine yourself. Truth. you need to adapt it to yourself. replacing the corresponding cells with your own. This problem can be solved by using a macro on the active cell. In this case, just click on the cell you want to check, after which the program will do everything for you.