Written by Allen Wyatt (last updated July 22, 2023)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, 2021, and Excel in Microsoft 365
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, 2016, 2019, 2021, and Excel in Microsoft 365.
Dive Deep into Macros! Make Excel do things you thought were impossible, discover techniques you won't find anywhere else, and create powerful automated reports. Bill Jelen and Tracy Syrstad help you instantly visualize information to make it actionable. You’ll find step-by-step instructions, real-world case studies, and 50 workbooks packed with examples and solutions. Check out Microsoft Excel 2019 VBA and Macros today!
If you distribute a workbook that is used by others for data entry, you may want a way to make sure they fill in certain ...
Discover MoreIs your worksheet, imported from an external source, plagued by non-printing characters that show up like small boxes ...
Discover MoreWant to add a bunch of blank rows to your data and have those rows interspersed among your existing rows? Here's a quick ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2023-08-01 10:16:43
J. Woolley
To select all empty cells between A1 and Ctrl+End:
1. Press F5
2. Click Special...
3. Pick Blanks
4. Click OK
2023-07-31 14:26:57
David Gray
Another option that I employ regularly is to define a conditional format that is applied to a cell when the length of its value is zero. For example, the conditional format for cell A1 is "=len(a1)=0".
I'll define my format, and set the fill to a pattern or a distinctive color, then apply it everywhere else that it matters by copying the format from the original cell, then using Paste Special to apply it to relevant cells. Since the cell reference is unanchored, it will adjust to the location of each destination cell.
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 © 2025 Sharon Parq Associates, Inc.
Comments