subject: Microsoft Excel Data Validation - a Complete Guide to Validating Data in MS Excel [print this page] Microsoft Excel Data Validation - a Complete Guide to Validating Data in MS Excel
Imagine that you have a spreadsheet asking a client to type in their estimated income intoa cell. You know that this should be in the range from 0 to 1000, but there is nothing to stop the client typing in a negative number, a date or indeed a piece of text. Until, that is, you apply data validation.
Beginning Validation
In Excel 2003 you can do this by choosing Data from the menu and then choosing Validation; in Excel 2007 and Excel 2010 you do this by clicking on the Data tab of the ribbon, and then choosing Data Validation followed by Data Validation (again). Whichever method you choose, you will then see a dialog box with three tabs: Settings, Input Message and Error Alert. Of these, you can ignore the middle Input Message tab (this is used to set a message to appear when you click on a cell, and is better handled with cell comments).
Applying Validation
The first thing to do is to decide what sorf of input is allowed into the cell. For our example you should choose Decimal from the Allow drop-down list, since this will allow you to input any number (and not just an integer). You could then specify that you want to allow any number between 0 and 1000.
Displaying an Error Message
Having chosen to allow the right sort of number (in our case, a decimal number between 0 and 1000), it's time to punish a user who transgresses. To do this, click on the Error Alert tab of the dialog box, and type in a title for the error message to display and the text of it. Typical values might be:
Title - "Invalid income"
Error message - "You must type in a number between 0 and 1000"
Note that you also have the chance to choose the Style for the validation message, which can be Stop, Warning or Information. This not only controls which icon will appear next to the message, but also the extent to which invalid values will be allowed. The safest thing to choose is the default Stop style, which means that it will be impossible to type in any value which doesn't lie in the prescribed range.
Displaying a Drop-List
One special use of validation settings is to restrict a user's choices to those in a drop list. For example, suppose that you want to allow a user to choose a month from a drop list. There are clearly 12 possible values, ranging from January to December. To make this work:
- create a column of values in a spreadsheet (12 cells), containing the months from January to December
- select these 12 cells, and give them a range name (let's say you choose Montths)
- go into the data validation dialog box as described above, and choose to allow values in a List
- in the next part of the dialog box Excel will ask you to specify the Source.Press F3, and choose the Months range name from the dialog box which appears.
You will now see a drop-list when you click in the cell.
Copying Validation Settings
To copy validation settings from one cell to another, select the first cell and copy it, then right-click on the target cell or cells and choose Paste Special.... You can then choose to paste the validation settings only from the dialog box which appears.
Highlighting Cells to Which You've Applied Validation
Not sure which cells you've applied validaiton to? Press F5 to bring up the GO TO... dialog box and click on the Special... button in the bottom left corner. You can now choose the appropriate radio button to highlight all cells to which you've applied validation.
