Scribd
Upload
283 views2 pages

Data Validation

Data validation in Excel allows you to control the information entered in worksheets. It can restrict entries to a specific type, create a list of options, set value ranges, and display messages. You can create a list by typing options directly, in a row or column, or on a different worksheet. Data validation also allows setting the data type (whole number, decimal, date, etc.), minimum and maximum values, and displaying input or error messages.

Uploaded by

Rachkara Paul
Copyright
© Attribution Non-Commercial (BY-NC)
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
283 views2 pages

Data Validation

Data validation in Excel allows you to control the information entered in worksheets. It can restrict entries to a specific type, create a list of options, set value ranges, and display messages. You can create a list by typing options directly, in a row or column, or on a different worksheet. Data validation also allows setting the data type (whole number, decimal, date, etc.), minimum and maximum values, and displaying input or error messages.

Uploaded by

Rachkara Paul
Copyright
© Attribution Non-Commercial (BY-NC)
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 2

Tutorial | www.corality.

com
DATA VALIDATION IN EXCEL
Data validation is a feature available in Excel that helps you to control the information that is entered in the worksheets. This tutorial describes ways to use the data validation feature and examples to illustrate how to implement it. Data validation restricts entries to a specific type in a cell. It allows you to do the following: Create list of options / items in a cell Set range of values that can be entered in a cell Create a prompt message / warning explaining the kind of data allowed in a cell Data validation dialog box can be activated by choosing: Data Validation in Excel menu.
Has the input been validated? Select option No

Calculate deposit on opening cash balance?

1=Yes,0=No

Yes

Create list of options


You can create a list of the entries you will accept for a cell in the worksheet. Such lists of items can be typed directly into the Source box in the data validation dialog box or it can be typed in a row or column on any worksheet in the workbook. Screenshot: Create a list by typing directly You can also custom format your options such as displaying 1 and 0 as Yes and No respectively. Refer to screenshot and our tutorial titled Custom Formats in Excel to learn about custom formatting.

Type a list directly in the dialog box


Follow these steps: Select a cell Select from menu: Data Validation On the Settings tab, click List in the Allow dropdown list By default, the Ignore blank and In-cell dropdown check boxes are selected. Do not change them In the Source box, type the list of entries that you wish, e.g.: Yes,No,Maybe Click OK In that cell, click the drop-down list and then click any item it contains. Refer to the screenshot for example. Note if you try to manually enter anything other than the list of entries that have been created in the Source box, a stop message will appear and your only options are Retry or Cancel.

Type a list in row / column


If the list of options is more than a couple of items, it will be easier to maintain if you type the list on a worksheet. For example in column E44:E46, type in the repayment options. The way to enter the reference in the data validation dialog box is as shown below
A B C 41 42 Repayment method D E Select option F Credit Froncier

43 44 45 46

Repayment options

Credit Froncier Equal P Flexible

Screenshot: Create a list in worksheet

Note: if the list of options is stored on a different sheet in the same workbook, you need to name the range first before using it as data validation source in another sheet. The screenshot below shows an example of where the list of options is named Products (The name box is located to the left of the formula bar). As shown, in the Source box in data validation dialogue box, we type in = Products or press F3 and paste in the range name.
Purchased product Select option US product 2

A B 6 7 8 9

F 20 mths

GH

L 9:30:00 AM 9:00:00 AM 10:00:00 AM

Whole number
Construction period

3 Time
Production start time Recommended earliest start Recommended latest start

10 11 12

4 Text Length
Year for maintenance capex 6 Password abcdef

13 14 15

Decimals
Price input 5.55

5 Date
Insurance start 31-Mar-09

Screenshot: Create a list in different worksheet

Set range of values in a cell


You can place limits on the data that can be entered in a cell. You can set or exclude a range of numbers, or set the minimum and maximum for certain cells. Select a cell and click: Data Validation On the Settings tab, Allow drop-down, click either Whole number, Decimal, Date, Time, Text length In Data you could choose criteria such as between, not between, equal to, etc. You can then type values into the dialog box, or Refer to cells in the worksheet, or, Use formula to set the values Refer to the examples in the screenshot for various validation criteria examples, more examples can be found in the accompanied workbook. Input / Error message could be added to these types of restricted cells to alert user on kind of data allowed.
16 17 18 19 20 21 Historical price 2004 5.00 AUD/t 2005 5.43 2006 4.95 2007 6.89

Screenshot: Various validation criteria examples

Error Alert
Click on the Error Alert tab to activate then add a check mark to the box. The alert message can be typed in the designated box. There are three styles of Error Alert from the drop-down list: Stop: User cannot enter invalid entry in the cell Warning: User could choose to enter invalid entry but the warning box will be displayed. If Yes is clicked, then the invalid entry is accepted. Information: This highlights the entry of invalid data; the user could leave the invalid entry in the cell.
Password Input Message abcdef Error Alert

Create input message / warning


Input Message
An Input Message can be displayed when the cell with data validation is selected: Select from menu: Data Validation Input Message Add the check mark in the box Type your message heading and message in the Title box and Input message box.

Screenshot: Input Message and Error Alert

You might also like