3 Ways to Fill Blank Cells with Text in Excel

Excel is a powerful tool for managing and analyzing data, but it can be frustrating when working with a large data set that contains blank cells. These blank cells can make it difficult to perform calculations or create charts and make the data harder to read. Fortunately, there are several ways to fill these blank cells with text in Excel. In this article, we will explore three different methods to fill blank cells with text in Excel.

Fill Blank Cells with Text


📁 Download Excel File


Learn to Fill Blank Cells with Text in Excel with These 3 Methods

In this article, we will demonstrate 3 simple yet elegant methods to fill blank cells with text in Excel. We will use two built-in features, such as Go To Special and Find and Replace to do the job. Additionally, we will create a VBA macro to automate the process. Our dataset will be an attendance sheet of students where, every time they attended a class, they typed “Present” on the sheet. We want to fill the empty/blank cells with the text “Absent”. Let’s dive into the main part of the article.

dataset for this article


Method 1

1. Applying Go To Special Tool

Go To Special tool in Excel is a useful feature that allows you to quickly select specific types of cells within a range, such as blank cells, cells with constants, or cells with formulas. You can use this tool to select all the blank cells in a worksheet or range, and then fill them with a specific text. This method is efficient and can save you a lot of time when working with large datasets. Here’s how to do it:

⬇️⬇️ STEPS ⬇️⬇️

  • Select range of cells that you desire to fill with text. In our example, we selected C7:G14.
  • Next, go to Home tab and click on Find & Select button in Editing group. Next, select Go To Special from the drop-down menu.

Accessing Go to Special to Fill Blank Cells with Text

  • Select Blanks in Go To Special dialog box and click OK.

Go To Special window

This will select every empty cell in the chosen range.

Selected Blank Cells

  • Once blank cells are selected, you can simply type the text you desire to fill the blank cells with and press Ctrl+Enter. The text will be filled in all blank cells at once. We typed “Absent” and pressed Ctrl+Enter.

Ctrl+Enter to Fill Blank Cells with Text

And there you go. The Blank cells are filled with text.

Method 1 result

📕 Read More: 4 Quick Ways to Insert 0 If Cell Is Blank in Excel

Method 2

2. Utilizing Find and Replace Tool

Find and Replace tool in Excel is another way to quickly fill blank cells with specific text. The advantage of this method is that it can also find and replace any specific value with another value, not just blank cells. Here’s how to use it:

⬇️⬇️ STEPS ⬇️⬇️

  • Select range of cells that you choose to fill with text. In our example, we selected C7:G14.
  • Go to Home tab and click “Find & Select” button in Editing group. Next, select Replace from the drop-down menu. You can also press Ctrl+H to access Find and Replace dialogue box.

Accessing Replace to Fill Blank Cells with Text

  • In Find and Replace dialog box, leave the Find what field blank and type the text you want to fill the blank cells within the Replace with. We will type “Absent” here. Next, click on the Replace All button.

Find and Replace window

This will replace all the blank cells in the selected range with the specified text.

Method 2 result

A message box will appear, showing how many cells have been replaced.

Change log

📕 Read More: 4 Ways to Leave Blank If Cell Contains Zero with Excel Formula

Method 3

3. Employing VBA Script

VBA (Visual Basic for Applications) macros are a powerful tool that can automate repetitive tasks in Excel. One of the tasks that can be automated is filling blank cells with text. Using VBA macros, you can quickly fill all the blank cells in a worksheet or range with a specific text, saving you time and making your data more organized and readable. In this section, we will explore how to use VBA macros to fill blank cells with text in Excel.

⬇️⬇️ STEPS ⬇️⬇️

  • Firstly, pressing Alt+F11 will open Microsoft Visual Basic where we insert a Module from the Insert tab to open a whiteboard.

Inserting module to Fill Blank Cells with Text

  • Now copy and paste following code into module.
Sub FillemptyCells()
Dim RG As Range
Dim CL As Range
Set RG = Range("C7:G14")
For Each CL In RG
    If IsEmpty(CL) Then
        CL.Value = "Absent"
    End If
Next CL
End Sub

Code to Fill Blank Cells with Text

  • Finally, press F5 to execute the code and replace the blank cells with “Absent” in the final output.

Method 3 result

📕 Read More: 9 Quick Tricks to Delete Row If Cell is Blank in Excel


📄 Important Notes

🖊️  You should double-check the range of cells selected before you press the Replace All button to avoid replacing any desired value.


📝 Takeaways from This Article

📌  Firstly, we used Go To Special feature of Excel to fill blank cells with specific text.

📌  Next, we employed the Find and Replace feature to do the same.

📌  Finally, we created a VBA macro to replace blank cells with text.


Conclusion

In conclusion, there are several ways to fill blank cells with text in Excel, and the method you choose will depend on your specific needs and the complexity of your data. By learning these different methods, you can save time and make your data more organized and readable. With the right approach, you can easily fill blank cells with text in Excel and make your data analysis more efficient. If you have any questions, feel free to comment below. I will try to answer them as soon as possible. Have fun using Excel, and you can visit Excelden.com for more tutorials. Thank You.


Related Articles

(Visited 46 times, 1 visits today)
Hassan Shuvo

Hassan Shuvo

Hello, I am Mehedi Hassan Shuvo.I am an Engineering graduate from Bangladesh University of Engineering and Technology. I love reading Thriller books, watching Anime, and playing Cricket. I also love learning about new software. Excel is one of my favorite ones by far. My efforts will be worthwhile if I can utilize my expertise to assist anyone. If you find any faults in my writing please let me know.I will try to correct them as I am learning everyday.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo