Scribd
Upload
82 views2 pages

Excel Data Validation Guide

Data validation is used to define restrictions on the data that can be entered into cells. This helps ensure invalid or erroneous data is not entered. There are several types of data validation that can be used including restricting values to a list, numbers within a range, dates within a time frame, text length, and validating based on formulas. Data validation can also create drop-down lists of valid values and provide input messages and error alerts when invalid data is entered.

Uploaded by

Maricon Duran
Copyright
© © All Rights Reserved
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
82 views2 pages

Excel Data Validation Guide

Data validation is used to define restrictions on the data that can be entered into cells. This helps ensure invalid or erroneous data is not entered. There are several types of data validation that can be used including restricting values to a list, numbers within a range, dates within a time frame, text length, and validating based on formulas. Data validation can also create drop-down lists of valid values and provide input messages and error alerts when invalid data is entered.

Uploaded by

Maricon Duran
Copyright
© © All Rights Reserved
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

Data Validation

What is Data Validation?

Data Validation is used to define restrictions on what data can be entered into a cell. Invalid data will have a warning
display. Drop-down lists may also be created from the items in a list.

Data Validation

 On the Data tab in the Data Tools group press the


down-arrow next to Data Validation
 Select Data Validation

Data Validation Choices

 Restrict data to predefined items in a list: limit values to


a predefined list of cells within the worksheet
 Restrict numbers outside a specified range: determine
the range of numbers that are valid
 Restrict dates outside a certain time frame: specify a
time between a date and a date in the future frame that
is valid
 Restrict times outside a certain time frame: specify a
time from a certain time frame
 Limit the number of text characters: limit the length of
characters input into a cell
 Validate data based on formulas or values in other cells:
validate the data with a formula or value maximum

Restrict Data Entry to Values in a Drop-Down List

 Select one or more cells to validate on the spreadsheet


 On the Data tab, in the Data Tools group, click Data
Validation
 In the Data Validation box, click Settings tab
 In the Allow: box, select List
 In the Source: box, click the icon at the end of the box
 Select the list of valid values from the spreadsheet
 Press Enter
 Press OK
 On the selected cell(s) to validate there will be a drop-
down with the values that may be chosen

Data Validation 3/1/2012 Page 1


Restrict Data Entry to a Whole Number Within Limits

 Select one or more cells to validate on the spreadsheet


 On the Data tab, in the Data Tools group, click Data
Validation
 In the Allow: box, select Whole Number
 In the Data box, select the type of restriction
 Type in the values (in the ex. Minimum and Maximum)
 Click OK

Create a Input Message and Error Alert

 If desired, type in an Input Message and an Error Alert to


display when data is entered
 On Input Message tab, type in a Title and Input message
 On Error Alert tab, select the Style:
o Stop – does not allow invalid entry
o Warning—warns that the item is outside of the
limits but allows the entry
o Information—message is displayed when a cell is
selected
 When all information is entered, click OK

Example of messages when Stop is chosen:

Note: Additional information on data validation may be found on the help menu
Enter “Data Validation” in the Search box

Data Validation 3/1/2012 Page 2

You might also like