4 Ways to Find and Replace Blank Cells in Excel

In Excel, you can use Find and Replace feature to find a specific value or text string in your workbook. You have two options: find the search item for future use or swap it out for something else. However, in this article, we will demonstrate how to find the blank cells and then fill them up with values. So, this article will explain 4 simple approaches to find and replace blank cells in Excel.

📁 Download Excel File

You can download the Excel file used for the demonstration from the link below.

Learn to Find and Replace Blank Cells in Excel with These 4 Methods

First, let us become acquainted with our dataset, which will be used as an example throughout the article. We have the attendance sheet for some students of 10th and 11th January. Here, the letter P denotes the student was present that day, and blank cells indicate absence. Now we want to complete this sheet by entering Absent into all those blank cells. Hence, we will use this dataset to demonstrate 4 simple approaches to find and replace blank cells in Excel.

This method will use Find and Replace feature to find and replace blank cells in Excel. Find and Replace feature finds a specific value or text string in your spreadsheet. However, we will use this feature to find the blank cells and replace them with our desired text which is Absent here.

Let us go through the steps.

⬇️⬇️ STEPS ⬇️⬇️

• First, select range C6:D10 and press Ctrl+H.

• Then, Find and Replace named box will appear. Keep Find what option blank, type Absent in Replace with option, click Replace All, and then on Close.

• Lastly, the outcome will look like this.

📕 Read More: 4 Quick Tricks to Fill Blank Cells in Excel with N/A

This method will apply the Go To Special feature to find and replace blank cells in Excel. If a cell contains a certain kind of data, like a formula, you can quickly locate and select it using the Go To command. Also, we can use Go To to find the cells that meet a set of specific criteria,—such as the last cell containing a value on the worksheet that contains formatting or data.

Based on that, we selected blank cells using this command and typed the text string in the selected cells later. In contrast, we found blank cells in the first approach and filled them in with values.

Now, let us go through the steps to apply this command.

⬇️⬇️ STEPS ⬇️⬇️

• First, select range C6:D10 and go to Editing group of commands in Home tab. Now select Go To Special from Find and Select options.

• Then, Go To Special named box will appear; select Blanks from the options, and press OK.

• All the blank cells will be selected, and type Absent in one cell and press Ctrl+Enter.

• Lastly, the outcome will look like this.

📕 Read More: 4 Ways to Leave Blank If Cell Contains Zero with Excel Formula

This method will combine IF and ISBLANK functions to find and replace blank cells in Excel. IF function performs a logical test and returns a specified value if the test result is TRUE otherwise returns another specified value. On the other hand, the ISBLANK function returns TRUE if a cell is blank.

The formula combining these two functions is excellent because we can keep the primary dataset and apply the formula after copying the dataset. In this way, we can preserve the primary dataset for future needs.

Let us go through the steps.

⬇️⬇️ STEPS ⬇️⬇️

• First, copy range B5:D10 by pressing Ctrl+C and paste them in cell F5 by pressing Ctrl+V.

• Then, select cell G6 and insert the formula.

• After that, drag the Fill Handle down and then again on the right side to fill all cells with this formula.

• Lastly, the outcome will look like this.

📕 Read More: 4 Quick Ways to Insert 0 If Cell Is Blank in Excel

This method will demonstrate how to find and replace blank cells in Excel using Microsoft Visual Basic Application (VBA).

So, ensure you have the Developer tab in the ribbon before applying this code. If you do not have the Developer tab, click here to see how to enable the Developer tab on your ribbon.

Now, let us go through the steps.

⬇️⬇️ STEPS ⬇️⬇️

• First, go to the Developer tab and select Visual Basic from the code group.

• Then, Microsoft Visual Basic for Application named box will appear. Go to the Insert tab and select the Module option.

• Enter the code and press F5.

Sub Find_Replace_Blank_Cells()

Dim S_Range As Range
Dim Enter_value As String
On Error Resume Next

Enter_value = InputBox("Replace with", _
"Replace Blank Cell")

For Each S_Range In Selection
If IsEmpty(S_Range) Then
S_Range.Value = Enter_value
End If
Next

End Sub

• Now, select the range C6:D10, press Alt+F8, select Find_Replace_Blank_Cells, and then Run.

• After that, a box named Replace Blank Cell will appear. Type Absent there and press OK.

• Lastly, we will have our outcome.

📕 Read More: 10 Quick Tricks to Remove Blank Lines in Excel

Fill Blank Cells with Value Above in Excel

This method will show how to fill blank cells with the value above in Excel. Here, we have an attendance sheet where absent students are marked and only the first cell symbol for present student P is inserted. Now, we want to fill all the blank cells with P.

We will use Go To Special feature in this method. Now, let us go through the steps.

⬇️⬇️ STEPS ⬇️⬇️

• First, select range C6:D10 and go to Editing group of commands in Home tab. Now select Go To Special from Find and Select options.

• Then, Go To Special named box will appear; select Blanks from the options, and press OK.

• All the blank cells will be selected. Now insert the formula in one cell and press Ctrl+Enter.

• Now, we want all these as values. So copy range C6:D10 pressing Ctrl+C. Then, in the Home tab click on Paste values from the Paste.

• Lastly, we will have all blank cells filled with the value above.

📕 Read More: How to Get Blank Cell Instead of Zero Using Formula in Excel

📝 Takeaways from This Article

📌  This article demonstrated 4 approaches to find and replace blank cells in Excel.

📌  In the first method, we used Find and Replace feature in excel.

📌  After that, we applied Go To Special feature in Excel.

📌  Then, we combined IF and ISBLANK functions in Excel.

📌  Finally, we embedded a VBA code to find and replace blank cells in Excel.

Conclusion

This article has demonstrated 4 approaches to find and replace blank cells in Excel. All these ways are incredibly effective and simple to use. We hope this article helps you. Please leave a remark if you have any queries. The author will do their best to find an appropriate answer.

For more guides like this, visit Excelden.com.

Related Articles

• 3 Approaches to Make Empty Cells Blank in Excel
• 9 Quick Tricks to Delete Row If Cell is Blank in Excel
• How to Determine and Count If Cell Is Not Blank in Excel