Showing Text when a Cell is Empty

by Allen Wyatt
(last updated May 13, 2017)

3

Sheryl can use Conditional Formatting to make a cell appear a certain color if the cell is empty. Instead of a different color for the empty cell, she would like the empty cell to show some text. For instance, if the cell is empty, she might want to have it show "Customer Name," which would serve as a prompt to the user. Sheryl wonders if there is a way to do this sort of "conditional formatting" that shows text.

The short answer is no, this cannot be done. The traditional way to get around it is to separate your prompts from your input cells. For instance, if the user input is expected in cell B4, you might put the wording "Customer Name:" (with the colon) in cell A4. If you want the wording to disappear when the customer name is entered, you could, instead, use a formula in cell A4:

=IF(ISBLANK(B4),"Customer Name","")

There's also an approach you can use that takes advantage of the way that Excel deals with "cell overrun" when the cells contain text. Let's say, for example, that (again) your user input is expected in cell B4. You could make column A very narrow—say, about a single character wide—and then in cell A4 press the Space Bar a few times and type "Customer Name." As long as there is nothing in cell B4, what you typed in cell A4 is displayed, but it looks like it is in cell B4. When someone types something in cell B4, this blocks what is in cell A4 from being displayed. You could even, if desired, make the text in cell A4 a light gray, so it appears subdued when displayed.

If you prefer to go a macro route, you'll want to create one that is triggered whenever there is a change in the worksheet. This would go into the code module for the worksheet being used:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$B$4" Then
        If Target = "" Then
            ' Cell is empty; mark it and make gray
            Target = "(Customer Name)"
            With Selection.Font
                .ThemeColor = xlThemeColorDark1
                .TintAndShade = -0.249977111117893
            End With
        Else
            ' Cell contains something; remove gray
            With Selection.Font
                .ColorIndex = xlAutomatic
                .TintAndShade = 0
            End With
        End If
    End If
End Sub

Note that the macro only kicks into action if the cell being changed is cell B4.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (614) applies to Microsoft Excel 2007, 2010, 2013, and 2016.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Continuing Macro Lines

Program a macro, and you can easily find that some lines get very long. If you want to shorten the lines so they are more ...

Discover More

Getting Audible Feedback

You can add a bit of sound to your editing tasks by turning on Word's sound capabilities. This tip shows where this ...

Discover More

Turning E-mail Addresses into Hyperlinks

Got a whole bunch of e-mail addresses that you need to convert to active hyperlinks? You can do the conversion in a ...

Discover More

Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!

More ExcelTips (ribbon)

Can't Enter Years in a Cell

Sometimes getting the right thing to show up in a cell can be a bit tricky when working with dates. If you enter a year ...

Discover More

Can't Copy Data between Workbooks

Edit a group of workbooks at the same time and you probably will find yourself trying to copy information from one of ...

Discover More

Understanding AutoComplete

Entering data in a worksheet can be time consuming. One of the tools that Excel provides to make entry easier is ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 5 + 9?

2018-08-31 14:30:24

Julio Costa

You can actually use Conditional Format to do that. Just format <Text> then <Custom> and add the prompting text you want in it.


2017-05-14 11:53:21

bill driskell

Excel provides a way to select only blank cells in a range. Combine this with a "fill-all" of 'customer name' and it's done.

select the region of interest
press F5 (Go To), Special, Blanks
type "Customer Name" once
press Control Enter


2017-05-13 05:28:46

Janis

The "cell overrun" option is really clever. Simple and effective :) Thanks.


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.