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.

find and replace blank cells in excel


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.

find and replace blank cells in excel

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

find and replace blank cells in excel

  • Excel will replace the blank cells with Absent.

find and replace blank cells in excel


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.

find and replace blank cells in excel

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

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

find and replace blank cells in excel

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.

find and replace blank cells in excel

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

find and replace blank cells in excel

  • 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


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

Get FREE Advanced Excel Exercises with Solutions!
Akib Bin Rashid
Akib Bin Rashid

AKIB BIN RASHID, a materials and metallurgical engineer, is passionate about delving into Excel and VBA programming. To him, programming is a valuable time-saving tool for managing data, files, and internet-related tasks. Proficient in MS Office, AutoCAD, Excel, and VBA, he goes beyond the fundamentals. Holding a B.Sc in Materials and Metallurgical Engineering from Bangladesh University of Engineering and Technology, MD AKIB has transitioned into a content development role. Specializing in creating technical content centred around Excel and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo