Welcome to week one. Welcome Nicky. >> Hi Prashan. >> This week we're going to be talking about data validation. So Nicky, can you let our learners know why data validation is so important? >> Of course, the reality is that we're going to be entering a lot of data into our workbooks, and that introduces the possibility of human error. You've heard the saying, garbage in, garbage out? Yep, if we put rubbish in, our result's going to be rubbish coming out. So we want to try and avoid these errors as much as possible, and one way to do that is to use Excel's data validation tools. >> For example, we might know that all the staff in our company have employee IDs, which are just eight characters long, and every employee's ID might start with the number four. Or the employees might also work for a certain defined set of departments, and we don't want anyone typing in HR when we mean human resources, AC when we mean accounting, because when we go to summarize that data, our summaries are just going to break. >> Absolutely, and these are exactly the kind of issues data validation can help us with. >> So for example, you can specify that you want a whole number between two values, and that will help with your employee ID. You can even specify you want a date within a certain range, or text of a certain length, and probably most usefully, you can even specify that you want the value to be within a certain list, and that list can be typed in directly into data validation, or you can actually have the list sitting somewhere else in your workbook. And what Excel will do, is it will actually give you a little drop-down list, and this not only helps keep the data correct, but it speeds up and simplifies the data entry. >> And with data validation sometimes we get a bit scared because the error messages are quite alarming. Is [LAUGH] there any way of making it a bit more user friendly for our learners? >> Definitely, it's kind of tucked away on a hidden tab, but one of the things we're going to look at is how we can actually create user friendly error messages, and that's highly recommended. You can also even create a little prompt. So if what you need the user to type in isn't totally obvious, when they click on the cell, a little box pops up saying, make sure the date is MM/YY. >> Now with the day to day running of a business, duplicates can creep into our spreadsheet, and data validation itself hasn't got anything in built for that. >> Yes, and that's a shame actually, because that we be really good, but the good news is that you can actually use formulas in your data validation, and this opens a whole realm of possibilities, and one of those is to avoid duplicate entries, and that's something we're actually going to look at how to do in our practice video. >> We must let our learners know that data validation is about data entry validation. If the errors are already there, or if we copy and paste, or input data, data validation's not going to help us in that instance. >> You're completely right. But fortunately there are also quite a lot of tools to help us work with cells that have data validation and one of the ones is you can actually identify any values that don't meet the data validation criteria. So, although we can't prevent the error necessarily getting in, we can at least identify it once it's there, and we'll also look at how we can copy and paste validation, and even locate cells that contain validation. >> And as a bonus we have tucked along at the end of the week, we've got some advanced conditional formatting to identify values, which are not necessarily errors. >> Absolutely, so yeah, sometimes it's not that you want to pick up an error, but you actually want to pick up something that meets certain criteria. Now we have looked at conditional formatting in a previous course, but as you said, we're going to look at more advanced conditional formatting, and similar to the data validation, we're going to actually look at how we can include a calculation in our conditional formatting to make it that much more sophisticated. >> Thank you, Nicky. >> Sure. >> Now it's over to you. We've got some great practice videos for you to work alongside with us. Make sure you download the Excel workbook so that you can follow us step-by-step and do it yourself. Then come back and practice these videos again because practice makes permanent. We've even got a great toolbox for you to check out, and in that there's a great ninja tip. There's also a practice challenge where we're going to take your Excel skills and apply it in a totally different context to give you confidence. So now, it's over to you. [SOUND]