Fill Blank Cells with Text in Excel (3 Effective Ways)

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.

4 Effective Ways to Fill Blank Cells with Text in Excel.


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.

Fill Blank Cells With Text in Excel Using Go To Special Function

  • Choose the Blanks option under the Select section. Press OK. It will select all of the blank cells.

Fill Blank Cells With Text in Excel Using Go To Special Function

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

Fill Blank Cells With Text in Excel Using Go To Special Function

Look at the screenshot below. All the blank cells are filled with the same word “Void” here.


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.

Fill Blank Cells With Text in Excel Using Find and Replace Tool

  • Now, leave the Find what box blank and type “Void” in the Replace with box. After that, click on Replace All.

Fill Blank Cells With Text in Excel Using Find and Replace Tool

Finally, here is the result. Press OK and close the pop-up.


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.

Using VBA Codes

  • 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

Using VBA Codes

Look at the following image. Our VBA code has successfully filled the void cells with a word we have chosen in the code.


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.

Hafiz Islam
We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo