1 00:00:01,670 --> 00:00:06,020 Why do you need data validation? Data validation is necessary 2 00:00:06,080 --> 00:00:12,530 If you're designing templates that are going to be filled by other people. It's also necessary if you're 3 00:00:12,530 --> 00:00:15,980 designing reports that will be used by others. 4 00:00:16,030 --> 00:00:20,500 Imagine you need to collect this information from different departments. 5 00:00:20,510 --> 00:00:22,000 So you're sending this out. 6 00:00:22,070 --> 00:00:26,040 You want each department lead to fill out this information about their team. 7 00:00:26,060 --> 00:00:31,940 You create this mini template and you even add in some comments so they know what to fill in. 8 00:00:31,940 --> 00:00:38,390 For example employee I.D. should be a value between 100 and 999 and segment should 9 00:00:38,390 --> 00:00:41,840 be picked from game, productivity, and utility. 10 00:00:41,840 --> 00:00:46,230 The first department has completed this information, they just sent it back to you. 11 00:00:46,250 --> 00:00:48,240 And this is what you see. 12 00:00:48,260 --> 00:00:52,640 You take a closer look and you notice something's off. 13 00:00:52,700 --> 00:00:54,730 For example here for employee I.D. 14 00:00:54,890 --> 00:00:57,000 This one doesn't look like a number. 15 00:00:57,020 --> 00:00:58,430 These are numbers. 16 00:00:58,430 --> 00:01:03,260 Now it's not that sensitive to have everything as a number for employee I.D. because you're not planning 17 00:01:03,260 --> 00:01:09,410 to add them up together but it would be nice to have a consistent look and have all these as numbers. 18 00:01:09,980 --> 00:01:10,670 For segment, 19 00:01:10,670 --> 00:01:12,230 There's also something strange. 20 00:01:12,410 --> 00:01:15,360 One is game and the other is games. 21 00:01:15,470 --> 00:01:18,940 Productivity was also spelled incorrectly. 22 00:01:18,980 --> 00:01:20,330 And look at the data here. 23 00:01:20,330 --> 00:01:22,350 This one is left aligned. 24 00:01:22,430 --> 00:01:25,090 Remember we said dates are actually numbers. 25 00:01:25,130 --> 00:01:29,080 So if you input them correctly they should be right aligned. 26 00:01:29,090 --> 00:01:29,840 This one is not. 27 00:01:29,900 --> 00:01:36,220 So either someone added that one by mistake or should be the eight eight or it should be month 8 and 28 00:01:36,220 --> 00:01:37,850 the day should be 18. 29 00:01:37,940 --> 00:01:42,980 If this was a bigger list it's going to take you a lot of time going through these and making these 30 00:01:42,980 --> 00:01:44,110 corrections. 31 00:01:44,120 --> 00:01:47,050 This is something you can avoid with data validation. 32 00:01:47,060 --> 00:01:54,560 So now let's assume you sent out the same template except you added data validation to it and then you 33 00:01:54,560 --> 00:01:56,120 got back this. 34 00:01:56,120 --> 00:01:58,120 This is recognized as a number. 35 00:01:58,130 --> 00:02:03,710 This is game, productivity was input correctly and the date has been input correctly. 36 00:02:03,710 --> 00:02:06,150 But where is the data validation here. 37 00:02:06,200 --> 00:02:07,990 It's actually in the cell. 38 00:02:08,479 --> 00:02:13,840 So here let's type something that's not a number. Let's just type "ninety" 39 00:02:14,330 --> 00:02:15,280 We get an error. 40 00:02:15,320 --> 00:02:18,410 This value doesn't match the data validation restriction. 41 00:02:18,530 --> 00:02:24,590 So I can't input "ninety". Could I input that number as a text? so "'985" press enter. 42 00:02:24,590 --> 00:02:27,440 I can't I have to input a number. 43 00:02:27,440 --> 00:02:31,090 What if I input a number that's not between 100. 44 00:02:31,100 --> 00:02:31,640 What was that? 45 00:02:31,640 --> 00:02:33,300 100 and 999 46 00:02:33,350 --> 00:02:34,520 So let's say just put nine. 47 00:02:35,360 --> 00:02:37,890 I also can't input that. 48 00:02:37,910 --> 00:02:39,820 And what about the productivity? 49 00:02:39,830 --> 00:02:47,420 There is a dropdown here and I have to select between these. Could I type in just "games" here? 50 00:02:47,540 --> 00:02:48,360 I can't. 51 00:02:48,800 --> 00:02:49,190 I see. 52 00:02:49,190 --> 00:02:51,370 "Please select from the list provided" 53 00:02:51,740 --> 00:02:57,000 I'm going to cancel and select from the list provided. Also the date. 54 00:02:57,020 --> 00:03:03,560 What if I wanted to input 18 here? What do I get? "This value doesn't match the data validation restriction 55 00:03:03,950 --> 00:03:05,700 that was defined for this cell" 56 00:03:06,200 --> 00:03:10,700 So at least to a degree I can make sure that what I receive is correct. 57 00:03:10,700 --> 00:03:17,470 Obviously they can input the wrong date but at least they cannot input a date that's not recognized. 58 00:03:17,480 --> 00:03:18,940 How does this work? 59 00:03:18,950 --> 00:03:20,040 Let's take a quick look. 60 00:03:20,450 --> 00:03:24,230 So let's implement data validation on the original list here. 61 00:03:24,260 --> 00:03:26,780 I'm just gonna remove the numbers. 62 00:03:26,780 --> 00:03:31,190 For name I'm not going to input any type of data validation because it should be clear what they should input there. 63 00:03:31,190 --> 00:03:33,100 For employee I.D.. 64 00:03:33,170 --> 00:03:38,640 I want to make sure that they input a number that was between 100 and 999. 65 00:03:38,660 --> 00:03:45,880 So to do that I'm going to go to Data > Data validation and select the first data validation here. 66 00:03:46,040 --> 00:03:53,510 The default data validation for any cell is to allow for any value to be input. Instead of any value, 67 00:03:53,630 --> 00:03:55,730 I'm gonna make a selection from here. 68 00:03:55,730 --> 00:04:01,730 So in this case what do you think would fit best? A whole number. 69 00:04:01,730 --> 00:04:05,420 Now I can decide what I want the minimum and maximum to be. 70 00:04:05,510 --> 00:04:07,410 So I have other options here. 71 00:04:07,430 --> 00:04:10,360 One is between, not between, equal to, and so on. 72 00:04:10,370 --> 00:04:16,200 So in my case between works out well between a 100 and 999. 73 00:04:16,430 --> 00:04:21,950 In addition to this I can define an input message and an error alert. 74 00:04:21,950 --> 00:04:28,700 So if they don't input a number that's in between this range I can say please ensure the number is between 75 00:04:28,950 --> 00:04:32,380 100 and 999 and click on OK. 76 00:04:32,480 --> 00:04:39,950 Now if I input 250 nothing happens, it's all good. If I input 25 and press enter, I get that error 77 00:04:40,000 --> 00:04:45,140 alerts that we saw before so "please ensure the number is between 100 and 999" 78 00:04:45,260 --> 00:04:47,900 And I can retry and press enter. 79 00:04:48,020 --> 00:04:52,540 So now that I applied the data validation to this one, I'm happy with it. 80 00:04:52,640 --> 00:04:56,490 How do I apply that data validation to these cells. 81 00:04:56,540 --> 00:05:04,940 I can do that by just dragging the cell down. The data validation is copied automatically but my values 82 00:05:04,970 --> 00:05:06,650 also copied automatically. 83 00:05:06,680 --> 00:05:09,350 So let's just make sure that the data validation is there. 84 00:05:09,350 --> 00:05:11,180 I'm gonna put 25 here. 85 00:05:11,180 --> 00:05:13,190 I get this pop up, so it's there. 86 00:05:13,200 --> 00:05:20,560 Now if you wanted to copy the data validation but without the value in there you can actually copy the 87 00:05:20,560 --> 00:05:23,060 cell and paste as special. 88 00:05:23,660 --> 00:05:25,050 So I'll just show you quickly here. 89 00:05:25,050 --> 00:05:32,840 So I would copy this Control+c, come here, highlight this range, right mouse click, go to paste special, and 90 00:05:32,840 --> 00:05:35,760 select validation, and then click on OK. 91 00:05:35,870 --> 00:05:40,890 So this just copies the data validation without copying the number. 92 00:05:40,890 --> 00:05:42,610 Now let's take a look at segment. 93 00:05:42,620 --> 00:05:48,440 How do I add that dropdown? This time I'm going to highlight the area I want to have the data validation 94 00:05:48,470 --> 00:05:54,030 in and then go to Data Validation. Let's go back to settings here. 95 00:05:54,050 --> 00:06:00,680 I want to get a list here, a dropdown list. For that I need to select list. For source I have different 96 00:06:00,710 --> 00:06:01,550 options. 97 00:06:01,550 --> 00:06:05,660 One option is just to type them directly in here. 98 00:06:05,690 --> 00:06:10,320 So if this is something that's not going to change, you can directly type it here. 99 00:06:10,320 --> 00:06:15,690 The separator you need to use is your Excel separator. 100 00:06:15,720 --> 00:06:20,950 So in my case it's a comma, I can put the comma here and click on OK. 101 00:06:20,970 --> 00:06:25,230 Now when I click on these cells I have my data validation. 102 00:06:25,800 --> 00:06:30,440 If you want to make this more dynamic you can use a cell reference for this. 103 00:06:30,510 --> 00:06:36,750 So I'm going to highlight the area go back to data validation and instead of fixing it here I'm going 104 00:06:36,750 --> 00:06:38,190 to use a cell reference. 105 00:06:38,190 --> 00:06:40,460 So on another sheet called "Master". 106 00:06:40,950 --> 00:06:43,990 I've typed in the different segments I want. 107 00:06:44,040 --> 00:06:51,000 I'm going to highlight them here and then let's also actually add an error alert and click on 108 00:06:51,000 --> 00:06:52,140 OK. 109 00:06:52,230 --> 00:06:58,620 So now these are dynamic based on the data I have in master. 110 00:06:58,620 --> 00:07:05,460 So if I change one of these divisions or if I want to add a division to this, I can add one to the 111 00:07:05,460 --> 00:07:07,490 middle of this segments. 112 00:07:07,500 --> 00:07:07,740 Right. 113 00:07:07,740 --> 00:07:12,830 Because remember I restricted the data validation to be only this range. 114 00:07:12,870 --> 00:07:16,140 If I add one to the middle, it's going to recognize it. 115 00:07:16,170 --> 00:07:22,560 If I add it to the bottom it's not going to expand it. To get this to update automatically if you're adding 116 00:07:22,560 --> 00:07:24,230 new segments to the bottom. 117 00:07:24,270 --> 00:07:25,680 You have different options. 118 00:07:25,680 --> 00:07:28,870 One option is to just include more cells at the beginning. 119 00:07:28,870 --> 00:07:35,220 There's just going to be empty in your dropdown list or you can turn this area into an official Excel table. 120 00:07:35,220 --> 00:07:40,320 Now Excel tables or something we're going to take a look at in a future section. 121 00:07:40,320 --> 00:07:46,500 So just keep this in mind for now that it is possible and later you can see how you can update this 122 00:07:46,500 --> 00:07:47,790 using a table as well. 123 00:07:48,300 --> 00:07:51,480 So I'm just gonna add something else just to see that this is dynamic. 124 00:07:51,480 --> 00:07:55,530 Let's just put in new segment and let's go back to data validation. 125 00:07:55,770 --> 00:08:02,640 Let's click on this and I see games was updated and the new one that I added in the middle was actually 126 00:08:02,760 --> 00:08:04,920 added to this dropdown as well. 127 00:08:04,950 --> 00:08:12,540 Right so that's the advantage of having a list that's connected to cell values. 128 00:08:12,540 --> 00:08:15,340 Now let's take a look at the start date. 129 00:08:15,480 --> 00:08:19,160 Go back to Data Validation, to settings. 130 00:08:19,320 --> 00:08:26,520 This time my data validation is going to be a date. I can select between which date, equal to a certain 131 00:08:26,520 --> 00:08:27,340 date. 132 00:08:27,450 --> 00:08:28,790 So greater than. 133 00:08:28,800 --> 00:08:29,630 So let's say 134 00:08:29,760 --> 00:08:36,429 I need to make sure that the date is greater than the first of January. 135 00:08:36,570 --> 00:08:40,140 Let's say 2014, click on OK. 136 00:08:40,200 --> 00:08:41,159 So let's try. 137 00:08:41,159 --> 00:08:47,020 I'm going to put the first of January 2013 138 00:08:47,040 --> 00:08:53,220 It's not allowed, the 1st of January 2015 139 00:08:53,220 --> 00:09:02,760 That's allowed. Another thing you can do here is to say it should be less than today's date and there 140 00:09:02,760 --> 00:09:09,330 is a formula for todays date. There is actually an Excel function and it's called the today function. 141 00:09:09,540 --> 00:09:14,490 And since it's a function you need to open the bracket and close the bracket. 142 00:09:14,490 --> 00:09:17,820 So this by default is always today's date. 143 00:09:17,820 --> 00:09:18,280 Click on ok. 144 00:09:18,310 --> 00:09:26,320 So now if I attempt to put a future date in here, I will get that error message. 145 00:09:26,320 --> 00:09:29,340 Another thing I can do is to add the input message. 146 00:09:29,350 --> 00:09:31,620 So we've taken a look at these settings part. 147 00:09:31,620 --> 00:09:38,880 We took a look at the error alert, now the input message is just some information to the user on what 148 00:09:38,880 --> 00:09:42,100 they should input here and click on OK. 149 00:09:42,510 --> 00:09:46,310 So now every time they come to the cell they see this input message. 150 00:09:46,320 --> 00:09:49,300 Once we click away that input message is gone. 151 00:09:49,320 --> 00:09:52,680 Now we want to apply this data validation to the rest. 152 00:09:52,830 --> 00:09:58,430 So I'm just going to copy that cell, highlight the area I want to have the data validation in, right mouse 153 00:09:58,560 --> 00:10:03,100 click, go to paste special, and select validation, and click on Ok. 154 00:10:03,100 --> 00:10:03,160 . 155 00:10:03,210 --> 00:10:09,060 So this also brings in everything in data validation. It brings in the error message and it brings in 156 00:10:09,330 --> 00:10:11,670 the input message. 157 00:10:11,670 --> 00:10:14,850 How can we remove an existing data validation? 158 00:10:14,880 --> 00:10:23,130 You can go back to Data > go to Data validation, from settings select any value. That's going to remove 159 00:10:23,130 --> 00:10:26,700 the data validation from your highlighted cells. 160 00:10:26,700 --> 00:10:31,570 So right now my data validation is gone and I can input any value in here. 161 00:10:31,590 --> 00:10:36,990 Another way to remove data validation is just to copy an existing cell. 162 00:10:36,990 --> 00:10:41,780 That's just normal cell, it doesn't have any data validation over your area. 163 00:10:41,800 --> 00:10:48,270 So if I do Control+V here, I take out the data validation from these. And if I also pasted over these 164 00:10:48,270 --> 00:10:52,630 where I have the list, I take out the data validation from them as well. 165 00:10:53,270 --> 00:10:53,590 Okay. 166 00:10:53,610 --> 00:11:01,380 So that's the basics of data validation and how it can save you time when you create templates or reports 167 00:11:01,380 --> 00:11:03,810 that are designed to be used by others.