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.

Returning Value if Cell is Blank in GIF

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.

Representing Dataset overview

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.

Blank Cells within Dataset

Steps:

  • Select the output cell F5.
  • Input the following formula:
=IF(D5="",C5,"")

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.

Inserting Formula to Return Value

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

Copying Formula in All Cells

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

Showing Blank Cells within Dataset

Steps:

  • Select the output cell F5.
  • Input the following formula:
=IF(D5="",5%*E5,"")

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.

Inserting Formula to Return Value if Cell is Blank

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

Copying Formula in All Cells

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


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.

Showing Blank Cells Within Dataset

Steps:

  • Select the output cell F5.
  • Copy the following formula and 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. IF will then return “Not Delivered.” Otherwise, the function 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

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

Returning Values if Cell is Blank


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.

Showing Blank Cells Within Dataset

Steps:

  • Select the output cell E5.
  • Copy 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 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.

Inserting Formula to Return Value if Cell is Blank

  • Drag down the Fill Handle Tool.

Copying Formula in All Cells

  • 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


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.

Showing Blank Cells within Dataset

Steps:

  • Select the output cell E5.
  • Input the following formula and press Enter.
=IF(COUNTIF(D5,"")>0,"Not Delivered","Delivered")

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.

Inserting Formula to Return Value if Cell is Blank

  • Drag down the Fill Handle Tool.

Copying Formula in All Cells

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

Blank Cells within Dataset

Steps:

  • Select the output cell E5.
  • Insert the following formula and press Enter:
=IF(SUMPRODUCT(--(D5=""))>0,B5,"")

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.

Inserting Formula to Return Values if Cell is Blank.

  • Drag down the Fill Handle Tool.

Copying Formula in All Cells

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

Returning Values if Cell is Blank


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.

Showing Blank Cells within Dataset

Steps:

  • Select the output cell E5.
  • Copy 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. 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.

Inserting Formula to Return Values if Cell is Blank

  • Drag down the Fill Handle Tool.

Copying Formula in All Cells

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

Returning Values if Cell is Blank


Method 8 – Highlighting Blank Cells with Excel Conditional Formatting

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

Showing Blank Cells within Dataset

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.

Steps for Conditional Formatting

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

Setting New Formatting Rule

Steps:

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

Setting Formatting Rule

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

Setting Formatting Color

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

Steps for Conditional Formatting

  • You will get the blank cells highlighted.

Showing Highlighted Cells


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.

Blank Cells within Dataset

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.

Steps for Conditional Formatting

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

Setting New Formatting Rule

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

  • Click on Format.

Setting Formula as Formatting Rule

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

Selecting Format Color

  • Press OK to complete conditional formatting.

Steps for Conditional Formatting

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

Showing Blank Cells within Dataset

Steps:

  • Put the following formula in 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.
  • You will get the sum of Sales for the Items which have not been delivered yet.

Showing Result if Cell is Blank


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.

Blank Cells within Dataset

Steps:

  • Insert the following formula in E12.
=COUNTIF(D5:D11,"")

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

Inserting Formula if Cell is Blank

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

Blank Cells within Dataset

Steps:

  • Go to the Developer tab and select Visual Basic.

Steps for VBA Developer

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

Step for Inserting VBA Code

  • A blank Module will be created.

Showing Module in VBA

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

Inserting VBA code

  • Press F5.
  • You will get the Not Delivered state for the corresponding blank cells from the Delivery Date column.

Returning Value if Cell is Blank with VBA

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


Download Workbook


Related Articles


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

Get FREE Advanced Excel Exercises with Solutions!
Tanjima Hossain
Tanjima Hossain

TANJIMA HOSSAIN is a marine engineer who enjoys working with Excel and VBA programming. For her, programming is a handy, time-saving tool for managing data, files, and online tasks. She's skilled in Rhino3D, Maxsurf C++, MS Office, AutoCAD, and Excel & VBA, going beyond the basics. She holds a B.Sc. in Naval Architecture & Marine Engineering from BUET and is now a content developer. In this role, she creates tech-focused content centred around Excel and VBA. Apart from... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo