Data Validation
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.
Make a List of Entries Allowed in the Cell
You can make a list of the entries you will accept for a cell on a worksheet. You can
create a dropdown list and restrict the user to enter data.
data -data tools-validations
Enter Values B
Create a Prompt Message Explaining the Type of Data Allowed in a Cell
You can create a message that explains the type of data that can be entered in a ce
Enter Values A
Create a Message That Appears When Incorrect Data Is Entered
The style of the error message Microsoft Excel displays when you type incorrect da
Create a Stop Alert Message
If you create a Stop Alert message and enter invalid data in the cell, your only optio
Enter Values 12
Create a Warning Alert Message
A Warning Alert message has three options: Yes, to allow entry of the invalid data;
Enter a date 9/12/2019
Create an Information Alert Message
When an Information Alert message appears, you can click OK to accept the invalid
Enter a text zXzXZxzczx
Check for Incorrect Entries Using the Auditing Toolbar
After your data is entered, you can look for entries that are outside the limits you s
Enter a number 6
Determine If Entry Is Valid Based on Calculation in Another Cell
A= 10 B= 2
Rejecting Sundays and Satutdays
Enter a date 8/29/2019
Monthly Budget <=5000
Transportaion 1200 Apply the validation rule in the range where we are i
Entertainment 900
Grocery 780
Total 2880
How to prevent duplicate entry of data
SapNo
1000
1002
1003
1004
1005
1001
Code Should always start with "C" followed by three digits
Enter Item Code C345
Dependent DropDown lists
Pizza Mediterranenan Bacon chillichicken
Pancakes Pepperconi Cheese Chop Suey
Chinese California Crab rangoon
NewYorker
Enter your favorite food Chinese Favorite Dish
cts the values allowed in a cell.
g the kind of data allowed in a cell.
incorrect data has been entered.
the Auditing toolbar.
can be entered in a cell.
on calculation in another cell.
ept for a cell on a worksheet. You can then restrict the cell to accept only entries taken from the list by using the data validation
India China Japan Bhutan
in a Cell
pe of data that can be entered in a cell. When you select the cell, the message appears near the cell
l displays when you type incorrect data determines whether the restrictions are enforced.
nvalid data in the cell, your only options are Retry or Cancel; you are not allowed to enter invalid data in the cell.
Yes, to allow entry of the invalid data; No, to give you a chance to enter valid data; and Cancel, to remove the invalid entry.
you can click OK to accept the invalid value or you can click Cancel to reject it.
ntries that are outside the limits you set. When you click Circle Invalid Data on the Auditing toolbar, circles appear around cells t
lidation rule in the range where we are inputting values.
TRUE
create a named ranges for each section
Food A96:A98
Pizza B96:B99
Pancakes D96:D97
Chinese E96:E99
chillichicken
st by using the data validation feature.
India Cell Reference.
d data in the cell.
remove the invalid entry.
ar, circles appear around cells that contain incorrect entries. If you correct an invalid entry, the circle disappears.
disappears.
