How to Return Value If Cell Is Blank (12 Ways)

Consider the following overview of checking if the cell is blank, where the status is updated based on the delivery date.

We are using the following table, which contains Order Dates, Delivery Dates, and Sales of Items. Some of the delivery dates in the table are empty, which will be checked and used for formulas. Let’s get started.

Method 1 – Using the IF Function to Return a Value If a Cell Is Blank in Excel

Let’s say you want to get the Order Dates for the products that have not been delivered yet (Empty cells in the Delivery Date column) in the Order Date for Not Delivered Items column.

Steps:

• Select the output cell F5.
• Input the following formula:

Here, the logical condition is D5=”” which checks whether D5 of the Delivery Date is empty. If the result is TRUE, then it will return the value of cell C5 of the Order Date column. Otherwise, it will return Blank.

• Press Enter.
• Drag down the Fill Handle Tool.

• 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 the IF Function to Return a Value if a Cell Is Blank

Suppose the company wants to compensate the customers for late delivery by 5% off on the Total Sales value.

Steps:

• Select the output cell F5.
• Input the following formula:

Here, the logical condition is D5=”” which checks whether the cell D5 of the Delivery Date column is empty. If the check returns TRUE, the function will return 5% of the respective Sales value (E5 cell). Otherwise, it will return Blank.

• Press Enter.
• Drag down the Fill Handle Tool.

You will get a 5% commission of the Sales values for the corresponding Blank cells of the Delivery Date column.

Method 3 – Combining IF and ISBLANK Functions to Return a Value If a Cell Is Blank

Let’s use the delivery date to determine a status of the delivery.

Steps:

• Select the output cell F5.
• Copy the following formula and press Enter:

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

• Drag down the Fill Handle Tool.

• This fills in the column with the results we needed.

Method 4 – Using IF and COUNTBLANK Functions to Return a Value If a Cell Is Blank in Excel

Let’s streamline the dataset a bit and repeat the check with a different function.

Steps:

• Select the output cell E5.
• Copy the following formula and press Enter:

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

• Drag down the Fill Handle Tool.

• You will have the Not Delivered state for the corresponding Blank cells of the Delivery Date column.

Method 5 – Applying IF and COUNTIF Functions to Return a Value If a Cell Is Blank

We can get similar results with a different function and the same dataset.

Steps:

• Select the output cell E5.
• Input the following formula and press Enter.

COUNTIF(D5,””) will return the number of blank cells. If it finds a blank cell in cell D5 of the Delivery Date column, then the number will be greater than 0, and so the check will return TRUE.

• Drag down the Fill Handle Tool.

• You will have the Not Delivered state for the corresponding blank cells of the Delivery Date column.

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

Method 6 – Using IF and SUMPRODUCT Functions to Return a Value If a Cell Is Blank

Let’s determine which items haven’t been delivered yet.

Steps:

• Select the output cell E5.
• Insert the following formula and press Enter:

Here, the —  operator will turn TRUE or FALSE into 1 or 0. For blank cells, the value will be 1 since the check would be TRUE. So, SUMPRODUCT(–(D5=””))>0 will return TRUE when the D5 cell is blank. When it is TRUE, IF will return the value of the B5 cell. Otherwise, the function will return a Blank value when the respective cell of the Delivery Date column is not empty.

• Drag down the Fill Handle Tool.

• You will get the Items name for the corresponding Blank cells of the Delivery Date column.

Method 7 – Using IF and LEN Functions to Return a Value If a 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.

Steps:

• Select the output cell E5.
• Copy 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. Then, IF will return the value of the B5 cell. Otherwise, the function will return Blank when the cells in the Delivery Date column are not empty.

• Drag down the Fill Handle Tool.

• You will get the Items name for the corresponding blank cells of the Delivery Date column.

Method 8 – Highlighting Blank Cells with Excel Conditional Formatting

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

Steps:

• Select the cell range on which you want to apply the Conditional Formatting.
• Go to the Home tab and select the Conditional Formatting Dropdown, then choose the New Rule option.

• The New Formatting Rule dialog box will appear. Select the Format only cells that contain option.

Steps:

• Choose Blanks under Format only cells with.
• Click on Format.

• The Format Cells Dialog Box will open up.
• Go to the Fill tab.
• Select any Background Color.
• Make any other changes to the Fill and Border tabs if you want.
• Click on OK.

• The Preview will be shown in the box. Press OK.

• You will get the blank cells highlighted.

Method 9 – Using ISBLANK Function and Conditional Formatting to Highlight Blank Cells

Let’s directly highlight the blank cells with a formula inside conditional formatting.

Steps:

• Select the data range on which you want to apply the Conditional Formatting.
• Go to the Home tab, select the Conditional Formatting drop-down and choose the New Rule option.

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

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

• Click on Format.

• The Format Cells Dialog Box will open up.
• Select the Fill tab.
• Choose any Background Color.
• Click on OK.

• Press OK to complete conditional formatting.

• You will get the blank cells highlighted.

Read More: How to Apply Conditional Formatting in Excel If Another Cell Is Blank

Method 10 – Using the SUMIF Function to Sum up Values Based on Blank Cells

Let’s sum up the Sales value for the Items that have a blank Delivery Date.

Steps:

• Put the following formula in cell E12:

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

• Press Enter.
• You will get the sum of Sales for the Items which have not been delivered yet.

Method 11 – Applying the COUNTIF Function to Sum up the Number of Blank Cells

We will count the number of blank cells in the Delivery Date column.

Steps:

• Insert the following formula in E12.

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

• Press Enter.
• You will get the number of Items which weren’t delivered yet.

Method 12 – Using VBA Code to Return a Value If a Cell Is Blank in Excel

Let’s fill in the status column based on whether the delivery date is blank.

Steps:

• Go to the Developer tab and select Visual Basic.

• The Visual Basic Editor will open up.
• Go to the Insert tab and select Module.

• A blank Module will be created.

• Enter 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

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”. The resulting 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—meaning a cell has a value—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.
• You will get the Not Delivered state for the corresponding blank cells from the Delivery Date column.

Read More: How to Check If Cell Is Empty Using Excel VBA

Download Workbook

Related Articles

• How to Find If Cell is Blank in Excel
• How to Find & Count If a Cell Is Not Blank
• If a Cell Is Blank then Copy Another Cell in Excel
• How to Check If Cell Is Empty in Excel
• Excel VBA: Check If Multiple Cells Are Empty
• If Cell is Blank Then Show 0 in Excel

<< Go Back to If Cell is Blank Then | Blank Cells | Excel Cells | Learn Exce