O'Reilly logo

Excel 2010: The Missing Manual by Matthew MacDonald

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 24-1).

In this worksheet, which calculates mortgage payments, it makes sense to use data validation on the cells you expect people to change—like 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 another person using the spreadsheet from modifying other cells altogether.

Figure 24-1. In this worksheet, which calculates mortgage payments, it makes sense to use data validation on the cells you expect people to change—like 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 another person using the spreadsheet from modifying other cells altogether.

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.

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.

Settings

Use the Settings tab of the Data Validation dialog box (shown in Figure 24-2) to specify ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required