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

Get FREE Advanced Excel Exercises with Solutions!

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.

Returning Value if Cell is Blank in GIF


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.

Representing Dataset overview

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 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.

Blank Cells within Dataset

Step-01:
➤ First, Select the output cell F5.

➤ Then, 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.

Inserting Formula to Return Value

➤ And, Press Enter.

➤ Then, Drag down the Fill Handle Tool.

Copying Formula in All Cells

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

Returning Value if Cell is Blank

Read More: Excel If Two Cells Are Blank Then Return Value


Method-2: Using IF Function to Return Value if Cell is Blank

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.

Showing Blank Cells within Dataset

Step-01:
➤ First, Select the output cell F5.

➤ Then, 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.

Inserting Formula to Return Value if Cell is Blank

➤ Now, Press ENTER.

➤ And, Drag down the Fill Handle Tool.

Copying Formula in All Cells

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

Showing Result Returning Value if Cell is Blank

Read More: How to Fill Blank Cells with Formula in Excel (2 Easy Methods)


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.

Showing Blank Cells Within Dataset

Step-01:
➤ Again, Select the output cell F5.

➤ Then, Type the following formula.

➤ Now, Press ENTER.

=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.

Inserting Formula to Return Value if Cell is Blank

So, we get Delivered as output.

➤ Then, Drag down the Fill Handle Tool.

Copying Formula in All Cells

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

Returning Values if Cell is Blank

Read More: Return Non Blank Cells from a Range in Excel (3 Suitable Ways)


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.

Showing Blank Cells Within Dataset

Step-01:
➤ First, Select the output cell E5.

➤ Then, Type the following formula and Press ENTER.

=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.

Inserting Formula to Return Value if Cell is Blank

➤ Drag down the Fill Handle Tool.

Copying Formula in All Cells

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

Showing Result Returning value if Cell is Blank

Read More: How to Find & Count If a Cell Is Not Blank (with Examples)


Similar Readings


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.

Showing Blank Cells within Dataset

Step-01:
➤ Select the output cell E5.

➤ Type the following formula and Press ENTER.

=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.

Inserting Formula to Return Value if Cell is Blank

➤ Drag down the Fill Handle Tool.

Copying Formula in All Cells

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

Returning Values if Cell is Blank

Read More: Find If Cell is Blank in Excel (7 Methods)


Method-6: Using IF Function and SUMPRODUCT Function to Return a Value if Cell is Blank

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.

Blank Cells within Dataset

Step-01:
➤ Select the output cell E5.

➤ Type the following formula and Press ENTER.

=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.

Inserting Formula to Return Values if Cell is Blank.

➤ Drag down the Fill Handle Tool.

Copying Formula in All Cells

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

Returning Values if Cell is Blank

Read More: How to Fill Blank Cells with Value from Left in Excel (4 Suitable Ways)


Method-7: Using IF Function and LEN Function to Return a Value if Cell is Blank

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.

Showing Blank Cells within Dataset

Step-01:
➤ Select the output cell E5.

➤ Type the following formula and Press ENTER.

=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.

Inserting Formula to Return Values if Cell is Blank

➤ Drag down the Fill Handle Tool.

Copying Formula in All Cells

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

Returning Values if Cell is Blank

Read More: Fill Blank Cells with Text in Excel (3 Effective Ways)


Method-8: Highlighting Blank Cells

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

Showing Blank Cells within Dataset

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.

Steps for Conditional Formatting

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

Setting New Formatting Rule

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

Setting Formatting Rule

After that, the Format Cells Dialog Box will open up.
➤ First, Go to the Fill tab.

➤ Then, Select any Background Color.

➤ And, Click on OK.

Setting Formatting Color

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

Steps for Conditional Formatting

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

Showing Highlighted Cells

Read More: How to Fill Blank Cells with Color in Excel (5 Methods)


Similar Readings


Method-9: Highlighting Blank Cells with a Formula

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

Blank Cells within Dataset

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.

Steps for Conditional Formatting

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

Setting New Formatting Rule

 

➤ 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.

Setting Formula as Formatting Rule

After that, the Format Cells Dialog Box will open up.
➤ Select Fill Option.

➤ Choose any Background Color.

➤ Click on OK.

Selecting Format Color

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

Steps for Conditional Formatting

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

Returning Value if Cell is Blank

Read More: How to Apply Conditional Formatting in Excel If Another 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.

Showing Blank Cells within Dataset

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.

Inserting Formula to Return Value if Cell is Blank

➤ Press ENTER.

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

Showing Result if Cell is Blank

 

Read More: How to Calculate in Excel If Cells are Not Blank: 7 Exemplary Formulas


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.

Blank Cells within Dataset

Step-01:

➤ Type the following formula in the cell E12.

=COUNTIF(D5:D11,"")

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

Inserting Formula if Cell is Blank

➤ Press ENTER.

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

Read More: VBA to Count Blank Cells in Range in Excel (3 Methods)


Method-12: Using VBA Code to Return Value if Cell is Blank

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

Blank Cells within Dataset

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

Steps for VBA Developer

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

Step for Inserting VBA Code

After that, a Module will be created.

Showing Module in VBA

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.

Inserting VBA code

➤ Press F5.

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

Returning Value if Cell is Blank with VBA

Read More: Excel VBA: Check If Multiple Cells Are Empty (9 Examples)


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.

Showing Practice Section


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.


Related Articles

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

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo