Understanding the Need for Excel Data Validation

A free video tutorial from Kyle Pew
Microsoft Certified Trainer - 1 Million+ Students
Rating: 4.6 out of 5Instructor rating
26 courses
1,776,915 students
Understanding the Need for Excel Data Validation

Lecture description

Discover why data validation is essential.

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 Microsoft/Office 365/2024

21:05:50 of on-demand video • Updated April 2024

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 [CC]
-: So during this section of the course we're gonna 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 wanna 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 gonna collect information on their clients, their customers, and the way that they were gonna get this data was from some surveys that they were gonna 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 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. Their states where they lived, 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 wanna do is when we send data out, or we got a list that users are gonna be filling out, we wanna 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 wanna make sure we stay within that threshold. We don't want any extraneous data that shows up inside of there. So we wanna be able to control how users are inputting data. Now open in front of you, I've got the example file that we're gonna be working with. This is inside of the Excel102Exercise 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 of fields in there. ID number, make, model, doors, auto smoke, yes or no, convertible, insured, and rate. Now users are gonna be in here manipulating the data adding records, deleting records, editing records. They're gonna be working with this list. Now I wanna 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? 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 N and Y, 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 Y and yes and misspelled, right? I just wanna 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 wanna see Y and N, nothing else inside the mix. We get into the make. We only carry certain makes 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 wanna make sure that people are spelling things right, like Oldsmobile. I'm gonna 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 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.