Data validation allows you to
control exactly what a user can enter into a cell. It allows you to do the
following:
·
Make a list of the entries that restricts the
values allowed in a cell.
·
Create a prompt message explaining the kind of
data allowed in a cell.
·
Create messages that appear when incorrect data
has been entered.
·
Check for incorrect entries by using the
Auditing toolbar.
·
Set a range of numeric values that can be
entered in a cell.
·
Determine if an entry is valid based on
calculation in another cell.
Validation
Criteria
To specify the type of data
allowable in a cell or range, follow the steps below, which shows all the three
tabs of the Data Validation dialog box.
1. Select
the cell or range.
2. Choose
Data » Data Tools » Data Validation. Excel displays its Data Validation dialog
box having 3 tabs;
a. settings,
b. Input
Message and
c. Error
alert.
Settings
Tab
Here you can set the type of
validation you need. Choose an option from the Allow drop-down list. The
contents of the Data Validation dialog box will change, displaying controls
based on your choice.
·
Any Value
o Selecting
this option removes any existing data validation.
·
Whole Number
o The
user must enter a whole number.For example, you can specify that the entry must
be a whole number greater than or equal to 50.
·
Decimal
o The
user must enter a number. For example, you can specify that the entry must be
greater than or equal to 10 and less than or equal to 20.
·
List
o The
user must choose from a list of entries you provide. You will create drop-down
list with this validation. You have to give input ranges then those values will
appear in the drop-down.
·
Date
o The
user must enter a date. You specify a valid date range from choices in the Data
drop-down list. For example, you can specify that the entered data must be
greater than or equal to January 1, 2013, and less than or equal to December
31, 2013.
·
Time
o The
user must enter a time. You specify a valid time range from choices in the Data
drop-down list. For example, you can specify that the entered data must be
later than 12:00 p.m.
·
Text Length
o The
length of the data (number of characters) is limited. You specify a valid
length by using the Data drop-down list. For example, you can specify that the
length of the entered data be 1 (a single alphanumeric character).
·
Custom
o To
use this option, you must supply a logical formula that determines the validity
of the user’s entry (a logical formula returns either TRUE or FALSE).
Input
Message Tab
You can set the input help
message with this tab. Fill the title and Input message of the Input message
tab and the input message will appear when the cell is selected.
Error
Alert Tab
You can specify an error
message with this tab. Fill the title and error message. Select the style of
the error as stop, warning or Information as per you need.
Reference :
https://support.microsoft.com/en-in/help/211485/description-and-examples-of-data-validation-in-excel
follow us on http://arivilm.blogspot.in
like us on https://www.facebook.com/Arivilm2501/
No comments:
Post a Comment