Hi, my name's Mr. Hall, and welcome to this Oak National Academy lesson, which is called Data Validation in a Spreadsheet Model.

This lesson is taken from the Spreadsheets unit.

Thank you for joining me today, and I look forward to learning with you in this lesson.

The outcome of this lesson is that you'll be able to apply data validation techniques to improve data accuracy.

So let's get started.

We've got two keywords in this lesson today.

So the first one is data validation.

So data validation is a tool that controls what kind of data can be entered into a cell.

And then we've got criteria.

So criteria means the rule set in data validation that decide what data is allowed in a cell.

So there's our keywords, data validation and criteria.

We've got two learning cycles.

So the first learning cycle, which we'll start in a few seconds, is to describe the need for data validation.

So that's where we'll start.

And then we'll move on to using validation rules in a model.

So let's get started with the first learning cycle.

A spreadsheet is a digital document which is used for organising and storing data.

They're used to help you analyse and present data in meaningful ways.

And there's a screenshot of a typical spreadsheet application there on the left of the screen.

This one is Google Sheets.

Other spreadsheet applications may look slightly different.

Spreadsheets can become very large, and when I say very large, I mean very large.

One popular spreadsheet software has a maximum size of 10 million cells.

As you fill up your spreadsheet with lots and lots of data, there are a lot of elements to keep track of, so you need some way to help you do that.

There's a number of things that can make using a spreadsheet challenging.

So Sam says, "First of all, when I type information, sometimes I type it into the wrong cells." Then Andeep adds, "When working with so much data, it's very easy to make a mistake." When working with spreadsheets, it is easy to put data in the wrong cell, or to make a typing mistake and enter incorrect information.

Data validation controls what is allowed to be entered into a cell.

It helps prevent mistakes by making sure the data is in the correct format or within the set limits.

So this is what data validation can help you to do.

So first of all, it can help you to reduce errors.

It can help you to keep data consistent, and help avoid incorrect results in calculations.

And it does this by using criteria.

So there's our second keyword, which we started off with.

Think of it like a digital checklist.

So the spreadsheet will only accept what is on the list.

Time for a knowledge check.

So what does data validation do in a cell? Does it a, change the background of a colour; b, limit what data can be entered; c, format cells automatically; or d, adjust the font size? That's right, data validation limits what data can be entered.

So the correct answer is b.

Data validation, when used correctly, can make a spreadsheet more user-friendly.

There's two kinds of messages that data validation can add.

So first of all, you can have an input message, and that appears when you click in the cell.

Or you can have an error message, and that appears if you enter something wrong.

And using these messages can make a spreadsheet easier to use.

So a question here.

When could you use data validation in a spreadsheet? And we've got responses from Andeep, Sam, and Sofia.

So Andeep says, "You can use data validation when tracking dates.

You could set the cells to only accept numbers that are formatted as dates." Sam says, "You could use data validation to stop users leaving cells blank, the sheet could show instructions." And then Sofia says, "If you're entering scores from a game, you could set it to only accept numbers from one to five, for example." Now, you may well have seen data validation in other places.

Any form where you enter information on a computer may use data validation.

So we've got an example here on this slide.

So under the username, it says the username must be between 3 and 25 characters, as if you are signing up to some kind of service, for instance.

The password then must have at least eight characters, and it must include one lowercase character, one number, and one special character.

And there's further validation in that you need to enter the password again.

So you've got to enter it once, then enter it again, identically.

So there's three different types of data validation going on on that example of a form you can see on the slide there.

So time for a true or false question.

Data validation is useful because it lets you enter anything into cells.

Is that true or false? That's right, it's false.

Data validation helps prevent incorrect data being entered into a cell.

It does this by limiting what could be entered by using criteria.

So a Rockstar Challenge is a televised competition, where people can text or phone to vote for who they want to win.

The judges record their opinions in this spreadsheet table whilst the contestants are performing.

So we've got a row of headers here.

We've got the contestant, song performed, score between 1 and 10, the judge's comment, audience reaction, and then next round? So some of the data doesn't need to be validated.

So the name of the contestant and the song performed doesn't need to be validated.

The score must be a whole number between 1 and 10.

The judge's comment must be one-off: Needs work, Good, or Excellent.

The audience reaction must be: Booed, Clapped, or Cheered.

And then in next round we have either Yes or No or True or False.

So here's the instructions for your task.

First of all, open the spreadsheet file Judging Panel.

Look carefully at the data in the table.

Then identify six problems can see with the data.

Then explain how data validation could help fix or stop these problems from happening.

So let's have a look at the answer to number three.

This is the six problems with the data.

So Zane Storm's score says "seven" as a word instead of a number.

Blaze Harmony has "Excel" in the judge's comment, which isn't one of the allowed comments.

Cassandra's score is 11, which is too high.

So it's outside the range of 1 to 10.

Nova Dream has "Ok" in the judge's column, which isn't one of the allowed comments.

Ruby Riot's audience reaction says "Good," but that doesn't match the list of allowed reactions.

And then, finally, Zane Storm's audience reaction says "clap," but that doesn't match the list of allowed reactions either.

And question four, how could data validation stop these problems? So by allowing only the correct options in each column, it can check if the data is in the right format and give an error message if it's not.

Okay, on to the second learning cycle.

So we're going to use validation rules in a model.

Data validation is a way to prevent user error by limiting the data a user can input.

There are different types of data validation rules, including: You can have a list.

So in a list, the user chooses from set options like specific text.

You could have a number range.

So a number range only allows values between two numbers.

Or you can have a custom formula.

This is more advanced, like "must be a number" or "must be a date in the future." Time for a true or false question.

Data validation can stop users entering letters in a number-only cell.

Is that true or false? So data validation can stop users entering in a number-only cell.

True or false? That's right, the answer is true.

One way of using data validation is creating a dropdown list.

So in the example below we've got D, and the header is Judge's Comment, and below there's a dropdown list.

So the dropdown list has a dropdown arrow.

And then inside there is our dropdown list, which is comprised of three different options: Excellent, Good, or Needs work.

A dropdown list reduces errors because the user can't make any typing mistakes, they simply select one of the options available.

To set up data validation in a spreadsheet, here's the steps you need to follow.

So first of all, select the cells you want to format.

So we've got a range of cells selected here, from B2 through to B9.

Then you click on the Data menu.

And then choose Data validation from the list.

And there it is, down towards the bottom of the options.

This is the data validation rules menu.

So first of all, check the range you have selected here is the one you want.

So we wanted B2 through to B9, and that's the range we've got.

So that's correct.

In this example, the criteria has been set to a value of between 100 and 200.

So we've chosen Criteria, Is between, and then the minimum amount, which is 100, and the maximum amount, which is 200.

Another way is to only allow certain values to be added to a cell.

You can select this by using the criteria menu.

This menu lets you set criteria for either values that are greater than or less than, values that are equal to or not equal to, or values that are between a set range.

You can also use text or dates as criteria.

This menu lets you set an error message for the user.

In this example, a warning will show if the data does not match the criteria.

So here we have one of the advanced options.

So if the data is invalid, we've got Show a warning selected.

Be careful because in some spreadsheet software you may have different options of error messages.

So it might not always look exactly like this example.

Data validation, when used correctly, can make a spreadsheet more user-friendly.

There are two kinds of messages that data validation can add.

So you can have input messages, and they appear when you click in the cell.

Or you can have an error message like we've just seen, which appears if you enter something wrong.

You can set a warning in the data validation menu.

Here's an example of a warning.

So you can see the error message for cell B5 here.

The user has entered a score that doesn't fit the criteria, so a warning is shown.

And in this case, the warning gives us a little bit more information.

So the warning says that input is invalid because the input must be a number between 100 and 200.

And as you can see, the input is 279.

So it's not between 100 and 200.

So there's our warning message.

Time for a question.

Why would you set an error message in data validation? Is it a, to warn users when they format a cell; b, to tell users they're in the wrong spreadsheet; c, to guide users when they enter data that doesn't follow the rules; or d, to change the font colour of incorrect data? Well done, the correct answer is c.

So we'd use an error message in data validation to guide users when they enter data that doesn't follow the rules.

So watch the video on using data validation of whole numbers.

Now you can move on to task B.

Use the spreadsheet file Judging Panel, which you used in task A.

Next, in the score column, add data validation to only allow whole numbers from 1 to 10.

Then in the judge's comment column, add a dropdown list with: Excellent, Good, and Needs work.

And then in the audience reaction column, add a dropdown list with: Cheered, Clapped, Booed.

Next, add an appropriate error message to columns D and E.

Then fix any errors in the spreadsheet.

And finally, add a new column in column F.

Add a heading to that column, which is "next round." Then using data validation, create a dropdown list that only contains the options Yes and No.

And mark which contestants are through to the next round.

You can view the completed solution in the spreadsheet file Judging Panel Solution.

Your spreadsheet should look something like this.

Now let's have a look at what you should have done in the spreadsheets themselves.

So first of all, I'm gonna start with the original spreadsheet, and in there we can have a look for the errors that are in the original.

So first of all, I can spot that we've got the score of "seven" written in characters, written in letters, rather than as a numeral.

So that's one error.

We've then got below that an error that the score is greater than 10.

So the score is 11.

We've next got an error in the judge's comments, where there's an "Ok" comment, where it should be either Excellent, Good, or Needs work.

And there's another one in D6 here, where it's "Excel," which looks like that's a misspelling of "Excellent." And then in the audience reaction column, we've got some errors as well.

So we're looking for Cheered, Clapped, or Booed in that column.

So we've got an error of "Clap" in cell E5.

That's not one of the permitted responses.

And it looks like we got one more, which is in cell E9, where the audience reaction is listed of "Good." And once again, the audience reaction should be Cheered, Clapped, or Booed.

Those are the areas in the original spreadsheet.

Now we'll have a look at the solution spreadsheet to see the data validation applied.

So here's the solution with the data validation rules in place.

So let's have a look first of all at column C.

So in column C, I can look at the data validation, which tells us it's a list.

And the source numbers are 1 through to 10.

So anything outside of that should not be in that cell.

So that looks good.

In column D, we'll look at the data validation in column D.

So column D, once again, a list, and we've got Excellent, Good, and Needs work.

Okay.

And this time we're in a cell dropdown.

So I can select them from a dropdown list here, like this.

And then in column E, we're looking for data validation similar to column D.

Once again, we've got a list this time: Booed, Cheered, or Clapped.

So that's all looking good.

We've got an additional column, column F, added.

And there should be validation to column F.

So let's look, see what's on the dropdown first this time.

So we've got Yes or No.

So we can just see that in the data validation menu.

So we have the sources Yes or No in a list, once again.

Now, we can tie this up a little bit more.

We can add in some error messages.

So column C, we want to say something if a number outside of the range 1 to 10 is entered.

So I'm just gonna go back into Data Validation and click in Error Alert.

And I'm gonna say Show error after invalid data has been entered.

So there is a default error message, but I'm gonna change it for this one.

So I'm gonna call it Error.

So input must be between 1 and 10.

Okay, I'm just gonna apply that to the one cell there first.

So cell C2.

So I'm gonna try entering 11 now on cell C2.

And I get the error message saying that the input must be between 1 and 10, one we've just set up.

So we'll retry and we'll put three back in there.

Similarly, we can put an error message in for column D as well.

So I'm gonna do multi-select here, select the whole range, and go to Data Validation and, sure, Error Message.

I will once again say Error.

And I'm just gonna say, "Select from dropdown list." So I'm just gonna test that now.

So I'm gonna change it to Great.

See what happens.

I get the error, which says, "Select from the dropdown list." So retry.

This time I'll use the dropdown list and change it back to Needs work.

And we could do exactly the same with column E.

So just adding to all of the selection data validation and then filling in the error alert there.

So one more time, Error.

Select from list.

Okay, we could also add that validation to column F if we wanted to.

So now we've got all the columns with the data validation they need.

The only thing I didn't do was validate the remaining fields in column C.

That would be easy, just to select the whole range and apply it to all of column C.

So this is the complete spreadsheet with all the data validation rules in there that you need.

So here's a summary of what we've looked at today.

So data validation is a tool in spreadsheets that controls what kind of data can be entered into a cell.

It's used to help prevent mistakes, such as typing the wrong word or entering numbers outside a set range.

Various types of criteria can be used in data validation, such as text and number ranges.

Data validation can show helpful messages when users make an error.

Using data validation helps keep data accurate and consistent.

Thank you for joining me for this Oak Academy lesson.

I hope you've enjoyed learning with me today, and I look forward to seeing you again in the future.