Please Note: This article is written for users of the following Microsoft Excel versions: 2007 and 2010. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Shading a Cell Until Something is Entered.

Shading a Cell Until Something is Entered

by Allen Wyatt
(last updated September 8, 2015)

When creating a worksheet in which information must be entered into specific cells, you may find it helpful to shade the cells if they are blank, but have the shading removed if something is entered into the cell. You can easily accomplish this task by using the conditional formatting feature in Excel. Follow these steps:

  1. Select the cells to which the conditional formatting should apply.
  2. Display the Home tab of the ribbon.
  3. Click the Conditional Formatting tool in the Styles group. Excel displays a list of conditional formatting options.
  4. Choose New Rule. Excel displays the New Formatting Rule dialog box.
  5. In the list of rule types, select Use a Formula to Determine which Cells to Format. (See Figure 1.)
  6. Figure 1. The New Formatting Rule dialog box.

  7. In the formula area, enter the following formula, replacing A1 with the address of the active cell selected in step 1:
  8.      =ISBLANK(A1)
    
  9. Click Format to display the Format Cells dialog box.
  10. Click the Fill tab. (See Figure 2.)
  11. Figure 2. The Fill tab of the Format Cells dialog box.

  12. Select the color you want used for shading the cell if it is blank.
  13. Click OK to dismiss the Format Cells dialog box. The shading color you selected in step 9 should now appear in the preview area for the rule.
  14. Click OK.

All the empty cells among those selected in step 1 should now appear shaded. When you enter something into one of the shaded cells, the shading should disappear.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (6867) applies to Microsoft Excel 2007 and 2010. You can find a version of this tip for the older menu interface of Excel here: Shading a Cell Until Something is Entered.

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

Setting the Starting Line Number

Line numbers are an important part of some documents, such as those prepared in the legal profession. If you want to modify ...

Discover More

Suppressing Zero Values in PivotTables

PivotTables are great for digesting and analyzing huge amounts of data. But what if you want part of that data excluded, such ...

Discover More

Dates with Periods

You may want Excel to format your dates using a pattern it doesn't normally use—such as using periods instead of ...

Discover More

Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!

MORE EXCELTIPS (RIBBON)

Conditional Format that Checks for Data Type

Conditional formatting can be used to highlight cells that contain the improper type of data for your needs. This tip ...

Discover More

Diagonal Borders in a Conditional Format

Conditional formatting is a great tool for changing how your data looks based on the data itself. Excel won't allow you to ...

Discover More

Shading Rows with Conditional Formatting

If you need to shade alternating rows in a data table, you'll want to examine how you can accomplish the task with ...

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 for this tip:

There are currently no comments for this tip. (Be the first to leave your comment—just use the simple form above!)

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.

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.

Links and Sharing
Share