Excel - Data Validation

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. 2.

Select the range of cells where you want the criteria to apply 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

PDF Creator - PDF4Free v2.0

M: 07885 232 030

E: [email protected]

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 11560 22-Sep-94 11561 25-Sep-94 11562 25-Sep-94 11563 27-Sep-94 11564 29-Sep-94 11565 30-Sep-94 11566 30-Sep-94 11567 30-Sep-94 11568 30-Sep-94 11569 03-Oct-94 11570 03-Oct-94 11571 03-Oct-94 11572 03-Oct-94 11573 04-Oct-94 11574 05-Oct-94 11575 05-Oct-94 11576 05-Oct-94

1. 2. 3.

Number TC/001 TC/004 TC/024 TC/004 TC/001 TC/004 TC/010 TC/024 TC/001 TC/028 TC/004 TC/010 TC/001 TC/107 TC/004 TC/010 TC/001

Company Ability Lectern Systems Adept Computer Technology Lectern Systems Ability Lectern Systems Northwind Systems Adept Computer Technology Ability Inova Business Services Lectern Systems Northwind Systems Ability Courseware Company Lectern Systems Northwind Systems Ability

Amount VAT £3,472.00 £607.60 £782.00 £136.85 £690.00 £120.75 £499.00 £87.33 £2,890.00 £505.75 £1,809.00 £316.58 £3,635.00 £636.13 £804.00 £140.70 £1,575.00 £275.63 £638.00 £111.65 £597.00 £104.48 £357.00 £62.48 £2,556.00 £447.30 £2,869.00 £502.08 £254.00 £44.45 £329.00 £57.58 £3,002.00 £525.35

Total £4,079.60 £918.85 £810.75 £586.33 £3,395.75 £2,125.58 £4,271.13 £944.70 £1,850.63 £749.65 £701.48 £419.48 £3,003.30 £3,371.08 £298.45 £386.58 £3,527.35

To do this highlight the whole of column B Select the Data ribbon and choose Data Validation 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

PDF Creator - PDF4Free v2.0

M: 07885 232 030

E: [email protected]

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

PDF Creator - PDF4Free v2.0

M: 07885 232 030

E: [email protected]

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 To prevent entry of invalid data, select Stop.

c) 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) b) c)

to make sure that a value equals a value in a specified list to make sure a value entered is between two values to ensure entry of text rather than numeric data

Provided by Abacus Computer Training, Rickmansworth T: 01923 710552

PDF Creator - PDF4Free v2.0

M: 07885 232 030

E: [email protected]

http://www.pdf4free.com