Data Validation
This command is used to ensure that new entries to a list or database meet certain criteria.
You can specify the type of data you will allow (e.g. whole numbers, dates, times, text,
number range). You can also have a list of acceptable values and create a drop down list).
In order to set this up do the following:-
1. Select the range of cells where you want the criteria to apply
2. Select the Data ribbon and choose the Data Validation icon. The following dialog
box will appear:
3. In the Settings tab indicate what type of data and what range of values you wish to
allow i.e. if you wish to specify a date range choose Date
4. The Input Message tab allows you to create a prompt that tells the user what kind of
data is allowed (i.e. help)
5. The Error Alert tab allows you to specify the message that appears if an entry is
invalid
Specifying Data Type and Acceptable Values
You can choose from the following types:-
Any value, whole number, decimal, list, date, time, text length, custom
Once you have chosen a data type Excel modifies the dialog box so that you can enter
additional information about the chosen type. If for example you choose Whole Number
you will be asked to specify and minimum and maximum value.
Supplying a List of Acceptable Values
To set up a list of acceptable values choose List from the Allow drop-down box. Specify the
worksheet range where your list appears.
Provided by Abacus Computer Training, Rickmansworth
T: 01923 710552 M: 07885 232 030 E: Lesley.Keddy@virgin.net
PDF Creator - PDF4Free v2.0 http://www.pdf4free.com
Using a Formula to Validate Input
To use a formula for Validation, choose Custom from the Allow drop-down box and specify
a Formula on the formula line that appears. e.g. to ensure that cell A1’s value is greater
than cell B1’s, select A1 then choose the Validation command, choose Custom from the
Allow drop-down box and then specify = A1>B1 on the formula line.
In the following example we are going to set up data validation to ensure that a date typed
into column B can only be today’s date or later – e.g. not a past date.
Invoice Date Number Company Amount VAT Total
11560 22-Sep-94 TC/001 Ability £3,472.00 £607.60 £4,079.60
11561 25-Sep-94 TC/004 Lectern Systems £782.00 £136.85 £918.85
11562 25-Sep-94 TC/024 Adept Computer Technology £690.00 £120.75 £810.75
11563 27-Sep-94 TC/004 Lectern Systems £499.00 £87.33 £586.33
11564 29-Sep-94 TC/001 Ability £2,890.00 £505.75 £3,395.75
11565 30-Sep-94 TC/004 Lectern Systems £1,809.00 £316.58 £2,125.58
11566 30-Sep-94 TC/010 Northwind Systems £3,635.00 £636.13 £4,271.13
11567 30-Sep-94 TC/024 Adept Computer Technology £804.00 £140.70 £944.70
11568 30-Sep-94 TC/001 Ability £1,575.00 £275.63 £1,850.63
11569 03-Oct-94 TC/028 Inova Business Services £638.00 £111.65 £749.65
11570 03-Oct-94 TC/004 Lectern Systems £597.00 £104.48 £701.48
11571 03-Oct-94 TC/010 Northwind Systems £357.00 £62.48 £419.48
11572 03-Oct-94 TC/001 Ability £2,556.00 £447.30 £3,003.30
11573 04-Oct-94 TC/107 Courseware Company £2,869.00 £502.08 £3,371.08
11574 05-Oct-94 TC/004 Lectern Systems £254.00 £44.45 £298.45
11575 05-Oct-94 TC/010 Northwind Systems £329.00 £57.58 £386.58
11576 05-Oct-94 TC/001 Ability £3,002.00 £525.35 £3,527.35
1. To do this highlight the whole of column B
2. Select the Data ribbon and choose Data Validation
3. From the dialog box that appears enter the following:
In the drop down menu under Allow choose Date
Click on the drop-down arrow alongside Data and choose greater than or equal to
In the Start Date box type =TODAY() (this formula means the date has to be today’s
date or greater)
Provided by Abacus Computer Training, Rickmansworth
T: 01923 710552 M: 07885 232 030 E: Lesley.Keddy@virgin.net
PDF Creator - PDF4Free v2.0 http://www.pdf4free.com
4. Click on the Input Message tab to continue
5. In this dialog box we are going to enter a message which will displayed as a
comment each time a cell in column B is selected. This works like a prompt.
6. Click in the title box and type the message that you wish to appear when the cell is
selected. In this example we have used Date Value
7. Click in the Input Message box and type an appropriate message for the user as
above
Next we are going to use the Error Alert box to set up an error message in case the data
does not meet the preset criteria.
8. Click on the Error Alert tab – the following dialog box will appear:
Provided by Abacus Computer Training, Rickmansworth
T: 01923 710552 M: 07885 232 030 E: Lesley.Keddy@virgin.net
PDF Creator - PDF4Free v2.0 http://www.pdf4free.com
If you click on the drop-down arrow under style you will be presented with 3 choices
as follows:
a) To display an information message that does not prevent entry of invalid
data, select Information
b) To display a warning message that does not prevent entry of invalid data,
select Warning
c) To prevent entry of invalid data, select Stop.
9. In the Title box type an appropriate title for the Error Message box e.g. Wrong Date
Entered
10. In the Error Message box type a meaningful error message as per the example
above
This function is very using for reducing the margin of error. The operator is
presented with a reason why their data is incorrect. Other examples it can be used
for are as follows:-
a) to make sure that a value equals a value in a specified list
b) to make sure a value entered is between two values
c) to ensure entry of text rather than numeric data
Provided by Abacus Computer Training, Rickmansworth
T: 01923 710552 M: 07885 232 030 E: Lesley.Keddy@virgin.net
PDF Creator - PDF4Free v2.0 http://www.pdf4free.com
