[FAQ's: OD] How to do Data Validation in MS Excel
Data Validation
Note: This has been tested in Excel 2003
Excel’s data validation feature is similar in many respects to the conditional formatting feature. This feature enables you to set up certain rules that dictate what you can enter in a cell.
For example
You may want to limit data entry to whole numbers between 1 and 13. If the user makes an invalid entry, you can display a custom message as shown in the picture below.
Specifying validation criteria
To specify the type of data allowable in a cell or range, follow these steps:
a) Select a cell or a range. Say A1:D10
b) Choose Data=>Validation. Excel displays the Data Validation Dialog Box.
c) Click on the settings Tab
d) Choose an option from the drop down box labeled ‘Allow’. Let’s say “Whole number”
e) Specify the conditions by selecting the drop down box labeled Data. As shown below
f) (Optional) Click on the input Message Box Tab and specify which message to display when a user selects the cell. You can also use this step to tell the user what type of data is expected. As shown below
g) (optional) Click the Error Alert tab and specify which Error message to display when a user makes an invalid entry. As shown below
h) Click OK to see the result as shown in picture below
Types of Validation Criteria
The settings tab of the data validation dialog box enables you to specify a wide variety of data validation criteria. The following options are available in the Allow Drop-Down box
a) Any Value: Selecting this option removes any existing data validation.
b) Whole Number: The user enters only whole numbers
c) Decimal: The user must enter a decimal
d) List: The user must choose from a list of entries you provide (covered later in this thread)
e) Date: The user must enter a date
f) Time: The user must enter a time
g) Text Length: The user can enter text up to a certain length
h) Custom: A logical formula determines the validity of the user’s entry
The settings tab of the data validation dialog box contains two other check boxes:
i) Ignore Blank: If checked, blank entries are allowed
j) Apply these changes to 'All' other cells with the same setting: If checked, the changes you make apply to all other cells that contain the original data validation criteria.
IMPORTANT NOTE
Even with Data Validation in effect, the user could enter invalid data. If the Style setting in the Error Alert Tab of the Data Validation dialog Box is set to anything except Stop, invalid data can be entered. Also remember that Data validation does not apply to the calculated results of formulas. In other words, if the cell contains a formula, applying conditional formatting to that cell will have no effect.
Creating a drop down list
Perhaps one of the most common uses of data validation is to create a drop down list of items {at least for me :-) }. The figure below shows an example that uses the month names in A1:A12 as list of source.
First, enter the list items into a single-row or single-column range. (These are the items that will appear in the drop-down list). Then select the cell that will contain the drop down list and access the data validation dialog box. In the settings tab, select the list option and specify the range that contains the list using the source control. Also, make sure that the In-Cell Dropdown check box is checked. See picture below.
After performing these steps, the cell displays a drop-down arrow when it is activated. Click the arrow and choose an item from the list that appears. (See picture 8)
a) Accepting text only
To force a range to accept only text (no vales), use the following data validation formula:
=ISTEXT(A1)
This formula assumes that the active cell in the selected range is cell A1.
b) Accepting a larger value than the previous cell
The following data validation formula enables the user to enter a value only if it’s greater than the value in the cell directly above it:
=A2>A1
This formula assumes that A2 is the active cell in the selected range. Note that you can’t use this formula for a cell in Row1
c) Accepting non duplicate entries only
The following data validation formula does not permit the user to make a duplicate entry in the range A1:C20
=COUNTIF($A$1:$C$20,A1)=1
This formula assumes that A1 is the active cell in the selected range. Note that the first argument for COUNTIF is an absolute reference. The second argument is a relative reference, and it adjusts for each cell in the validation range.
d) Accepting text that begins with “k”
The following data validation formula demonstrate how to check for a specific character(s). In this case, the formula ensures that the user’s entry is a text string that begins with the text “k” (either uppercase or lowercase) see picture below.
=LEFT(A1)= “k”
This formula assumes that the active cell in the selected range is Cell A1
Re: [FAQ's: OD] How to do Data Validation in MS Excel
Thanks Koolsid,
One more thing I noticed was that the list can also be typed directly in Source. Each value seperated by commas. Similarly the list can be generated for one cell and dragged to other cells or copy ->paste special->validation
