Understanding the Need for Excel Data Validation

Kyle Pew
A free video tutorial from Kyle Pew
Microsoft Certified Trainer
4.6 instructor rating • 19 courses • 918,901 students

Learn more from the full course

Microsoft Excel - Excel from Beginner to Advanced

Excel with this A-Z Microsoft Excel Course. Microsoft Excel 2010, 2013, 2016, Excel 2019 and Office 365

18:08:51 of on-demand video • Updated May 2021

  • Master Microsoft Excel from Beginner to Advanced
  • Build a solid understanding on the Basics of Microsoft Excel
  • Learn the most common Excel functions used in the Office
  • Harness the full power of Microsoft Excel by automating your day to day tasks through Macros and VBA
  • Maintain large sets of Excel data in a list or table
  • Create dynamic reports by mastering one of the most popular tools, PivotTables
  • Wow your boss by unlocking dynamic formulas with IF, VLOOKUP, INDEX, MATCH functions and many more
  • Access to a Professional Trainer with 10+ years of Excel Training
English [Auto] 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 in sort of our Excel documents. So I want to give you a scenario here. I was out teaching a class, a life 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 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 up the surveys to their customers and it asked for things like their name, their address, their state, their zip code, their phone number, their email and so on and so on. Asked 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 spelled 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. When I go to exercise file, which we've downloaded by now, hopefully you're following along with me and I'm looking at the sort and filter worksheet instead 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 no make model doors, auto smoke, yes or no convertible ensured 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 there. Nineteen ninety five to a maximum of thirty nine. Ninety five. I don't want anything outside of those bounds. Have to stay within that range and 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 we 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 point all the yeses. I don't want to have to look for why and yes. And misspelled. Right. I just want to look for a consistent value sorting bill three 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 makes of automobiles. Well, I don't want them to put 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 away. That makes it easy for data entry so that we get consistent, correct formatted data instead 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.