Conditional formatting is great for adding visual clues to values, but you can also use it to identify input cells to guide users through a data entry task.
You probably use conditional formatting to highlight specific values in a conditional way. For instance, if a value is less than 0, you might want to display that value in red. You can also use conditional formatting to highlight a range of input cells - it's simple and the result is an easy-to-implement data entry guide.
The following sheet shows a simple savings calculator that uses conditional formatting to highlight the input cells for each savings option. The user selects a savings options from the list and conditional formatting highlights the input cells. Those cells remain highlighted until the user selects a different savings option from the list. The formulas above the input area display the appropriate savings result.
Before we get started, you should know that this example is about using conditional formatting to highlight input cells. It is not about the savings formulas or the other pieces used to round out this example. In fact, you don't have to enter the formulas at all to get this technique to work. There are a number of ways to enhance this sheet further if your goal is to build a savings calculator. For instance, a finished sheet might display only the relevant results (column B in the upper portion). In addition, I based the data validation list on the static list in the upper part of the sheet. You might not want to display that list in the actual data entry portion. For the sake of explanation and simplicity, I chose to put everything on the same sheet.
Now, let's get started! To build the sheet, do the following:
- Use the sheet below (which still displays gridlines) as a guide to entering all the labels.
- Next, define the following names: B6 Rate C6 YSave D6 MSave E6 OTSave F6 Deposit G6 Goal
- Enter the following formulas B2 =IFERROR(FV(Rate/12,YSave*12,-MSave,-Deposit),"Error") B3 =IFERROR(FV(Rate,YSave,0,-OTSave),"Error") B4 =IFERROR(PMT(Rate/12,YSave*12,-Deposit,Goal),0)
At this point, your sheet should resemble the one below. You might want to apply the currency format to B2:B4 and D6:G6. I also shaded and outlined a few label cells. (These formatting steps aren't necessary for the technique to work.) Now you're ready to build the list that offers savings options.
- Select A6.
- Click the Data tab.
- In the Data Tools group, click the Data Validation dropdown.
- Choose Data Validation. In Excel 2003, choose Validation from the Data menu.
- In the resulting dialog box, choose List from the Allow dropdown.
- Enter =$A$2:$A$4 in the source control and click OK.
The next step is to add the conditional formatting that highlights the input cells for each savings option. To do so for the first savings option, Future Value (Monthly), do the following:
- Select cells B6:D6 and F6.
- Click the Home tab.
- In the Styles group, click the Conditional Formatting dropdown. In Excel 2003, choose Conditional Formatting from the Format menu.
- Choose New Rule. (Skip this step in Excel 2003.)
- Select the Use A Formula To Determine Which Cells To Format option in the Select A Rule Type section. In Excel 2003, choose Formula Is from the Condition 1 dropdown.
- In the Format Values Where This Formula Is True control, enter the formula: =$A6="Future Value (Monthly)".
- Click Format.
- Choose a color from the Fill tab and click OK.
- Click OK to return to the sheet.
Repeat the steps above to set the conditional formatting for the other two savings options:
- Future Value (One-Time) uses input cells B6:C6 and E6 and the conditional formula, =$A6="Future Value (One-Time)".
- Savings Goal uses input cells B6:C6, F6:G6 and the conditional formula, =$A6="Savings Goal".
If you like, you can turn off the gridlines by clicking the Page Layout tab and unchecking the View option under Gridlines in the Sheet Options group. At this point, your sheet should resemble the first sheet shown above, which highlights the input cells for the third savings option, Savings Goal.
To guide data entry for the first option, Future Value (Monthly), choose that option from the validation list. As you can see below, doing so highlights a different set of input cells. Choose Future Value (One-time) to change the input cells to accommodate that savings option.
Remember, you can further enhance this sheet as an actual savings calculator. You might want to use different (more precise) formulas, or add more savings options and input values. You'd probably want to apply validation rules for the input cells and protect the formula and label cells. In addition, you should probably delete input values when changing options. They won't interfere with the formulas if you leave them, but they might confuse users.
The sheet, as is, illustrates how to use conditional formatting to highlight input cells. It doesn't complete the savings calculator as a useable product.
I'd like to hear how you put this particular tip to use—it's interesting, easy, and has a world of possible uses!