This is one of those really neat and versatile
features with a really vague name. Validation can be used to ensure
that only certain types of data are entered into a cell range, such as dates
only, text, numbers between x and y, etc. Validation can also be used to display
user prompts, informing the user of the information required in a cell. You
can also create your own error messages if the users' input does not match the
criteria for the cell (that's always the fun part!).
To add this feature to a cell:
- Select the cell
you wish to add Data Validation to.
- Display the Data Ribbon and click on the
Data Validation button
. (If you click on the bottom of the Data Validation button, choose Data Validation
from the list.) There are three tabs within this window.
- Specify the allowable data within the Settings tab. Theses settings
allow you to specify the type of information that can be entered into the
- Click on the drop list button in the Allow field to view your
choices. The following is a summary of those choices.
- Any Value places no restrictions on the valid entries. Use this
setting if you want to display an input message without checking for valid
- Whole Number specifies that entries must be integers (no decimals).
- Decimal specifies that entries must be numbers or fractions.
- List lets you specify a list of the valid entries. Refer to the
heading Limit Input to a List below for additional tips on using
- Date limits the entry to a date. You can also specify valid date
criteria, such as greater than a specific date.
- Time limits the entry to a time. You can also specify valid time
criteria, such as greater than a specific time.
- Text Length specifies that entries must be in time format.
- Custom lets you enter a formula, use an expression, or refer to
a calculation in another cell to determine valid entries.
- Use the drop list button in the Data field to select criteria,
such as greater than, less than, etc. Once selected, complete the criteria.
Example: Allow a whole number between 1000 and 2000.
- Specify a message prompt to appear using options within the Input Message
tab. When the user selects the associated cell(s) the message will appear.
It's a good idea to advise the user of any restrictions placed on the cell,
i.e. a message might read 'Enter the 6 character invoice number'.
- Enter an appropriate Title for the message. The title appears
in the title bar of the message window.
- Enter a message for the user in the Input message box. This field
would normally provide instruction for the user as to the type of information
required and/or format to use.
- Specify an error message within the Error Alert tab. This message
will only appear if the criteria you specified in the Settings tab are not
- Choose the Style of error: choosing Stop will prevent the user
from entering anything other than a valid response. Alternatively you
can choose Warning or Information.
- Enter an appropriate Title for the error message. The title appears
in the title bar of the message window.
- Enter a message for the user in the Error message box. A standard
message might include Please click on Retry then read the instructions.
Limit Input To A List
If desired, you can limit the user's selection in a cell to a specific list,
such as a list of department names, inventory items, etc.
- Enter the list of valid items down a column. (You can use any column
in the current worksheet but make sure it is separated from your database
by at least one blank column so that it does not get associated as part of
the database - refer to article on Data
Lists for more information on database layout.)
- Specify validation rules, (as indicated above), selecting List
in the Settings tab. In the source field enter an absolute reference
to the range in the following format =$Z$1:$Z$100 Alternatively,
you can drag your mouse over the range and Excel will add the reference for
you. - or - you can name the
source field and refer to the range name. It looks more 'English' than typing
dollar signs and colons.
- Specify the Input Message and Error Alert, as described above.
Circle Invalid Data
If you applied settings to data already present (typed), you can check for
invalid data (data that does not meet your settings) using this feature. This
is also useful if you did not choose the Stop action within the Error Alert
tab and wish to view data that does not meet your criteria.
- Select any cell within the range that you applied
- Click on the bottom of the Data Validation button within the Data Ribbon.
- Choose Circle Invalid Data. This process may take several seconds.
Data that does not meet the criteria specified in the settings tab will appear
with a red circle.
To remove the circles, repeat this process selecting
Clear Validation Circles in step 3.