How to Return Value if Cell is Blank (12 Ways)

If you are looking for some of the easiest ways to return value if a cell is blank, then you will find this article useful. So, let’s get started with the main article.

Download Workbook


12 Ways to Return Value if Cell is Blank

Here, I am using the following table which contains Order Dates, Delivery Dates, and Sales of some Items of a company. By using this dataset, I will try to demonstrate the ways of returning values for a Blank Cell.

Return Value if Cell is Blank

For creating the article, I have used Microsoft Excel 365 version, you can use any other versions according to your convenience.


Method-1: Using IF Function to Return a Value of the Adjacent Cell if Cell is Blank

Let’s say, you want to get the Order Dates for the products which are not been delivered yet (Empty cells in the Delivery Date column) in the Order Date for Not Delivered items column. To do this you can use the IF function.

Return Value if Cell is Blank

Step-01:
➤Select the output cell F5
➤Type the following formula

=IF(D5="",C5,"")

Here, the logical condition is D5=”” which means cell D5 of the Delivery Date column will be empty and if it is TRUE then it will return the value of cell C5 of the Order Date column otherwise it will return Blank.

adjacent cell

➤Press Enter
➤Drag down the Fill Handle Tool

adjacent cell

Result:
In this way, you will get the Order Dates for the corresponding Blank cells of the Delivery Date column.

adjacent cell


Method-2: Using IF Function to Return a Value

Suppose, the company wants to compensate the customers for late delivery by 5% Off on the Total Sales value. So, you can estimate this value for the products which are not been delivered yet by following this method.

Return Value if Cell is Blank

Step-01:
➤Select the output cell F5
➤Type the following formula

=IF(D5="",5%*E5,"")

Here, the logical condition is D5=”” which means cell D5 of the Delivery Date column will be empty and if it is TRUE then it will return 5% of the Sales value (E5 cell) otherwise it will return Blank.

IF function

➤Press Enter
➤Drag down the Fill Handle Tool

IF function

Result:
After that, you will get a 5% commission of the Sales values for the corresponding Blank cells of the Delivery Date column.

Return Value if Cell is Blank


Method-3: Using IF Function and ISBLANK Function 

For returning a value if any cell of the Delivery Date column is blank you can use the IF function and the ISBLANK function.

Return Value if Cell is Blank

Step-01:
➤Select the output cell F5
➤Type the following formula

=IF(ISBLANK(D5),"Not Delivered","Delivered")

Here, the logical condition is ISBLANK(D5), ISBLANK will return TRUE if cell D5 of the Delivery Date column is empty and if it is TRUE then IF will return “Not Delivered” otherwise it will return “Delivered” when the cells of the Delivery Date column are non-empty.

IF+ISBLANK function

➤Press Enter
➤Drag down the Fill Handle Tool

IF+ISBLANK function

Result:
Then, you will have the Not Delivered state for the corresponding Blank cells of the Delivery Date column.

IF+ISBLANK function


Method-4: Using IF Function and COUNTBLANK Function

You can use the IF function and the COUNTBLANK function to return a value for the empty cell of the Delivery Date column.

Return Value if Cell is Blank

Step-01:
➤Select the output cell E5
➤Type the following formula

=IF(COUNTBLANK(D5)>0,"Not Delivered","Delivered")

Here, the logical condition is COUNTBLANK(D5)>0, COUNTBLANK will count the number of blank cells and when there is a blank cell it will return a number greater than 0 and so it will return TRUE if cell D5 of the Delivery Date column is empty.

When it is TRUE, IF will return “Not Delivered” otherwise it will return “Delivered” when the cells of the Delivery Date column are non-empty.

IF+COUNTBLANK function

➤Press Enter
➤Drag down the Fill Handle Tool

IF+COUNTBLANK function

Result:
After that, you will have the Not Delivered state for the corresponding Blank cells of the Delivery Date column.

Return Value if Cell is Blank


Method-5: Using IF Function and COUNTIF Function

For returning a value if any cell of the Delivery Date column is blank you can use the IF function and the COUNTIF function.

Return Value if Cell is Blank

Step-01:
➤Select the output cell E5
➤Type the following formula

=IF(COUNTIF(D5,"")>0,"Not Delivered","Delivered")

COUNTIF(D5,””) will return the number of blank cells and if it finds a blank cell in cell D5 of the Delivery Date column then the number will be greater than 0 and so it will return TRUE otherwise FALSE.
When it is TRUE, IF will return “Not Delivered” otherwise it will return “Delivered” when the cells of the Delivery Date column are non-empty.

IF+COUNTIF function

➤Press Enter
➤Drag down the Fill Handle Tool

IF+COUNTIF function

Result:
Then, you will have the Not Delivered state for the corresponding Blank cells of the Delivery Date column.

Return Value if Cell is Blank


Method-6: Using IF Function and SUMPRODUCT Function to Return a Value

Here, I want to have the Item name for the blank cells of the Delivery Date column in the Not Delivered Products column by using the IF function and the SUMPRODUCT function.

Return Value if Cell is Blank

Step-01:
➤Select the output cell E5
➤Type the following formula

=IF(SUMPRODUCT(--(D5=""))>0,B5,"")

Here, —  will coerce TRUE or FALSE into 1 or 0 and so for blank cells the value will be 1 and so it will be greater than 0 otherwise it will be 0.

So, SUMPRODUCT(–(D5=””))>0 will return TRUE when the D5 cell is blank otherwise FALSE. When it is TRUE, IF will return the value of the B5 cell otherwise it will return Blank when the cells of the Delivery Date column are non-empty.

IF+SUMPRODUCT function

➤Press Enter
➤Drag down the Fill Handle Tool

IF+SUMPRODUCT function

Result:
Afterward, you will get the Items name for the corresponding Blank cells of the Delivery Date column.

IF+SUMPRODUCT function


Similar Readings:


Method-7: Using IF Function and LEN Function to Return a Value 

To get the Item name for the blank cells of the Delivery Date column in the Not Delivered Products column, you can use the IF function and the LEN function.

Return Value if Cell is Blank

Step-01:
➤Select the output cell E5
➤Type the following formula

=IF(LEN(D5)=0,B5,"")

LEN(D5) will return the length of the string in the cell D5 and it will return 0 when D5 is blank and then IF will return the value of the B5 cell otherwise it will return Blank when the cells of the Delivery Date column are non-empty.

IF+LEN function

➤Press Enter
➤Drag down the Fill Handle Tool

IF+LEN function

 

Result:
Then, you will get the Items name for the corresponding Blank cells of the Delivery Date column.

Return Value if Cell is Blank


Method-8: Highlighting Blank Cells

If you want to highlight the blank cells, you can follow this method.

Return Value if Cell is Blank

Step-01:
➤Select the cell range on which you want to apply the Conditional Formatting
➤Go to Home Tab>>Conditional Formatting Dropdown>>New Rule Option.

highlighting blank cells

Then the New Formatting Rule Wizard will appear.
➤Select the Format only cells that contain Option.

Return Value if Cell is Blank

Step-02:
➤Choose Blanks in the Format only cells with: Option
➤Click Format Option

highlighting blank cells

After that, the Format Cells Dialog Box will open up.
➤Select Fill Option
➤Choose any Background Color
➤Click on OK.

Return Value if Cell is Blank

After that, the Preview Option will be shown as below.
➤Press OK.

highlighting blank cells

Result:
In this way, you will get the blank cells highlighted.

highlighting blank cells


Method-9: Highlighting Blank Cells with a Formula

You can highlight the blank cells by using the ISBLANK function and Conditional Formatting.

Return Value if Cell is Blank

Step-01:
➤Select the data range on which you want to apply the Conditional Formatting
➤Go to Home Tab>>Conditional Formatting Dropdown>>New Rule Option.

highlighting blank cells with formula

Then the New Formatting Rule Wizard will appear.
➤Select Use a formula to determine which cells to format option.

Return Value if Cell is Blank

 

➤Write the following formula in the Format values where this formula is true: Box

=ISBLANK(B5:E11)

ISBLANK will return TRUE if any cell in the range is empty otherwise FALSE.

➤Click on Format Option.

highlighting blank cells with formula

After that, the Format Cells Dialog Box will open up.
➤Select Fill Option
➤Choose any Background Color
➤Click on OK.

highlighting blank cells with formula

After that, the Preview Option will be shown as below.
➤Press OK

highlighting blank cells with formula

Result:
Then, you will get the blank cells highlighted.

Return Value if Cell is Blank


Method-10: Using SUMIF Function for Summing up Values Based On Blank Cells

You can sum up the Sales value for the Items which have blank Delivery Dates (the items have not been delivered yet) by using the SUMIF function.

Return Value if Cell is Blank

Step-01:
➤Type the following formula in the cell E12

=SUMIF(D5:D11,"",E5:E11)

Here, D5:D11 is the criteria range, “” (Blank) is the criteria and E5:E11 is the sum range.

SUMIF function

➤Press ENTER

Result:
Afterward, you will get the sum of Sales for the Items which are not been delivered yet.

SUMIF function


Method-11: Using COUNTIF Function for Summing up the Number of Blank Cells

Here, I will use the COUNTIF function for counting the number of blank cells in the Delivery Date column.

Return Value if Cell is Blank

Step-01:

➤Type the following formula in the cell E12

=COUNTIF(D5:D11,"")

Here, D5:D11 is the criteria range, “” (Blank) is the criteria.

COUNTIF function

➤Press ENTER

Result:
Then, you will get the number of Items which are not been delivered yet.

COUNTIF function


Method-12: Using VBA Code for Returning Value

You can use the following VBA code to return values for the blank cells in the Delivery Date column.

Return Value if Cell is Blank

Step-01:
➤Go to Developer Tab>>Visual Basic Option

VBA code

Then, the Visual Basic Editor will open up.
➤Go to Insert Tab>> Module Option

Return Value if Cell is Blank

After that, a Module will be created.

VBA code

Step-02:
➤Write the following code

Sub blankcell()

Dim Lr As Long
Dim n As Long

Lr = Cells(Rows.Count, "C").End(xlUp).Row

  For n = 5 To Lr

      If Cells(n, "D").Value = "" Then

        Cells(n, "D").Offset(0, 1).Value = "Not Delivered"

      Else

          Cells(n, "D").Offset(0, 1).Value = "Delivered"

      End If

  Next n

End Sub

At first, I declared Lr, n as Long.

Lr will give you the last row of your data table and the FOR loop is used for performing the actions for rows from 5 To Lr. Here, 5 is for the first row of the range.

When Cells(n, “D”).Value = “” becomes TRUE, then the following line will continue and give the output in the adjacent cell as “Not Delivered”. Here, the adjacent cell will be selected by Cells(n, “D”).Offset(0, 1), which means it will move 1 column right from the input cell.

If the condition becomes FALSE means a cell doesn’t have any blank then the line under Else will execute and give the output value in the adjacent cell as “Delivered”.

This loop will continue for each row in this range.

VBA code

➤Press F5

Result:
Then, you will have the Not Delivered state for the corresponding Blank cells of the Delivery Date column.

VBA code


Practice Section

For doing practice by yourself I have provided a Practice section like below in a sheet named Practice. Please do it by yourself.

practice


Conclusion

In this article, I tried to cover the easiest ways to return values for blank cells. Hope you will find it useful. If you have any suggestions or questions, feel free to share them in the comment section.


Further Readings

Tanjima Hossain

Tanjima Hossain

Hello everyone, This is Tanjima Hossain. I have completed my graduation from BUET. Then I have started working as a technical writer in SOFTEKO. I have grown interest in technical content writing, research topics, numerical analysis related field and so I am here. Besides this I love to interact with different people and I love to spend my spare time by reading, gardening ,cooking etc.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo