- Data Validation Rules in Excel
are used to control what users can enter into a cell.
So this could be something like a number
between certain values or only items from an existing list.
For this example,
I have a note here that says,
"The value must be a whole number between one and 10."
So I'll create a Data Validation Rule
to limit entries to just these values.
With my cells selected,
the Data Validation Rules will be
under the Data tab in the Data Tools section.
And once here, click on Data Validation.
First, we need to select what we'll allow.
In this case, I need a whole number
and I need it between one and 10.
So my minimum will be one, my maximum will be 10,
and click Okay.
When you create a Data Validation Rule,
by default, Excel is going to apply a Stop Alert.
See what happens if I try to type a number
that is not within the rule settings.
This type of alert prevents the cell
from accepting my entry even after I've pressed Enter.
It also displays a pop-up message that reads,
"This value doesn't match the data validation restrictions
defined for this cell."
The thing is, if you stick to just using the default alert
you're missing a really great opportunity
to educate your users on how to fix whatever they enter
so that it does match the rule settings.
Let's walk through our error alert options.
You can set the alert settings
as you're making your rule,
or you can edit an existing rule.
In this example,
we're editing the rule we created just a moment ago,
and because the rule is already set,
I'll need to select the cell range
that has the rule applied to it.
And with those cells selected,
we'll need to go back into Data Validation.
And for this we'll need to go into the Error Alert Tab.
So the first thing you'll want to do,
is make sure that the
"Show Error Alert After Invalid Data Is Entered" box
is checked off, or your rule won't do anything.
Notice if I uncheck this box,
how I'm able to enter whatever value I want
into these cells, despite that I have a rule set up
that's supposed to prevent me
from entering anything that is not a whole number
between one and 10.
So let's undo this,
and go back into Data Validation, check off our box.
Here you have three styles of error alerts to choose from
and each behaves just a little differently.
The Stop Alert is the default setting.
This is the one we saw just a moment ago.
It prevents users from typing anything
that does not match the settings
in the Data Validation Rule.
Again, if I was to enter 1.5 into any of these cells,
I'm not able to do that and I get the default message.
Let's go back into Data Validation.
The next option is a Warning.
And notice how the icon here changes to a warning symbol.
This alert communicates
that an error has been entered in a cell
but it still gives the user the option to ignore the alert
and continue with their entry without making a correction.
Again, if I type a number outside my parameters
it lets me know that my entry is invalid,
but I can still click Yes to continue.
Let's delete this and we'll select our cells again
and go back into Data Validation.
The next style option is Information.
This one is the most lenient one
and it tells the user that their entry doesn't match
the Data Validation Rule Settings,
but it still lets them continue without fixing it.
So for this one, if I type something outside of my settings
it just says this value doesn't match,
but I can just click, Okay, and it'll still be accepted.
So let's delete this.
Obviously, you would choose the alert type
that works best for your project and your audience.
But regardless of which you choose,
this is still a really great opportunity
to give your users instructions
on how to fix what they entered.
With our cells selected, we'll go back into Data Validation
under the Error Alert Tab.
Again, make sure that the
"Show Error Alert Invalid Data Is Entered," is checked off.
Because if it's not, no error message is going to display
and your users will be able to enter whatever invalid data.
And select which of the three types of error alert warnings
you'd like for your project.
I'm going to select Stop for this example.
And next, create a Title for your error alert.
In this example, we could title it something like,
"Score values between one and 10."
(keyboard thumps)
Then in the "Error Message" field,
we can write more detail about the error
and instructions for how to fix it.
But keep in mind, that it's still a good idea to be brief.
So for our example, we can write something like,
"Values can only be whole numbers between one and 10."
And then in parenthesis
we'll type some whole numbers as examples.
And when you're done click, Okay.
Now if we type something that's outside of the settings,
we'll get this customized Data Validation Error Message.
Thanks for watching, and for more tutorials like this,
click on these links. Thanks!