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.

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.

Dataset to find and replace blank cells in excel

Approach 1

1. Using Find and Replace Tool

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.

Using Find and Replace feature to find and replace blank cells in excel

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

find and replace feature particulars

  • Lastly, the outcome will look like this.

blank cells filled up

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

Approach 2

2. Applying Go To Special Tool

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.

Applying Go To Special to find and replace blank cells in excel

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

go to special particulars

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

inserting value in selected blank cell

  • Lastly, the outcome will look like this.

all blank cells filled up with absent

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

Approach 3

3. Combining IF & ISBLANK Functions

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.

Copy and pasting dataset

  • Then, select cell G6 and insert the formula.

=IF(ISBLANK(C6),”Absent”,C6)

Combining IF and ISBLANK to find and replace blank cells in excel

🔨 Formula Breakdown

Here, the formula we inserted in cell G6 is:

IF(ISBLANK(C6),”Absent”,C6)

👉 Here, ISBLANK function returns TRUE if the cell is blank.

👉Then, IF function returns Absent otherwise, it returns the value cell C6 already contains.

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

Dragging Fill Handle fillup dataset

  • Lastly, the outcome will look like this.

using formula to find and replace dataset

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

Approach 4

4. Embedding VBA

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.

visual basic option on the ribbon

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

Inserting module in vba window

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

Embedding VBA to find and replace blank cells in excel

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

selecting word to fill up the blank cells

  • Lastly, we will have our outcome.

Outcome of finding and replacing blank cells in excel using vba

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

dataset

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.

selecting go to special feature in excel

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

go to special particulars for selecting blank values

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

=$C$6

inserting formula to fill up blank value

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

pasting values in blank cells

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

all blank cells filled up with values

📕 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

(Visited 48 times, 1 visits today)
Priti

Priti

Hello! Welcome to my Profile. I am Priti. I'm now working and researching Microsoft Excel, and I'll share articles on it here. I am passionate about problem-solving, and I hope these articles will help you. Please leave a remark if you have any queries, and I will do my best to find an appropriate answer.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo