In this screencast, I'm going to show you an exciting feature in Excel related to data validation. And part of the data validation options in Excel allows you to use drop-down lists. Let me just give you a preview to what we're going to be developing in this screencast. So I've got maybe you're in charge of a bunch of employees and you've got all these time sheets to manage. You've got the date, the employee, the rate, the hours, and so on. What I'm going to show you how to do is validate data so maybe the pay period is April 1st through April 30th. And if you try to enter in, so the first thing if you try to enter in a date that doesn't make sense, then it's going to give you an error. And by a value that doesn't make sense, I mean you can set the data validation or the range at which it will accept in this table. Also in the employee column, we can make it such that there's little drop-down lists here. Maybe the employees are only limited to this list over here on the right. I'm going to show you that if we have a rate schedule over here as a function of employees, we can use that with a simple lookup function. But it can look up the rate and then you would have to put in the hours here. We can select overtime or not. So there's a little option to put yes here for overtime so we can select overtime. I've also got a formula in the pay column that detects whether or not you have selected over time here. And if you have selected over time, it's going to add time and a half, so 1.5 times the hourly rate. So that's what we're going to be creating in this screencast. I've got this file called time sheets available for you to work with on the INITIAL tab, that's where going to start with. I've also got this All Data Tab and so we're going to actually copy the data here in a moment. Let's just start with a single entry and we're going to want to make this into an Excel table. I would highly recommend when you're making Excel table, so just start with the first row and then format it how you want. Then when you add in more rows to this data table, it automatically formats the rest of the table. So I've got the date here, and employee, the rate. Now I'm going to first set it up for data validation. Data validation will only accept a certain range that you specify or a certain condition for these inputs. So let's click on the date. Data validation is found up here in data, and there's this little data validation tool so we can click on that. Now what we want to do, this is a date. So my validation criteria, it's going to be a date. And here I'm going to have a between condition so you could change that if you want. The start date, I'm just going to refer to the begin date of our period, really important that you make that an absolute reference by adding the dollar signs and using F4 key to make that absolute. The end date, I'm also going to click up here and make that absolute by pressing F4 or manually adding the dollar signs. Then I can go ahead and click OK. What happens now is if somebody tries to put in something that's not in that range, it gives an error as I showed earlier in the preview. The employees here, we want to perform input validation here for the employees. So we're going to make a drop-down list. Let's go up here to Data Validation. Now you can allow lists. So if I click List, you can either type in names here so I could Charlie, Max, and so on. Or if you've got this in a list like I do already, then I'm just going to go here and I'm going to select that. There it added the dollar signs automatically, which is good. So that's going to allow us only to select an employee in that list. I go ahead and click OK and now you notice that there's this little drop-down menu. And I can select whoever I want but it's constrained or restricted to the employees that we have here. This just ensures that somebody doesn't put in somebody in the employee box that is not a current employee. Now the rate, I've got this little table over here. This is sort of our lookup table, our schedule for the different employees and their rates, so Perry, Harry, Terry, Mary, Jerry, Barry, Carrie, Gary, and Larry. So we want to be able to look up based upon the employee that's next to it what their rate is. We can put in the vlookup function. Before we do that, I'm going to go ahead and name this data set. I'm just going to name that rates up here in the name box. Then we can use the VLOOKUP function. We can look up the value next to it. The table array that I'm looking through is rates. The column index that I want to output is actually the second column of this table, which is 2. And I always put a false there if you need for it to be an exact match. I can go ahead and press enter. So what it's doing is it's looking through here for the employee and it's spitting out the rate and it changes based upon who I select. I'm going to go ahead and make that accounting to $21. The overtime box here, I'm going to also add data validation. So let's go Data, Data Validation, and this I want to constrain, this is also going to be a drop-down list. And I just want this to be either -- or Y. The double dash will basically make it blank and that's going to be our default. The default always comes first here. Let's go ahead and click OK. And so now the user can select yes, or they can select that- - which just makes it blank. You could put in an N there if you wanted to, but I like to keep it nice and clean and blank if there's no overtime. The pay calculation is going to be an IF statement. So the first thing we're going to do is we're going to check to see if there's overtime. If the overtime box, the cell next to it is equal to yes, then what we're going to do is we're going to take 1.5 so we're doing 50% extra pay. So maybe for holidays or weekends, 1.5 times the rate times the number of hours. If it's false, we just want to do 100% of the rate, which is just the rate times the hours. So that's going to be a little selection strategy based upon whether or not one has selected over time in the cell right next to it. For example, if I change this to a yes, then it automatically recalculates to be 150% of the regular pay. So now that we've got this all set up, we formatted everything nicely including the data validation. So I'm going to highlight this. I'm going to go to the Home tab and I'm going to do Format as table. And I'd like to use this green one. And now I've got my table. Now the nice thing about the table is you can add rows. So I can press the tab key when I'm on the last cell of that row and it'll add up an entire new row. The nice thing about tables also is we can take other data. So here I've got a lot more data. So I'm just going to select Mary down because I've already got Carrie there, control copy and I can go ahead and paste here. Now the nice thing about this is I can take the data here and I can just merge right into that table and it automatically recalculates, reformats. So it added the dollar signs. It performed all the calculations. One more thing I forgot to do is this last column, we want it to be accounting. So there's our dollars and now I'm just going to select a couple of them that have earned overtime. So Jerry, Perry, and Mary, these are all overtime and you see that it's a live calculation. You can always go back up here to the Table Design and do Total Row. And this is how much we're going to have to pay overall to all these employees in that month of April. If we wanted to calculate hours, I can always select from this drop-down menu the Sum, so total hours. And so that's how we can kind of use this data validation in a table. And again, if I try to let's say I added something else and I tried to put in Sally, it's going to give us an error because the data validation is only accepting things that we specified using that data validation tool. So let me go ahead and delete this last row, can delete table rows. The one last thing I wanted to show you is the nice thing about this is the next time the next month rolls around, you can just change to May 1st and May 31st. And then you can just copy paste the data right in here and it's going to automatically update. Maybe you give a employer raise, maybe Terry gets a raise to 20 bucks an hour and everything automatically updates. That's one of the really nice things about using Excel spreadsheets is everything updates in a live manner. So hopefully, you learned a lot about data validation in this screencast.
