Use this simple conditional format technique for Excel to solicit input values from users.
Conditional formats highlight data to alert users to specific conditions. It's a flexible and powerful tool, and it is sometimes overlooked if the need seems too simple. For instance, you'll often see sheets with shaded input cells. It's an easy way to visually tell users that they need to enter data. Often, the sheet's author just applies a normal shading format, which means the shading never disappears. It remains even after the user inputs the obligatory data. That's not wrong; it's just a bit inconsistent.
Using a conditional format, you can easily remove the shading once the user enters a value. Doing so might alleviate any possible confusion the user might sense when the shading remains. Now, let's look at a simple example using the simple sheet below. The formula in B3 will return the number of records that occur between the beginning and ending dates:
This sheet's purpose isn't particularly important, but it does have two input cells: B1 and B2. You can apply conditional shading to these two cells as follows:
- Select B1:B2.
- Click the Home tab. Click Conditional Formatting in the Styles group, and choose New Rule. In Excel 2003, choose Conditional Formatting from the Format menu.
- From the Select A Rule Type list, choose the Use A Formula To Determine Which Cells To Format option. In Excel 2003, choose Formula Is from the condition dropdown.
- Enter the following formula =ISBLANK(B1)
- Click the Format button.
- Click the Fill tab (if necessary), select a color, and click OK. The dialog will display the formula and the fill color. In Excel 2003, click the Patterns tab.
- Click OK to return to the sheet. Cells B1 and B2 will display the fill color you chose in step 7.
The ISBLANK() function returns true while either cell remains empty.
Now, you could've done the same thing using the Fill Color option, bypassing the ISBLANK() function and saving yourself a few steps, right? Not really. Enter a date into B1 and see what happens. As soon as you press Enter or Tab, the conditional format formula (the ISBLANK() function) is no longer true, so Excel removes the conditional format - a subtle clue to the user that he or she has completed the task successfully.
At this point, you might be wondering if the result is worth the work. When you're creating a workbook for someone else, the answer is usually yes. It's a small addition, but it does have meaning for the user.
If you'd like to learn more about the COUNTIFS() function used in the example sheet, read Count the number of Excel records that fall between two dates.