Understanding the Need for Excel Data Validation

-: In this section, we're gonna be taking a look at adding data validation to your Excel lists or tables. Now, open in front of you, I'm still working inside of the same exercise file, Excel-102-ExerciseFile-01. So make sure you've got that open and you're following along with me. And I'm currently looking at the Purchase Request worksheet. Now, on this worksheet, I've got a simple little table. It's got some headers along the top. There we go. And it's got your rows of records down below. Now, first, before we get in and start implementing some data validation techniques, let's take a look at why you would wanna use these features. So there's a number of things inside this list that we really need to address. There's some formatting differences, there's incorrect spellings, there's the numerics, or dates aren't being read as numeric or dates. So all things that we wanna clean up, really get ready for our list and for adding our data validation to this list. Really I wanna control how users are entering data and what data they're entering into this list. So let's take a look. The first one is the request ID. Now, if you look really closely at this column, kinda dig through each record, you'll see that there's a common pattern, but the pattern gets interrupted a couple of times. So if we look at the first one, we've got PR, capital PR, hyphen, and then four digits. In this case, 1001. Now, if I look to the next record, PR-1002, PR-1003, and so on, but I get to the fourth record, PR, that's great, but there's no hyphen. It just goes right into the four digits, 1004. Okay, so right there we've got a validation issue. We've got inconsistency in the value itself. So later on when I perform sorting or filtering or calculations or PivotTables, I'm gonna run into some issues here. So I wanna make sure that we have consistent data. It should always have PR at the beginning, capital PR, then a hyphen, and then four characters. Now, the next one is these dates. Now, they all look like dates, but you'll notice that most of 'em are right aligned. Excel recognize these as valid date values, but there's a few inside of here that right away I can identify that something's wrong about 'em, right? Whether it's dates being treated as text or it's dates that don't really have proper formatting for a proper date, such as these dots inside the date. That's great, and I see people use that quite a bit, but it's not something we wanna do inside of Excel, especially later on if we wanna perform calculations on this data. Plus, I just want consistent, clean formatting throughout my data. That's something else we're gonna need to fix. Let's see, if I look into the Request Type column, at the surface, everything looks good, right? Hardware, software, office supplies, services, software, hardware, soft. Oh wait, just a minute. We got a spelling error in there. I got S-O-F-W-A-R-E, so I'm missing a T inside that software. And I think there's a few others. We got hardwore, we got an O instead of an A. (instructor humming) And oh, right here, got another misspelling, office suplies, we're missing a P in supplies. So there's some issues inside that column. Once again, we got inconsistent data, we got dirty data so that later on when I go to perform reporting, such as a PivotTable, and I group on the request type, I'm gonna have issues there. So I wanna make sure that that is nice and clean. And then our estimated cost column, you know, I wanna clean that up. We got some numbers that are being treated as text in there, so when I perform like a sum operation in there, I'm not gonna get a proper return value because some of those numbers, they look like numbers, but they're being treated as texts and Excel mathematically won't work with text values. And there's a few other things in there, but that's just to name a few of 'em. We're gonna go through, we're gonna clean up the data and then start adding some validation rules so that we don't run into these issues. So let's jump in and first start cleaning up this worksheet.