Hafiz Islam
Suppose you have a large Excel dataset that consists of numerous blank cells. Now, you wish to fill these blank cells with text. If you want to fill it manually, it will waste your huge time. In this article, you will 3 effective ways to fill blank cells with text in Excel.
Download Practice Workbook
Download the following Excel file for your practice.
3 Effective Ways to Fill Blank Cells with Text in Excel
Let’s introduce first our sample dataset. It contains numerous blank cells. Our goal is to fill these blank cells with text.
1. Fill Blank Cells with Text in Excel Using Go To Special Tool
The first easiest way to fill blank cells is to use Go To Special tool. Just follow the steps below to use this magic trick.
Steps:
- Select the whole dataset (B5:F12) where you need to fill blank cells.
- Go to the Home tab. Then from the Editing group, click on the Find & Select drop-down menu. Then click on Go To Special option. A Go To Special dialog box will pop up.
- Choose the Blanks option under the Select section. Press OK. It will select all of the blank cells.
- Now, in the first blank cell, just type the text with which you want to fill blank cells. In this example, we will use “Void”. Press CTRL+ENTER.
Look at the screenshot below. All the blank cells are filled with the same word “Void” here.
Read More: How to Fill Blank Cells in Excel with Go To Special (With 3 Examples)
Similar Readings
- How to Skip Blank Rows Using Formula in Excel (8 Methods)
- How to Apply Conditional Formatting in Excel If Another Cell Is Blank
- If Cell is Blank Then Show 0 in Excel (4 Ways)
- How to Calculate in Excel If Cells are Not Blank: 7 Exemplary Formulas
2. Use the Find and Replace Tool
Another quick way to fill blank cells is to use the Find and Replace tool. Just follow the steps below.
Steps:
- Select the whole dataset (B5:F12) where you need to fill blank cells. Press CTRL+H. A Find and Replace dialog box will appear.
- Now, leave the Find what box blank and type “Void” in the Replace with box. After that, click on Replace All.
Finally, here is the result. Press OK and close the pop-up.
Read More: How to Find and Replace Blank Cells in Excel (4 Methods)
3. Use a Simple VBA Code to Fill Blank Cells with Suitable Text
While the above 2 ways work efficiently, in the situation when you need to fill blank cells quite often, the following VBA codes method is for you. Just follow the steps below.
Steps:
- Go to the Developer tab. Then press on Visual Basic button. A window will pop up. Under this window, click the Insert tab and select the Module option. A module window will pop up.
If you don’t have this tab, enable it, or press Alt+F11 to open a new module.
- Copy the following code and paste it into the module window. Then click on the Run Button shown in the following screenshot.
You can also run a VBA code by pressing the F5 key.
Sub FillBlankCells()
Range("B5:F12").SpecialCells(xlCellTypeBlanks) = "Void"
End Sub
Look at the following image. Our VBA code has successfully filled the void cells with a word we have chosen in the code.
Read More: How to Fill Blank Cells with Value Above in Excel VBA (3 Easy Methods)
Quick Notes
- Save your Excel file in XLSM format. Otherwise, it will lose the macro in it and you will not be able to run your VBA code.
Conclusion
In this tutorial, I have discussed 3 effective ways to fill blank cells with text in Excel. I hope you found this article helpful. You can visit our website ExcelDemy to learn more Excel-related content. Please, drop comments, suggestions, or queries if you have any in the comment section below.
Related Articles
- How to Fill Blank Cells with 0 in Excel (3 Methods)
- How to Remove Blank Cells Using Formula in Excel (7 Methods)
- Excel VBA: Check If Multiple Cells Are Empty (9 Examples)
- How to Deal with Blank Cells That Are Not Really Blank in Excel (4 Ways)
- Fill Blank Cells with Dash in Excel (3 Easy Ways)
- How to Fill Blank Cells with N/A in Excel (3 Easy Methods)
- VBA to Count Blank Cells in Range in Excel (3 Methods)
