Shading a Cell Until Something is Entered
Marne Einarson, Cornell University (mke3@cornell.edu)
This corner of the newsletter is reserved for readers to share practical tips, techniques and shortcuts that can make a difference in our work. Please share your technical prowess with us and send your tips to Gayle Fink (mailto:gfink@usmd.edu). Since this is a small corner, KISS (Keep It Short and Smart) will work the best.
This month's Technical Tip came from Allen Wyatt's excellent website on Excel Tips located at: http://excel.tips.net/ .
Summary: Using conditional formatting, you can shade a particular cell (or cells) until a value is entered in the cell. This is a handy way for users to tell when something needs to be entered in a particular cell. (This tip works with Microsoft Excel 97, Excel 2000, Excel 2002, and Excel 2003.)
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 cell or cells that you want shaded if they are empty. |
| 2. | Choose Conditional Formatting from the Format menu. Excel displays the Conditional Formatting dialog box. (Click here to see a related figure.) |
| 3. | Use the Condition drop-down to choose Formula Is. |
| 4. | In the formula area, to the right of the drop-down list used in step 3, enter the following formula, replacing A1 with the address of the active cell selected in step 1.: |
=ISBLANK(A1)
| 5. | Click Format to display the Format Cells dialog box. |
| 6. | Click the Patterns tab. (Click here to see a related figure.) |
| 7. | Select the color you want used for shading the cell if it is blank. |
| 8. | Click OK to dismiss the Format Cells dialog box. The shading color you selected in step 7 should now appear in the preview area for the condition. |
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.