How to Display User-Defined Text in All Blank Cells in Excel

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

Display User-Defined Text in All Blank Cells 1

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

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

Display User-Defined Text in All Blank Cells 2

Verify that Increase displays as we expect.

Step 2: 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.

Display User-Defined Text in All Blank Cells 3

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

Display User-Defined Text in All Blank Cells 4

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


Step 1: Select the range contains blank cells.

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

Display User-Defined Text in All Blank Cells 5

Step 3: Select Go To Special in Find & Select menu.

Display User-Defined Text in All Blank Cells 6

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

Display User-Defined Text in All Blank Cells 7

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

Display User-Defined Text in All Blank Cells 8

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

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])….

 

Related Posts

Excel Array Construction

This article will talk about how to create one-dimensional array or two-dimensional array by using some functions in Excel. When using array formulas in Excel, we often use functions to construct arrays. Generate Array with ROW or COLUMN Functions Array ...

Phone Number Format in Excel

Sometimes when we import phone numbers from external sources, the formatting of the phone numbers is removed. Even if in some situations the phone numbers are not imported from external resource, sometimes the phone numbers in worksheet are not displayed ...

If Cell Contain Specific Text

This article will explain how to check if a cell contains text or a specific text string in Google Sheets or Microsoft Excel spreadsheets. In our daily work, we often look for specific text in worksheets containing large amounts of ...

Cell Contains Number

This article will talk about how to check if a given cell contains any numbers in Microsoft Excel Spreadsheet or Google Sheets. How to quickly check if a cell contains a number? When you have a small number of cells ...

Calculate Days Open in Excel

If you want to know how to Calculate days in Excel, there are some formulas that you can use to do so. For example, you can use the DAYS function in Excel to find the number of days between two ...

Sidebar