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.
📁 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.
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.
- Select Blanks in Go To Special dialog box and click OK.
This will select every empty cell in the chosen range.
- 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.
And there you go. The Blank cells are filled with text.
📕 Read More: 4 Quick Ways to Insert 0 If Cell Is Blank in Excel
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.
- 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.
This will replace all the blank cells in the selected range with the specified text.
A message box will appear, showing how many cells have been replaced.
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.
- Now copy and paste following code into module.
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"
- Finally, press F5 to execute the code and replace the blank cells with “Absent” in the final output.
📄 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.
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.
- 3 Approaches to Make Empty Cells Blank in Excel
- How to Get Blank Cell Instead of Zero Using Formula in Excel
- 10 Quick Tricks to Remove Blank Lines in Excel
- How to Determine and Count If Cell Is Not Blank in Excel