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.

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.

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

➤ Then, Type the following formula.

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.

➤ And, Press Enter.

➤ Then, Drag down the Fill Handle Tool.

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

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.

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

➤ Then, Type the following formula.

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.

➤ Now, Press ENTER.

➤ And, Drag down the Fill Handle Tool.

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

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.

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

➤ Then, Type the following formula.

➤ Now, Press ENTER.

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.

So, we get Delivered as output.

➤ Then, Drag down the Fill Handle Tool.

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

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.

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

➤ Then, Type the following formula and Press ENTER.

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.

➤ Drag down the Fill Handle Tool.

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

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

Similar Readings

• How to Fill Blank Cells with 0 in Excel (3 Methods)
• If a Cell Is Blank then Copy Another Cell in Excel (3 Methods)
• How to Autofill Blank Cells in Excel with Value Above (5 Easy Ways)
• Find Blank Cells Using VBA in Excel (6 Methods)
• Excel VBA: Find the Next Empty Cell in Range (4 Examples)

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.

Step-01:
➤ Select the output cell E5.

➤ Type the following formula and Press ENTER.

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.

➤ Drag down the Fill Handle Tool.

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

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.

Step-01:
➤ Select the output cell E5.

➤ Type the following formula and Press ENTER.

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.

➤ Drag down the Fill Handle Tool.

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

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.

Step-01:
➤ Select the output cell E5.

➤ Type the following formula and Press ENTER.

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.

➤ Drag down the Fill Handle Tool.

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

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.

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.

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

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

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.

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

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

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

Similar Readings

• How to Remove Blanks from List Using Formula in Excel (4 Methods)
• Delete Empty Cells in Excel (6 Methods)
• How to Remove Blank Cells in Excel (10 Easy Ways)
• Fill Blank Cells with Value Above in Excel (4 Easy Methods)
• How to Delete Blank Cells and Shift Data Up in Excel

Method-9: Highlighting Blank Cells with a Formula

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

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.

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

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

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

➤ Click on Format Option.

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

➤ Choose any Background Color.

➤ Click on OK.

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

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

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.

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

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

➤ Press ENTER.

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

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.

Step-01:

➤ Type the following formula in the cell E12.

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

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

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

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

After that, a Module will be created.

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.

➤ Press F5.

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

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.

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

• Fill Blank Cells with Dash in Excel (3 Easy Ways)
• How to Fill Blank Cells with Value Above in Excel VBA (3 Easy Methods)
• Fill Blank Cells in Excel with Go To Special (With 3 Examples)
• How to Find and Replace Blank Cells in Excel (4 Methods)
• Remove Blank Cells from a Range in Excel (9 Methods)
• Deal with Blank Cells That Are Not Really Blank in Excel (4 Ways)
• How to Remove Blank Cells Using Formula in Excel (7 Methods)