How to check if excel cell is not empty or is empty

In this article, we’ll look at one interesting topic: “how to check if a cell in Excel is empty“. When working with big amounts of data, these empty cells can cause a lot of issues.

We are going to look at seven different ways we can check if an excel cell is empty or not.

Checking if an excel cell is empty with

  • Conditional formatting
  • IF function
  • ISBLANK function
  • Excel VBA Macros
  • Find command
  • Combining IF with the ISBLANK function
  • Multiple functions such as 
    • COUNTBLANK
    • COUNTIF
    • SUMPRODUCT

Let’s get right to It. We begin with.

Method 1: Checking if an excel cell is empty with Conditional formatting

Follow the steps below carefully

  • Select the cells or the range of cells that you wish to work on to search the empty cells.

  • Click on the home tab and select Conditional Formatting
  • Click on Highlight cells Rules
  • Click on more rules from the popup menu

  • Select Format only cells that contain
  • Click on Blanks

  • Choose the fill color of your choice to make the cells visible from the format option and click on OK

  • The end results are displayed below as all the blank cells in the selected range are highlighted in brown

Method 2: Checking if an excel cell is empty with the IF function

The IF function allows us to compare a value to what we expect in a logical manner.

The formula for this function is

What this means is

logical_test – The condition we want to test.

value_if_true – The value that we want to return if the result of logical_test is TRUE.

Value_if_false – The value that you want to return if the result of logical_test is FALSE.

The steps are as follows:

Click on cell B2 to activate it

Input the formula: =IF(B5=””,”Blank”,”Not Blank”) and press ENTER

  • Drag the fill handle down to apply the function to the rest of the cells

If you want to learn more about Microsoft Excel, then check out the list of posts that have been published under the Microsoft Excel Category

Method 3: Checking if an excel cell is empty with ISBLANK Function

Based on two states, the ISBLANK function returns TRUE or FALSE. Show TRUE if the argument is empty, else FALSE.

The formula used is: ISBLANK(value)

Meaning;

  • Value – This value will be tested. This may be empty or filled with text or logical value, etc.
  • Click on the B2 cell
  • Input the ISBLANK function and press enter
  • Select A2 as the argument. This is how it will be

Now drag the fill handle down to the last cell to apply the formula to the rest of the cells.

Now we can see that there is just one cell that is empty, and the result for that cell is TRUE. However, because the rest of the cells aren’t empty, they indicate FALSE.

Method 4: Checking if an excel cell is empty with Excel VBA Macros

  • First, go to the Home tab.
  • Select the Developer option from the main tab.
  • From the commands select Marcos.

We will get a Dialog box

Name the Macro as Checking_empty_cells and the click on create

Now, type the below code in the VBA command module and press F5 to run the code. Or function key (fn) + f5

The result after running the code is displayed below

We can see the empty cells are 3.

Method 5: Checking if an excel cell is empty with FIND COMAND

The Find command can also be used to see if a cell in a worksheet is empty. Before we begin, we’ll make a few changes to the previous dataset.

The steps below will guide you to complete this

select the range of cells from which you want to check for empty cells

Press Ctrl+F

Leave the Find what box empty

Click on Find All

As listed above, we can see the empty cells are A3, A6, and A9

techpady virtual classroom

Digital School Academy

Join our telegram channel and benefit from our regular digital skills trainings and also become a tech guru with very simplified tech tutorials

Method 6: Checking if an excel cell is empty by Combining IF with ISBLANK function

Click on cell B2Type the formula: =IF(ISBLANK(A1), “Blank”, “Not Blank”) into the formula bar

And click on enter

Drag the fill handle below to apply the function to the rest of the cells

Method 7a: Checking if an excel cell is empty with COUNTBLANK

COUNTBLANK is a statistical tool that is used to count the number of cells that are empty in a range of cells.

The formula it uses is: COUNTBLANK(range)

 Click on B1 to input the COUNTBLANK formula: =COUNTBLANK(A1:A10)

Click on enter

We can see from the above shot that the number of empty cells in the range A1:A10 is 3.

Method 7b: Checking if an excel cell is empty with SUMPRODUCT

SUMPRODUCT is a function that adds all of the products together. Originally, the SUMPRODUCT function was used to execute a sum operation. The sum of the products of supplied ranges or arrays is produced. Subtraction and division with multiplication are also included.

The formula used is: =SUMPRODUCT(array1, [array2], [array3], …)

The steps below will guide you on how to use this function

Click on cell B1 and input the formula: =SUMPRODUCT(–(A1:A10=””))>0

Click on OK

Method 7c: Checking if an excel cell is empty with COUNTIF

It calculates the number of cells that fulfill a condition.

Syntax:

COUNTIF(range, criteria)

COUNTIF is a function that counts how many times something happens.

Compare to blank in the A1:A10 range.

If there are any blanks, display TRUE; otherwise, display FALSE. The formula is as follows:

=COUNTIF(A1:A10,””)

And click on enter

We only identified three vacant cells after using this formula, and that number is displayed.

Spread the love

Similar Posts

Leave a Reply

Your email address will not be published.