Written by Allen Wyatt (last updated October 11, 2021)
This tip applies to Excel 2007, 2010, 2013, and 2016
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.
Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!
We all make mistakes. Fortunately, Excel makes it rather easy to undo your makes, right after you make them.
Discover MoreTwo lists of similar data can be challenging to synchronize. Here are some ways that you can align data in two different ...
Discover MoreLimiting what can be entered in a cell can be an important part of developing a worksheet that other people use. Here are ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2021-06-26 15:52:42
Jillian Aubrey
There's a work around that might help some people out. Basically, if you can make sure that whenever the cell is "empty" it's not actually empty but contains a zero-width space, you can get a conditional format to show your text.
In vba you can use ChrW(8203) to access this character. For use in formulas, google "zero width space copy", and you can find sites that will allow you to copy to your clipboard. It's a difficult character to work with, because it's completely invisible, but you can move your cursor using the arrow keys to see whether it's actually there, since it still counts as a character.
Add a conditional format like this:
=$A$1=""
Those quotes aren't empty, they contain the zero width space!
In the conditional format setup, go to the number tab and set a conditional format of (including the quotes!):
;;;"YOUR TEXT HERE"
You'll just have to make sure that if you have macro to clear the cell, you actually set it to chrw(8203) instead of clearing it, and when you go to use the value in the cell for something you'll probably want to use either SUBSTITUTE (for a formula) or Replace (for VBA) to replace the character with nothing
2019-04-08 12:21:52
Roy
Best I can get with Julio Costa's post is to avoid "TEXT" in any aspect of it, then apply a Conditional Format of, say:
#,##0.00;[Red]-###0.00;0;"Customer Name"
and apply it using a formula for the test:
=A1=""
which gets... nothing.
But, if I then enter a space into the cell (in other words, A1 is NOT empty!!!), I get the text from the Conditional Formatting.
Talk about weird. Excel 2019 from an Office 365 subscription, by the way.
2019-03-28 14:20:46
Michael W
Leveraging cell overrun is brilliant, mind=blown
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.
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.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2023 Sharon Parq Associates, Inc.
Comments