Akib Bin Rashid
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.
How to Find and Replace Blank Cells in Excel: 4 Methods
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.
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 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.
➤ Excel will replace the blank cells with “Absent”.
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 in the blanks of Student ID.
➤ Select the range B4:B14.
➤ Then go to Home tab >> select Editing >> select Find & Select >> select Go to Special.
➤ 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 in all the blanks with “Absent”.
Similar Readings
- How to Fill Blank Cells in Excel with Go To Special
- How to Fill Empty Cells with Last Value in Excel
- How to Fill Blank Cells with Value Below in Excel
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)
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
- IF(FALSE,”Absent”,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.
Excel will hide the original column.
Read More: How to Fill Blank Cells with Formula in Excel
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.
➤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 used a For loop to replace the empty cells.
➤ Run the code.
➤ 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”.
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.
Download Workbook
You can download the practice workbook from here.
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
- How to Fill Blank Cells with Value Above in Excel VBA
- How to Fill Empty Cells with Default Value in Excel
- How to Fill Blank Cells with Value Above in Excel
- How to Fill Blank Cells with 0 in Excel
- How to Fill Blank Cells with N/A in Excel
- Fill Blank Cells with Text in Excel
- Fill Blank Cells with Dash in Excel
- How to Fill Blank Cells with Color in Excel
- How to Fill Blank Cells with Value from Left in Excel
