Understanding the Need for Excel Data Validation

So during this section of the course, we're going to take a look at the data validation feature that's built into Microsoft Excel. Now, before we get in and start to actually mess around with the data validation settings, let's get a good understanding of what data validation is and why it's important, why we should be using it inside of our Excel documents. So I want to give you a scenario here. I was out teaching a class, a live class to a group of individuals, and somebody had brought up a scenario that they had run into. They had built this database that was going to collect information on their clients, their customers. And the way that they were going to get this data was from some surveys that they were going to send out. Now, they took this task and they shipped it off to a third party, said, Hey, this is what we want. Let's, let's get the survey sent out and let's gather this information from our customers, our clients. Now, unfortunately, that third party put no validation on the data. Here's what happened. They sent out the surveys to their customers and asked for things like their name, their address, their state, their zip code, their phone number, their email and so on and so on. Ask for a bunch of information from these individuals. Now, when the data came back, the data was all over the place, formatting wise phone numbers were formatted different ways. Some had parentheses, some had dots, some had hyphens. There. There states where they lived, where some were spelled out, some were abbreviated, some were misspelled. They were just all over the place with the data when they finally got it back from their clients. Now, the problem was they brought that data into their reporting system and they really couldn't create any reports because they had this dirty data or inconsistent formatting within their data. This is where data validation comes into play. What we want to do is when we send data out or we got a list that users are going to be filling out, we want to provide a means to make sure that we're getting properly formatted data back. Things are spelt correctly, phone numbers are formatted the way that we want them. Values such as numeric values that might range from X amount to this amount. We want to make sure we stay within that threshold. We don't want any extraneous data that shows up inside of there. So we want to be able to control how users are inputting data. Now open in front of you, I've got the example file that we're going to be working with. This is inside of the Excel 102 exercise file, which you've downloaded by now. Hopefully you're following along with me and I'm looking at the sort and filter worksheet inside of this workbook. Now you can see this is a rental car status and it's got a simple little table in it. It's got a handful, handful of fields in there. ID number make model doors, auto smoke, yes or no, convertible insured and rates. Now, users are going to be in here manipulating the data, adding records, deleting records, editing records. They're going to be working with this list. Now, I want to make sure things like the rates, perhaps we've got a certain threshold within our rates we can only go from. Let's see, what's the lowest one? They're 1995 to a maximum of 39, 95. I don't want anything outside of those bounds. Have to stay within that range. Let's see for the number of doors. Right. Maybe we've got a range there for the auto smoke convertible insured. Those are just in. And why just yes and no questions, right? That's it. I don't want anything else in there. Right. We could have them put yes or no. But then what happens when you filter later on? I want to be able to find all the yeses. I don't want to have to look for why. And yes. And a misspelled right. I just want to look for a consistent value sorting, filtering and so on. Later on when I create reports off of this, I want to build a group by the automatic column and I just want to see why. And then nothing else inside the mix. We get into the make. We only carry certain mix of automobiles. Well, I don't want them putting things in here like a Porsche or a Lamborghini. Actually, maybe I do want that in there. But for our purposes here, I don't want any additional ones in there. And I want to make sure that people are spelling things right, like Oldsmobile. I'm going to misspell that. So let's make sure that we give them a way that makes it easy for data entry so that we get consistent, correct formatted data inside of our list. This is why we use data validation. So let's take a look at how you create data validation and how you can apply it to your list.