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.
=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.
➤ 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.
=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.
➤ 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.
=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.
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.
=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.
➤ 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.
=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.
➤ 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.
=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.
➤ 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.
=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.
➤ 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(B5:E11)
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.
=SUMIF(D5:D11,"",E5:E11)
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.
=COUNTIF(D5:D11,"")
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)