This tutorial acquaints you with a very versatile and, in the long run, great timesaving feature that Excel calls data validation. You use this nifty feature to prevent the users of your spreadsheets from ever getting a chance to enter the wrong type of data entry or a type that's not on your list (when dealing with labels) or within a tolerable range (when dealing with numbers) in critical cells of its worksheets.
Not only can you restrict the types of data entries that you or any other spreadsheet user can make, you can also add input messages to your validated cells that indicate what types of entries are permissible there. As if this weren't enough, you can also tag the validated cells with error alerts that flash an alert symbol along with a custom message indicating the data entry rules for the current cell!

Only the Valid Need Apply

The data validation feature is the Settings tab of the Data Validation dialog box (Data → Validation). The Settings tab is where you specify the criteria under which Excel considers any data entry made in the current cell to be kosher.
The first control on the Settings tab is the Allow drop-down list box. By default, this box is set to Any Value to allow you to do any type of data entry in the current cell. To change all that anything-goes stuff by making only a certain type of entry permissible, select one of the other Allow options:
  • Whole Number restricts data entry to a whole number that is equal to, greater than, less than, or within a range of numbers that you then specify.
  • Decimal restricts data entry to a decimal number that is equal to, greater than, less than, or within a range of values that you then specify.
  • List restricts data entry to one of the items on a list (whose items must already have been entered in a range of cells in the worksheet). When using this Allow option, Excel enables you to create an in-cell drop-down list (with its very own tiny drop-down button) from which you or your users can select the entry.
  • Date restricts data entry to a date that is equal to, before, after, or within a range of dates that you then specify.
  • Time restricts data entry to a time that is equal to, before, after, or within a range of times that you then specify.
  • Text Length restricts data entry to a number of characters that is equal to, greater than, less than, or within a range of numbers that you then specify.
  • Custom restricts data entry to the parameters specified by a Logical formula (one that evaluates to TRUE for allowable entries or FALSE for invalid ones) that's already been entered into a cell of the worksheet to which you then refer.
By default, all data validation settings regard blank cells as valid. If you want to prevent users from leaving a cell blank, while at the same time making their entries conform to your other data validation criteria, clear the Ignore Blank check box on the Settings tab.

Data entries from a list

Enabling users to do data entry by selecting the data from an in-cell drop-down list is one of the most popular uses for the data validation feature. Not only does this type of data validation prevent users from selecting an invalid entry, but it also enables them to make a correct entry without having to do any typing.
To use the data validation List setting to restrict the data entry in this new table to any of the cities on this list, then followed these steps:
  1. Position the cell pointer in the first blank cell to use this data validation setting (B3 in this example).
  2. Choose Data → Validation to open the Data Validation dialog box with the Settings tab selected.
  3. Select List in the Allow drop-down list box.
  4. Put the Insertion point in the Source text box and then drag through the range of cells containing the list of valid entries (J3:J7 in this case).
    Because Excel automatically selects the In-cell Dropdown check box, the program will add a drop-down button that reveals this list of cities whenever the user selects the cell.
    To force users to select one of the cities - making it invalid to leave the cell blank - clear the Ignore Blank check box.
  5. Click OK to close the Data Validation dialog box.
After the Data Validation dialog box closes, Excel adds a drop-down button to the right side of the current cell. To select one of the cities on the list from its menu, click this button and then click the name of the city to enter in cell B3.

Copying data validation settings

After assigning your data validation criteria to a particular cell, you can then copy these criteria to all the cells in the vicinity that need to use the same restrictions. To do this, you follow these steps:
  1. Copy the cell with the data validation to the Clipboard.
    (Choose Edit → Copy or press Ctrl+C.)
  2. Select the cell range into which to copy the data validation criteria.
  3. Choose Edit → Paste Special or to open the Paste Special dialog box.
  4. Click the Validation option button and click OK.
For example, to copy the data validation criteria that cause the cell to display the cities drop-down list box from the original cell B3 down the column to the range B4:B15, I copied the contents of B3 to the Clipboard and then selected the range B4:B15. Next, I opened the Paste Special dialog box, clicked the Validation option button, and clicked OK. As a result, all the cells from B3 down B15 use the same data validation criteria. When you put the cell pointer in any of those cells, a drop-down button appears on the right side of the cell. When clicked, this button reveals the city drop-down list.

Finding cells using data validation

Most data validation settings that you assign to cells are invisible. (Okay, the List setting may be the exception here, but even the List setting is invisible when the cell pointer isn't in the cell.) To help you locate the cells in the active worksheet that use some sort of data validation criteria, you can use the Go To feature to find (and select) them:
  1. Press F5 or Ctrl+G to open the Go To dialog box.
  2. Click the Special button to open the Go To Special dialog box.
  3. Click the Data Validation option button and click OK.
    Because the All option button underneath the Data Validation option is automatically selected by default, Excel selects all the cells and cell ranges in the worksheet that have some type of data validation criteria assigned to them.
  4. Use the Enter or Tab key to move the cell pointer around the selection from cell to cell and from cell range to cell range.
  5. To find out what type of data validation a particular cell in the selected range uses, choose Data → Validation and check out its criteria on the Settings tab.
In a spreadsheet that uses many different types of data validation settings, you can find all the cells that use the same criteria. To do so, position the cell pointer in a cell that you know uses the criteria you want to find, open the Go To Special dialog box, click both the Data Validation and Same option buttons, and click OK.

Problem with Data Validation

The only problem with data validation is that because it's invisible to spreadsheet users, many times the only way they know they've hit a cell that uses it is when they try to make what the cell now considers an invalid entry. When anybody tries to complete an entry with text or a value that is verboten in the cell, Excel beeps at them and then displays an alert dialog box with the following unhelpful message:
The value you entered is not valid.
A user has restricted the value that can be entered into this cell.
The user can then click the Retry button and try to put something in the cell that Excel does find acceptable, or he or she can click the Cancel button and forget the whole thing.
To give users a fighting chance at rectifying their error by making an acceptable entry in the cell, I heavily suggest that you assign an input message to the cell explaining to the poor user what kind of entry is now valid. To do this, open the Data Validation dialog box (Data → Validation) and then click the Input Message tab.
There, you can enter a title for the message in the Title text box (this appears in bold at the top of the text box displaying the input message) as well as the explanatory message text you want displayed in the Input Message list box.

Warnings to Make Them Wary

If you're in danger of this happening, it's time to bring out the heavy artillery by adding an error alert message as well. (You can always forgo the input message and just add the error alert message letting users know exactly what kind of behavior will and won't be tolerated in that cell.)
To add an error alert message that appears whenever the user tries to make an entry that violates the cell's data validation criteria, open the Data Validation dialog box and select the Error Alert tab. The settings on this tab enable you to
  • Select a type of indicator to display in the alert dialog box (Stop, Warning, or Information) from the Styles drop-down list box.
  • Enter a title for the warning in the Title text box (which appears in bold in the title bar of the custom alert dialog box).
  • Type the text of the message in the Error Message list box (which appears in the body of the alert dialog box).
Don't select the Warning or Information type of error alert message unless you want it to be possible for your users to sidestep the validation settings and enter invalid data in the cell. (The Warning type allows this with its Yes button and the Information type with its OK button.) Only the default Stop type prevents users from going ahead and making invalid entries in the cell.
After adding an error alert message, whenever the user tries to enter something outside the bounds set by the cell's data validation settings, Excels beeps at them and then displays your custom error alert dialog box shows you how the custom error alert dialog box appears when you try to enter an invalid number (in this case, any whole number less than 5 or greater than 15 or any decimal number at all). When this Stop type of error alert appears, your users have two choices:
  • Click Retry to have another go at entering the data (in hopes that the user can get it right this time).
  • Click Cancel to clear the aberrant entry from the cell (and thus forget about the whole thing).

Post a Comment

Blogger Tips and TricksLatest Tips And TricksBlogger Tricks


Cloud Power For You

Website Hosting At Low Price


Empire Views