Sometimes there are some blank cells exist in created table, if we want to mark them with user-defined text to make them easier to be found out, how can we do? Actually, there are several ways to implement this. In this article, we will introduce you two tricks to display user defined text in blank cells, the first one is applying IF function, the second one is applying Go to special function in excel.

Precondition:

Prepare a table record decrease and increase status for amount. If amount is increased, no comment records. So, if we want to enter ‘Increase’ in all blank cell, we can follow below two methods to display ‘Increase’.

1. Display User-Defined Text in All Blank Cells by IF function

As we all know, if logic is True, IF function will return value of True, so we can set user-defined text as true value, and use IF function here to return user-defined text.

Step1: In D2, enter the formula =IF(C2=””,”Increase”,C2), then click Enter.

Verify that Increase displays as we expect.

Step2: Drag the fill handle down. Verify that if Cn cell is blank, then Dn cell displays Increase, otherwise text in Cn will be copied to Dn as well.

Step3: Copy D2:D11 to C2:C11. Then delete column D. Then all blank cells in table are filled with user-defined ‘Increase’ properly.

2. Display User-Defined Text in All Blank Cells by Go to Special Function

Step1: Select the range contains blank cells.

Step2: Click Home in ribbon, then click Find & Select in Editing group.

Step3: Select Go To Special in Find & Select menu.

Step4: In Go To Special screen, check on Blanks option. Then click OK.

Step5: After above operating, all blank cells are selected. Type ‘Increase’ directly and press Ctrl + Enter.

Verify that all blank cells are entered with ‘Increase’.

3. Display User-Defined Text in All Blank Cells Using VBA

let’s explore the third method—a more advanced approach utilizing VBA code. We’ll create a custom solution using VBA code to systematically fill blank cells with user-defined text.

Press Alt + F11 to open the VBA editor.

Insert a new module: right-click on any item in the Project Explorer, choose “Insert,” then “Module.”

Copy and paste the following code:

Sub FillBlanksWithText()
    Dim cell As Range
    For Each cell In Selection
        If cell.Value = "" Then cell.Value = "Increase"
    Next cell
End Sub

Close the editor.

Select the range where you want to fill blanks.

Press Alt + F8, choose “FillBlanksWithText,” and click “Run.”

You can see that the specified text has been successfully applied to all blank cells in your selected range using the VBA code.

4. Video: Display User-Defined Text in All Blank Cells

This Excel video tutorial where we explore three efficient methods—IF function, “Go to Special,” and VBA code—to display user-defined text in all blank cells.

5. Related Functions

• Excel IF function
  The Excel IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE. The IF function is a build-in function in Microsoft Excel and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value], [false_value])….