Transcripts for the above video clip:

Data Validation Tool

The data validation tool allows you to specify limits for what can be entered into a cell and deliver appropriate messages if an error is made. In this segment you will learn how to activate the data validation feature and use it to control the inputs into your spreadsheets.

Here we have a simple spreadsheet and what we want to do is control what gets entered into the various cells. So you will see there is a sales person column, we want to put a department in, we want to set some targets but we want a minimum of 5 and a maximum of 25. These are calculations. We want to specify a month and a year, we want to put some initials down but have a maximum of 3 characters, and we want to specify the next review date but it must be within 90 days of this date.

So now lets use the data validation tool on this spreadsheet. In this column I want the salesperson's name. I do not want to have t re type it every time, so we can use the data validation tool. So if I click on DATA, then VALIDATION, this box will pop up. Now lets just understand what they are showing you here. The first tab is settings and as you will see it sees ALLOW, and in this case it says any value. So all cells allow any value at this stage. If I click on it you will see there are a number of options, so we can allow a whole number, a decimal, a list, a date, a time, a text length or a custom. In this example I am going to allow for a list. What you will see now is it tells me , tell me what the source is. So I can go in here and type. So for ease I am going to type a A, and to separate my options with a comma, B, C, D for example. Now you will see the second tab here says INPUT MESSAGE. So what this allows you to do is to give the user a message when he clicks on the cell so I'm going to say "Enter a sales persons name". The last tab allows you to alert the user to an error and explain what was done wrong, so we can say "Error- choose a valid name". So now when I say OK what you will see is if you click on the cell you get the message we entered, if you click you will see our options pop up, you can choose one of them, and if, for whatever reason I typed in  something that was wrong we get our error message.

As an alternate to typing the list within the data validation tool we may want to have a separate list so that we can change the names here and the drop down box will change, you do not have to go every time and change every single one of them. So what we can rather do is click on the cell , go DATA, VALIDATION, and you will see our settings pop up. But in the source you can just overwrite this, delete it, and actually highlight the list, and I am happy with all the other settings. So when I say OK, and I click here on the drop down list you will see that my settings are all now based on this list, and if for example I clarify this persons name and I went back to the list, you will see that the list updates itself. So that's quite a useful way of organizing your drop down lists.

Now lets continue with the spreadsheet. In this cell I want to use the departments, and you will see I have another sheet where I have the list of the department names. So what I want to do is go DATA, VALIDATION, I want a list and what you will discover is that the data validation tool doesn't allow you to refer to other sheets, which is a bit of a problem. The work around for  this is to make use of Named Ranges. I am not going to go through what they are now I am just going to show you, you highlight the cells you click in there and give it a name, in this case we will call it department. And now this block of cells is known throughout the spreadsheet as department. When I go back I can now click on that cell, say DATA, VALIDATION, I want it to be a list, and the source now equals that group of cells which happens to called departments. When I say OK you will see it has gone and fetched that list from outside this particular sheet. That's the one limitation of the data validation tool , the list has to be on the same sheet or you must give it a named range.

Now we want to capture the targets, says a minimum of 5 or a maximum of 25. Again we can go to DATA, VALIDATION. This time however, we want to specify that this is a whole number and the same applies to decimal. We will go whole number and you will see we have a whole bunch of options i.e. data between, not between, equal to, not equal to etc, so you just need to choose whichever one you want. In this case I want to be between a minimum of 5 and a maximum of 25. And again if you wanted to you could put an input message and an error alert. When I say OK you will see that nothing visible changes, and now I go in here and enter 6, you will see it is allowed, however if I try and enter 4, you will see I am told there is a problem so I know I need to enter a number that meets those criteria.

We now want to enter the month and year we are involved in. You can see here that we have specified that the start date can be that and the end date can be that. So we want this information to be between these 2 dates. So again it is DATA, VALIDATION. In this case now we are looking at DATES, and it will be a similar concept for time but we will use dates. And again you can specify between, not between etc. The start date I am going to link to that cell, the end date to this cell, so I can change these and all the data validations will change themselves. Again if needs be we can put an input message and an error alert, and when I say OK, I can now enter say 3rd Jan 2008, which is within the period so that's fine, however, if I had to go 3rd Jan 2007, I get warned that there is potentially a problem there.

Lets say now I want to capture the initials of the capturer, but we only want to allow for 3 characters. Again it is DATA, VALIDATION, in this case the option we want is text length, and again there is a whole bunch of options, in this case I want a minimum of 1 character and a maximum of 3 characters. I can click OK. If I put in  my initials those are fine but if I have  another initial it tells me there is a problem.

The last part of data validation I want o show you is the custom option. So now we want to type in a date here, but it says it must be within 90 days of this date. So it is basically a calculation. So with the Data Validation tool you will see if we go to custom we now can enter a formula and what we need to say is take that, add 90 days it must be the date we entered or less. So I can say equals, I specify what it is looking at, so it must look at what is in this cell, and that must be less than that cell and in this case we are going to put plus 90. When I say OK, I can now enter a date in here, so if I enter lets say 1 Feb 2008, it allows it. But lets say we put in a date in a years time, so lets say 3 Jan 2009. Again we are warned there is a problem.

This is a very nice way to make sure what is captured makes sense and avoids finger trouble. We now have our spreadsheet set up. So lets just clear some stuff here and go through the capture process.

One other useful bit of information with regards the data validation tool is because it is not sitting within a cell you can't obviously tell that this cell has validation or that cell has validation because there is just no way of seeing it. Now that can be a problem if you want to make changes to data validation. So Excel has another tool called GOTO Special . So if you goto EDIT, GOTO, and SPECIAL you will see that over here there is an option which says show me, or GOTO, all the Data Validations and it can either be all the data validations in the spreadsheet or the same data validations as per the current cell. So if I say ALL you will see it highlights all the data validation cells and then you can work on them. So this is just a quick way to find the data validations. You can find out more about the GOTO special tool in other video clips (use the navigation bar on the left).