Section 22.2. Data Validation | Excel 2007[c] The Missing Manual

22.2. Data Validation

With data validation, you can easily prevent people from entering the wrong data in a cell (or at least warn them when they do). Data validation also helps make Excel a little kinder and gentler for a novice by letting you create custom error messages, and add helpful pop-up tips. You need a little time to set up data validation, so usually you'll use it only on your worksheet's most important cells (Figure 22-1).

To apply data validation, move to the appropriate cell, and then choose Data Data Tools Data Validation. A Data Validation dialog box appears with three tabs: Settings, Input Message, and Error Alert. You fill in the settings in these three tabs, and then click OK to put the rule into action. The following sections explain each tab of the Data Validation dialog box.

Figure 22-1. In this worksheet, which calculates mortgage payments, it makes sense to use data validation on the cells you expect people to changelike the loan amount (cell B3) and the interest rate (cell B4). You can then use worksheet protection, as discussed later in this chapter, to prevent the spreadsheet user from modifying other cells altogether.

Tip: You can apply validation to a number of cells at once. Just select all the cells before you choose Data

Data Tools

Data Validation.

22.2.1. Settings

Use the Settings tab of the Data Validation dialog box (shown in Figure 22-2) to specify what values are allowed in the cell. You have two methods at your disposal. First of all, in the Allow list box, you can set the type of value that's allowed. The simplest possible types include whole number, decimal, date, time, and text. (Two other types of valuescustom and listsare covered later in the chapter.) For example, if you select "Whole number" from the Allow box, and you try to input a value of 4.3 into the cell, Excel shows an error message and prevents your input.

Once you've chosen the data type, you still need to set the data range . Do so by choosing a comparison from the Data list box, and then specifying the values that you want to use for your comparison. For example, if you want to restrict input to a whole number from 0 to 5, choose "between" and set a minimum value of 0 and a maximum value of 5. Other comparisons you can use include less than, greater than, greater than or equal to, and so on. You set the data range for all data types, including dates, times, and text (in which case you set limits on how many characters can go in the text).

Figure 22-2. The options selected here force the person using the workbook to enter a whole number from 1 to 100 (or leave the cell blank). In addition, the "Ignore blank" checkbox in the top-right corner is turned on. This setting tells Excel to allow empty values, so that it doesn't try to validate the cell if it doesn't contain any data.

It's easy to remove your validation rule later on. Just select the appropriate cells, choose Data Data Tools Data Validation, and then click Clear All.

Note: Data validation begins policing a cell only after it's been applied. If you apply a validation rule and the cell already contains invalid information, Excel doesn't complain.

22.2.2. Input Message

Once you've added data type rules, it's a nice touch to give the person using your workbook (or template) fair warning. You can do so by adding a pop-up message that appears as soon as somebody moves to the corresponding cell.

Note: Use the Settings tab to prevent bad values. Use the Input Message tab to add some helpful information. Sometimes, you'll want to use just one of these two tabs, and other times you'll need them both. The Input Message tab really shines when you're sharing a workbook that someone's likely to copy and reuse in the future, or when you're building a template. And it's particularly handy for giving Excel newbies some guidance.

An input message has two components : a title (displayed in bold) and a more detailed message. In the Input Message tab of the Data Validation dialog box (shown in Figure 22-3), you can enter both of these pieces of informationjust fill in the Title and "Input message" boxes. (Also, while you're there, make sure the "Show Input message when cell is selected" checkbox is turned on. Otherwise, Excel doesn't show your message at all.)

Input messages should contain more than a description of your data validation settings. Ideally, your input message explains a little bit about the data that the cell is looking for. In fact, depending on who'll be using your workbook, you can include information about the type and format of information, what restrictions they need to be aware of, and even how they should enter the information.

Figure 22-3. Here's how to create a helpful and descriptive message to tell whoever's using your workbook what the term "mortgage principal" really means. When you define an input message, you choose a title for the pop-up box (the title appears in bold) and a descriptive message. Figure 22-4 shows the message in action.

Figure 22-4. When the person using the workbook moves to a cell that has an input message, a yellow tip box appears displaying the message. Spreadsheet users can drag the box to another location on the worksheet if it's obscuring some important information.

For example, for an invoice date cell, you might want a message that says, "This is the date your invoice was submitted to your manager. When you enter a date, use the format day-month-year (as in 29-1-2008 for January 29, 2008), and make sure you don't enter a date earlier than 1-1-2007." For a payment code, you might include a message like "This is the code from the top-right corner of your pay stub. All pay codes start with the letters AZ."

22.2.3. Error Alert

Despite your best attempts, someone, somewhere will probably still type the wrong information in a cell, defying your input messages. In this case, you need to respond by politely explaining the problem. Unfortunately, Excel's standard error messagewhich it displays when someone breaks the data validation rules set down in the Settings tableaves a lot to be desired. It's unnecessarily harsh and confusingly vague. Use a better approach, with the Error Alert tab to define your own error message.

To do so, head to the Data Validation dialog box's Error Alert tab (Figure 22-5). Begin by turning on the "Show error alert after invalid data is entered" checkbox. This setting tells Excel to monitor the cell for invalid information. If anyone enters the wrong data, a dialog box appears with an error message. The buttons that Excel uses in that dialog box depend on the style of error message you choose.

Figure 22-5. Helpful error messages, like this one, tell spreadsheet users what they need to do to fix their mistakes.

To choose an error message style, select an option from the Style list. Excel novices will appreciate it if you use the friendly Information icon, as shown in Figure 22-6, instead of the red X alert icon. But keep in mind that different icons have different effects on whether Excel tolerates invalid input. Here are your choices:

Stop . Excel displays the error message along with a Retry and Cancel button. The person using the workbook must click Cancel to reverse the change (which returns the cell to its last value) or Retry to put the cell back into edit mode and try to fix the problem. The Stop option is the only Style choice that completely prevents the person using the workbook from entering invalid data.
Warning . In this case, the error message includes Yes and No buttons that let the person entering the data decide whether to go ahead with the input. Clicking Yes makes Excel accept the data entered into the cell, even though it's broken the validation rules.
Information . The error message comes with Cancel and OK buttons. Clicking OK enters the new (invalid) data in the cell; Cancel leaves the cell unchanged.

Figure 22-6. Because this example uses the Information style, the error message box shows a friendlier iconthe "i" inside a circle.

After you've set the icon, specify a title, and then type a descriptive message indicating the error message you want. Remember, you won't know exactly what went wrong, so it's best to reiterate the data type rules you've applied, or use a phrase that begins with something like "You've probably " to identify a common problem.

Note: If you set a data validation rule but turn off the "Show error alert after invalid data is entered" checkbox, the person entering data won't see any error messages and won't have any idea if she's entered the wrong type of information. You would switch off the "Show error alert after invalid data is entered" checkbox only if you ever want to temporarily disable your validation rules but not remove them, so that you can apply them again later.

GEM IN THE ROUGH
Quickly Spotting Every Error

Auditing circles are an often-overlooked Excel troubleshooting tool. When you choose the Data Data Tools Data Validation Circle Invalid Data command, Excel draws a red circle around every cell thats broken a validation rule on the entire worksheet (Figure 22-7). You can click Data Data Tools Data Validation Clear Validation Circles to remove this information.

You don't need the auditing circles when you're using strict data type validation (in other words, when you're rejecting all errors with the Alert message type), because you never end up with invalid data. However, it comes in very handy when you're allowing errors and only showing a Warning or Information message. You might choose to apply these invalid data circles if you want to let one person fill out a workbook, but let another person review it. In this scenario, the auditing circles can help the second person in the chain automatically find the mistakes that need to be corrected.

Figure 22-7. Validation circles, automatically added by Excel when you use the Data Data Tools Data Validation Circle Invalid Data command, help you spot the troublemaking cells.

22.2.4. Data Validation with Formulas and Cell References

You can create more advanced cell restrictions by using formulas and cell references. Imagine you want people to enter the current date on a form they're filling out. You want to make sure that the date is no earlier than the current date. In this scenario, you can't use a literal date in your data validation rule because the date limit needs to change depending on when someone makes the edit.

However, you can use the TODAY() function to help you out. To do so, select Data Data Tools Data Validation. In the Settings tab, select the Date data type, and require values to be greater than or equal to = TODAY() . In other words, when someone types information into that cell, Excel runs the TODAY() function and compares the result against the cell value. This example shows one way you can embed a function within a data validation rule.

You could also use a formula that contains a cell reference. You might want to make sure that an expense cell always contains a value that's equal to or less than a corresponding budget cell. In this case, you can't put the budget limit directly into the data validation rule, because you don't know what the budget will be until the person using the workbook fills it in. You need to create a data validation rule that uses a formula that references the value of the budget cell. You could specify that the value in the expense cell must be less than or equal to the formula =C3, assuming the budget value's in cell C3.

For an even more powerful approach, from the Allow list box, choose the Custom data type. When you use Custom, you must supply a conditional formula in the formula box. A conditional formula is simply one that responds with a value of either true or false . (Chapter 13 tells you all about how to create conditional functions.) If the result is true, Excel allows the cell entry. If it's false, Excel displays your custom error dialog box (assuming the "Show error alert after invalid data is entered" checkbox in the Error Alert tab is switched on). The neat thing about conditional formulas is that you can combine as many unrelated conditions as you need, using the conditional functions AND() and OR().

Conditional validation rules are also useful if you need to compare the current cell against the value returned by a function. The following fairly intimidating-looking formula prevents the person using the workbook from entering a date that falls on a weekend . B3 is the cell containing the validation rule, and the WEEKDAY() functions verify that B3 doesn't represent a Saturday (a value of 7) or a Sunday (a value of 1). This formula forces B3 to adhere to both these restrictions by using the AND() function:

 =AND(WEEKDAY(B3)<>1, WEEKDAY(B3)<>7)

Another time you might use a conditional formula in a data validation rule is when you need to monitor a group of cells and make sure their total value doesn't exceed a total that you specify. If you don't want the series of expense items in cells B2 to B7 to total more than $5,000, you would select all these cells, choose Data Data Tools Data Validation, choose the Custom data type, and then supply the following formula:

 =(SUM($B:$B)<=5000)

Figure 22-8 shows an example that introduces an improvement on this formula. Instead of using an exact budget limit in the data validation rule, another cell supplies the budget limit.

Note: You may notice that the SUM() formula uses absolute cell references (for example, $B$2) rather than ordinary references (like B2). You can apply the exact same SUM() formula to multiple cells at once. If you don't take this step, Excel modifies the cell references in each subsequent cell, which isn't what you want. See Section 8.3.1 for a refresher on the difference between absolute and ordinary cell references.

Figure 22-8. Here, a custom data validation rule polices the total value of a group of cells. Figure 22-9 shows the result.

Figure 22-9. If the value of cells B2 through B6 is greater than the Maximum Budget cell value (B10), the rule rejects the entry. Of course, you can combine this example with the techniques shown later in this chapter to lock the Maximum Budget cell, preventing other people from changing how much money they're allowed to spend .

You may also use the Custom data type and write a conditional formula to prevent duplicates in a range of cells. For example, the next formula checks to see that there's no other instance of B3 in the range of cells from B2 to B7. This validation rule goes into cell B3.

 =COUNTIF(B2:B7,B3)<=1

This formula isn't quite as convenient as the summing formula because the SUM() formula applies to all the cells in a range. The COUNTIF() formula needs to be tweaked for each cell. The formula shown above is what you'd use to validate the contents of cell B3, but in order to perform the same check for duplicates in the other cells in the specified range (B2 to B7), you'd need to modify the formula (replacing the reference B3 with whatever cell you wanted to check).

22.2.5. Data Validation with Lists

The only other data type choice you have (when filling out the Allow field in the Settings tab, as shown in Figure 22-2) is the List option. The List choice is interesting because it doesn't just restrict invalid values, it also lets you add a handy drop-down list box that appears when anyone using your spreadsheet moves into that cell. The person who's entering data can use the list to quickly insert an allowed value, without needing to type it in. You can also type values in by hand, but Excel assumes that if the value you enter doesn't match one of the entries in the list, your entry's invalid (and it shows an error message depending on your Error Alert settings).

To create this list, choose the Data Validation dialog box's Settings tab, and then choose List from the Allow text box. You have two choices for supplying a list in the Source box: You can type in a list of comma-separated values (like 1,2,3 or blue,black,red ), or you can use a cell range that contains a list. If you want the person using the list to be able to choose the entry from a drop-down list of options in the cell (which is a slick and convenient touch), make sure you keep the "In-cell dropdown" checkbox turned on. Figure 22-10 shows an example that modifies the lookup worksheet used in Chapter 12. You can create an invoice by choosing products from a drop-down list.

Figure 22-10. This worksheet uses list validation. This approach's advantage is that people using the spreadsheet don't need to remember the name or ID of each product. Instead, there's always a complete list of possible choices available at their fingertips. The only disadvantage is that the list you use must be on the same worksheet. Here, that means the entire product catalog is off to the side of the current worksheet, so that it's available for the drop-down list and lookup functions. Figure 22-11 shows the data validation settings that make it work.

Figure 22-11. The list validation settings in this dialog box tell Excel to generate a list of product choices drawn from the range of cells indicated in the Source box. See Figure 22-10 for what this looks like on the spreadsheet.

Note: List validation can't point to a range that's stored in another worksheet. However, this limitation doesn't apply if you use a named range (Section 13.2). Check out the Invoice.xlsx file (posted along with the other samples for this chapter at www.missingmanuals.com) to see an example that uses list validation with a named range.