Chapter 35: Validating Data

Overview

• I’m entering scores from professional basketball games into Excel. I know that a team scores from 50 through 200 points per game. I once entered 1000 points instead of 100 points, which messed up my analysis. Is there a way to have Excel prevent me from making this type of error?

• I’m entering the date and amount of my business expenses for a new year. Early in the year, I often enter the previous year in the Date field by mistake. Is there a way I can set up Excel to prevent me from making this type of error?

• I’m entering a long list of numbers. Can I have Excel warn me if I enter a nonnumeric value?

• My assistant needs to enter state abbreviations when she enters dozens and dozens of sales transactions. Can we set up a list of state abbreviations to minimize the chance that she’ll enter an incorrect abbreviation?

Our work often involves mind-numbing data entry. When you’re entering a lot of information into Microsoft Office Excel 2007, it’s easy to make an error. The data validation feature of Excel can greatly lessen the chances that you’ll commit a costly error. To set up data validation, you begin by selecting the cell range that you want to apply data validation to. Choose Data Validation from the Data tab and then specify the criteria (as you’ll see in this chapter’s examples) that Excel uses to flag any invalid data that’s entered.

• I’m entering scores from professional basketball games into Excel. I know that a team scores from 50 through 200 points per game. I once entered 1000 points instead of 100 points, which messed up my analysis. Is there a way to have Excel prevent me from making this type of error?

• Let’s suppose that you’re going to enter into cells A2:A11 the number of points scored by the home team, and in cells B2:B11, you’ll enter the number of points scored by the visiting team. (You’ll find the work I did to solve this problem in the file Nbadvl.xlsx.) You want to ensure that each value entered in the range A2:B11 is a whole number from 50 through 200.

• Begin by selecting the range A2:B11, and then choose Data Validation from the Data tab. Then select the Settings tab. Select Whole Number from the Allow list, and then fill in the Data Validation dialog box as shown in Figure 35-1. In response to invalid data, the default response (called an Error Alert) is a message stating “The value entered is not valid. A user has restricted values that can be entered into the cell.”

  
  Figure 35-1: Use the Settings tab in the Data Validation dialog box to set up data-validation criteria.

• You can use the Error Alert tab in the Data Validation dialog box (see Figure 35-2) to change the nature of the error alert, including the icon, the title for the message box, and the text of the message itself. On the Input Message tab, you can create a prompt that informs a user about the type of data that can be safely entered. The message is displayed as a comment in the selected cell. For example, in cell E5, I created the prompt shown in Figure 35-3. To do this, I clicked the Input Message tab shown in Figure 35-2 and typed the input message shown in Figure 35-3.

  
  Figure 35-2: Error Alert tab options in the Data Validation dialog box

  
  Figure 35-3: Add a data-validation input prompt so that users know what data they can enter.

• I’m entering the date and amount of my business expenses for a new year. Early in the year, I often enter the previous year in the date field by mistake. Is there a way I can set up Excel to prevent me from making this type of error?

• Suppose you’re entering the year in the cell range A2:A20. (See the file Datedv.xlsx.) Simply select the A2:A20 range, and then choose Data Validation from the Data tab. Fill in the Settings tab of the Data Validation dialog box as shown in Figure 35-4.

  
  Figure 35-4: Use settings such as these to ensure the validity of dates you enter.

• If you enter a date in this range that occurs earlier than January 1, 2005, you’ll be warned about the error. For example, entering 1/15/2004 in cell A3 will bring up the error alert shown earlier in Figure 35-2.

• I’m entering a long list of numbers. Can I have Excel warn me if I enter a nonnumeric value?

• To unleash the power of data validation, you need to use the Custom setting. When you select Custom in the Allow list on the Settings tab of the Data Validation dialog box (see Figure 35-1), you use a formula to define valid data. A formula you enter for data validation works the same as a formula used for conditional formatting, which is described in Chapter 22, “Conditional Formatting.” You enter a formula that is true if and only if the content of the first cell in the selected range is valid. When you click OK in the Data Validation dialog box, the formula is copied to the remaining cells in the range. When you enter a value in a cell in the selected range, Excel displays an error alert if the formula you entered returns False for that value.

• To illustrate the use of the Custom setting, let’s suppose we want to ensure that each entry in the cell range B2:B20 is a number. (See the file Numberdv.xlsx.) The key to solving this problem is using the Excel ISNUMBER function. The ISNUMBER function returns True if the function refers to a cell that contains numeric data. The function returns False if the function refers to a cell that contains a nonnumeric value.

• After selecting the cell range B2:B20 and placing the cursor in B2, display the Data tab of the Ribbon, click Data Validation in the Data Tools group, and then fill in the Settings tab of the Data Validation dialog box as shown in Figure 35-5.

  
  Figure 35-5: Use the ISNUMBER function to ensure that the data in a range is numeric.

• After clicking OK, we’ll receive an error prompt if we try to enter any nonnumeric value in B2:B20. For example, if we type John in cell B3, we receive an error prompt.

• If you click Data Validation while in cell B3, the formula shown in Figure 35-5 is displayed as =ISNUMBER(B3). This demonstrates that the formula entered in cell B2 is copied in the correct fashion. Entering John in cell B3 causes =ISNUMBER(B3) to return False, so we receive the error alert.

• My assistant needs to enter state abbreviations when she enters dozens and dozens of sales transactions. Can we set up a list of state abbreviations to minimize the chance that she’ll enter an incorrect abbreviation?

• The key to this data validation problem is to use the List validation criteria. Begin by entering a list of state abbreviations. See the file Statedv.xlsx. In this example, I’ve used the range I6:I55 and named the range abbrev. Next, select the range in which you’ll enter state abbreviations. (The example uses D5:D156.) After clicking Data Validation from the Data tab, fill in the Data Validation dialog box as shown in Figure 35-6.

  
  Figure 35-6: The Data Validation dialog box can be used to define a list of valid values.

• Now, whenever you select a cell in the range D5:D156, clicking the drop-down arrow causes a list to appear, as shown in Figure 35-7. The list contains the state abbreviations. Only abbreviations that appear on the list are valid values in this range.

  
  Figure 35-7: Drop-down list of state abbreviations

• If you do not use the drop-down list and instead type in a state abbreviation, should you enter an incorrect abbreviation (such as ALK for Alaska), you’ll receive an error message.