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.

Read More: How to Fill Blank Cells in Excel with Go To Special (With 3 Examples)


Similar Readings


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.

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.

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.

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

Hafiz Islam

Hafiz Islam

Hi there. I am Hafiz, graduated from BUET. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. Now you can see my articles in the ExcelDemy blog.

We will be happy to hear your thoughts

Leave a reply

5 Excel Hacks You Never Knew

Genius tips to help you unlock Excel's hidden features

FREE EMAIL BONUS

ExcelDemy
Logo