How to Find and Replace Blank Cells in Excel (4 Methods)

Excel is the best tool when it comes to working with a large dataset. Sometimes we need to find and replace blank cells in Excel. In this article, I will explain 4 easy methods to find and replace blank cells in Excel.


Download Workbook

This is the workbook I am going to use to explain the methods. We have a number of students along with their Student ID and their Marks. Some of the students did not appear for the exam. That’s why there are blanks in the column. We will replace these blank cells.

find and replace blank cells in excel


4 Methods to Find and Replace Blank Cells in Excel

1. Using Find and Replace to Find and Replace Blank Cells in Excel

In this section, I will explain to you how to apply Find & Replace to find and replace blank cells in Excel.

STEPS:

Select the range D5:D14.
Then go to Home tab >> select Editing >> select Find & Select >> select Replace.

find and replace blank cells in excel

Find and Replace dialog box will appear. Keep the Find what box empty and write Absent in the Replace with box. After that, select Replace All.

A Message box will pop up. Press OK.

find and replace blank cells in excel

➤ Excel will replace the blank cells with “Absent”.

find and replace blank cells in excel

Read More: How to Find Blank Cells in Excel (8 Easy Ways)


2. Replace Blank Cells of Multiple Columns Using Go to Special

Sometimes, there are blank cells in two or more different columns. In those cases, we cannot replace the blank cells with one specific value. We need different values or texts. In this section, I will illustrate this case.
For this case, I have changed the dataset a bit. There are blanks in the Student ID column as well as the Marks column. We will replace these blanks.

STEPS:

We have to fill the blank cell’s column by column. First, we will fill the blanks of Student ID.
➤ Select the range B4:B14.
➤ Then go to Home tab >> select Editing >> select Find & Select >> select Go to Special.

find and replace blank cells in excel

Go to Special box will appear. Mark the Blanks option and then select OK.

Excel will select the blank cells.

➤ Now replace these blank cells manually. The absent IDs are 1411004 and 1411008.

Now we will fill the blanks of the Marks column.
➤Select the range D4:D14.

Then select the blank cells following the previous steps.

Now, Keeping the blank cells selected, write “Absent” in the formula bar.

➤ Then press CTRL + ENTER. Excel will fill all the blanks with “Absent”.

Read More: How to Fill Blank Cells in Excel with Go To Special (With 3 Examples)


Similar Readings


3. Applying a Combination of IF and ISBLANK Functions to Find and Replace Blank Cells in Excel

In this section, I will explain another useful method. In the previous methods, we updated the original dataset. But this time, we will keep the original dataset intact. I will use the IF and ISBLANK functions to find and replace blank cells in Excel.

STEPS:

➤ Select E5 and write down the formula

=IF(ISBLANK(D5),"Absent",D5)

find and replace blank cells in excel

Formula Breakdown

  • ISBLANK(D5) ➟ Determines whether D5 is blank or not.
    • Output ➟ FALSE
  • IF(ISBLANK(D5),”Absent”,D5) ➟ Returns the output after analyzing the logical statement. If D5 is blank, the output will be “Absent”, if not, then the output will be D5.
    • IF(FALSE,”Absent”,70)
      • Output: 70

➤ Now, press ENTER. Excel will return the result.

➤ Now use Fill Handle to AutoFill up to E14. Excel has replaced the blank cells with “Absent”.

Now you can hide the original columns. In this way, your original dataset will be intact.
➤ Select the entire D column. Then, right-click your mouse to bring the Context Bar.
➤ After that, select Hide.

find and replace blank cells in excel

Excel will hide the original column.

Read More: How to Find Blank Cells Using VBA in Excel (6 Methods)


4. Use of VBA Macro to Find and Replace Blank Cells in Excel

Now, I will show you the use of VBA macro to find and replace blank cells in Excel.

STEPS:

➤ Select the entire dataset.

➤ Press Alt + F11 to open the VBA Macro.

find and replace blank cells in excel

➤Go to Insert >> select Module.

➤ Then write down the following code.

Sub Replace_Blank_Cells()
Dim Selected_Range As Range
Dim Put_a_value As String
On Error Resume Next
Put_a_value = InputBox("Replace with", _
"Replace Empty Cell")
For Each Selected_Range In Selection
If IsEmpty(Selected_Range) Then
Selected_Range.Value = Put_a_value
End If
Next
End Sub

I have created a Sub Procedure Replace_Blank_Cells and used Dim statement to define two variables Selected_Range as Range and Put_a_Value as String. I have set an InputBox for the variable Put_a_value where the name of the box will be Replace Empty Cell. Then I have used a For loop to replace the empty cells.
➤ Run the code.

find and replace blank cells in excel

Replace Empty Cell input box will pop up. Write “Absent” in the box. Then press OK.

Excel will replace all the blank cells with “Absent”.

Read More: Excel VBA: Find the Next Empty Cell in Range (4 Examples)


Practice Workbook

It is easy to find and replace blank cells in Excel. But you should practice these methods. That’s why I have attached a practice sheet for you.


Conclusion

In this article, I have explained 4 easy methods to replace blank cells in Excel. I hope you will find this article helpful. And lastly, if you have any kind of suggestions, ideas, or feedback please feel free to comment down below.


Related Articles

Akib

Akib

Hi, this is MD Akib Bin Rashid. I completed my BSc in Engineering back in 2019. After that, I did an MBA. Then, I joined as an Excel and VBA Content Developer at SOFTEKO Digital. Being passionate about data analytics and finance, I will try to assist you with Excel

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo