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 out manually, it will waste your time. In this article, you will 3 effective ways to fill blank cells with text in Excel.
How to Fill Blank Cells with Text in Excel: 3 Effective Ways
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.
Similar Readings
2. Use the Find and Replace Tool
Another quick way to fill in 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 in 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
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 the 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
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.
Download Practice Workbook
Download the following Excel file for your practice.
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. Please, drop comments, suggestions, or queries if you have any in the comment section below.
Related Articles
- How to Fill Blank Cells with Value Below 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 Dash in Excel
- How to Fill Blank Cells with Color in Excel
- How to Fill Blank Cells with Value from Left in Excel
- How to Fill Empty Cells with Default Value in Excel
- How to Fill Empty Cells with Last Value in Excel
