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

The sample dataset contains student’s names along with their Student ID and their Marks. Some students did not take the exam and so their mark is blank.

Method 1 – Using Find and Replace to Find and Replace Blank Cells in Excel

STEPS:

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

• A Find and Replace dialog box will appear.
• Keep the Find what box empty and write Absent in the Replace with box.
• Select Replace All.

• A Message box will pop up. Press OK.

• Excel will replace the blank cells with Absent.

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

STEPS:

We have to fill the blank cell’s column by column. First, we will fill in the blank Student IDs.

• Select Column B.
• Go to Home tab >> select Editing >> select Find & Select >> select Go to Special.

• Go to Special box will appear. Mark the Blanks option and 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.

• Select the blank cells following the previous steps.

• Keeping the blank cells selected, enter Absen in the formula bar.

• Press CTRL + ENTER to fill the blanks.

Read More: How to Fill Blank Cells in Excel with Go To Special

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

STEPS:

• Select E5 and enter the following formula.

• Press ENTER. Excel will return the result.

• Use Fill Handle to AutoFill up to E14.

• Select the entire D column.
• Right-click to bring up the Context Menu.
• Select Hide.

Excel will hide the original column.

Method 4 – 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.

• Go to Insert >> select Module.

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

For loop replaces the empty cells.

• Run the code.

• Replace Empty Cell input box will pop up. Enter Absent in the box.
• Press OK.

• Excel will replace all the blank cells with Absent.

Practice Workbook

Download Workbook

You can download the practice workbook from here.

Related Articles

• How to Fill Empty Cells with Default Value in Excel
• How to Fill Empty Cells with Last Value in Excel
• Fill Blank Cells with Text in Excel
• How to Fill Blank Cells with N/A in Excel

<< Go Back to Fill Blank Cells | Excel Cells | Learn Excel