All language subtitles for 2. Other Types of Data Validation

In the last lesson, we started exploring data validation dropdown lists, and whilst these are probably

the most popular type of data validation in Excel, there are other different types of data validation

that we can use to control the information that's going into our cells.

So in this lesson, I just like to explore a few of the other options.

I'm going to start out by taking a look at data validation using whole numbers.

So we have a very basic example here.

I have the days of the week listed out, and each day of the week has a number assigned to it.

And effectively, what I want to do over here is I want to be able to type in the number and have it

return the day and in-cell F7.

I actually already have an index, a match formula, which is going to do that for me.

So currently, if I type in number two into this cell, it's going to bring back Tuesday.

If I type in number four, it's going to bring back Thursday, so on and so forth.

But what about if I type in something that doesn't fall within that range of one to seven?

If I type in an eight and enter, I'm going to get an an error.

And we actually saw this in one of our earlier modules when we were looking at the EF-S formula.

Now there is an easy way that you can get rid of this narra simply by using the F and a formula and

wrapping it around the index a match.

But we can also use data validation to help us handle situations like this.

And the reason why I like to use data validation for this type of thing is because it gives the user

or whoever is using this spreadsheet instruction and guidance as to what they need to enter into the

cell.

So let me show you what I mean.

I'm just going to put this back to a number that is valid.

Let's click on the cell and go up to data validation.

Now, this time we're going to select whole number, and what we can do is basically define what this

number needs to be, what conditions need to be met for this number to be valid so I could pick a number

between two numbers.

It could be not between or equal to or not equal to or greater than so on and so forth.

So if I want my users to enter in a value between one and seven, I'm going to use the between option

and then I just need to enter in one as my minimum and seven as my maximum.

And what you'll also notice is that I have two additional taps up here for input message and error alert.

So if I want to be really helpful to my users, I can give them a clue as to what they need to type

in to this cell.

So it might be that I add a title here called instruction that says, please enter a number between

one and seven.

And it might be that I want to add an amber alert so that if they do type something in, that's incorrect.

They get a customized error message.

So we're going to say stop number is not between one and seven.

And it's worth noting that you do have some different styles here.

You don't just have the stop style.

You could choose to have that as a warning or information.

Now I'm going to keep mine on stop and click on.

OK, now notice what happens straight away.

As soon as I select that cell, my instruction text is popping up.

So this is really good for anybody using this worksheet because they don't really have to think it just

tells them what they need to enter in.

So if I was to now enter in a number outside of those parameters, I'm going to get that error alerts

as well.

No, is not between one and seven.

So data validation is a great way of adding instruction and guiding people who are using a spreadsheet.

It's a really good thing if you're going to be sharing a spreadsheet with lots of other people.

So that is the whole no option.

Let's take a look at a couple of other ways that we can use data validation.

We can also use it to validate dates.

So it might be that I want people to enter in their date of birth, but they must be over 18.

So let's click on the cell.

Let's got to data validation, and this time we're going to use a date.

And what I'm going to say here is that the date that they enter must be less than or equal to, and

will say, December the 30 first 2000 and full anything before that date basically means that person

is over 18.

So once again, I can add an input message.

Let's say warning must be older than 18 and an error alert, let's say stop.

You are not old enough.

Let's click on, Okay, so I'm getting my instruction just there.

And if I enter my birthday, let's type that in.

That's absolutely fine.

But if I enter that same date, but let's say 2005, I'm going to get that error message because that

means I'm under 18.

So some really cool ways that you can use data validation.

Now, the final example I'm going to show you is text length.

So this is good if you have to have a specific amount of characters going into a cell, for example,

maybe I'm typing in some three character airport codes.

Most airport codes are three characters long, and I want to confine people to only being able to type

in three characters into this cell.

We can jump back up to data validation.

Go to our settings and we have a text length option.

And I'm going to say that this has to be equal to three characters.

And of course, I could add an input message and an error alert as well.

Now, I'm not going to here just to show you what happens.

If you don't add those in, let's click on.

OK.

So if I type in an airport code, that's absolutely fine because it's three characters.

If I type in something like that, it doesn't work.

Now, notice I'm still getting a pop up message.

It's just not a customized one.

So that's the difference if you don't actually specify your own custom error alert.

So now we've covered for data validation options.

We do have a few more in here, but they all work along the same lines, so I'll leave you to have a

little play around with those.

One that we are going to look at is this custom option.

I'm going to look at that in the next lesson.