Create SMART Drop Down Lists in Excel (with Data Validation)

today's video is more than just the basics of excels data validation I'm going to show you how you can get the content of the drop-down to be dynamic so if you add new categories they automatically show in the drop-down then I'm going to show you how you can validate for specific entries for example let's say the input date should fall in the future or numbers input should fall within a range or the input values should contain a specific number of characters imagine this imagine you work for the headquarters of a company that creates different types of apps and you have three divisions if the game productivity and utility divisions now you're responsible for collecting the information and the different types of projects that each company is working on you've created this template that includes the basic information you need to collect from them in the first column you have division you need to make sure that they type in the correct division that they don't make any spelling mistakes because some people could spell games instead of the game division right and you don't want to spend a lot of your time correcting these mistakes so here you want to add a drop-down list where the people can actually select the divisions for this you can add a data validation to do that you have to go to the data tab and underneath data tools you see something called data validation just click on it the first tab here is the Settings tab and you can see that by default Excel allows you to input any value in any of these cells but you can change that you could add in additional restrictions now you can see we have different options here you can also get very creative by adding in a custom check here using formulas but in this specific case we want to add a list this means we're adding our own drop-down now under source we have different option we could use cell referencing to keep things dynamic or we could input something directly in here so let's say these divisions are super static and they're hardly ever going to change I could actually type in game productivity and utility here and I just have to use my usual Excel separator between these okay I'm just gonna go with okay and I get this little down arrow here when I click it I can choose between the three different types of divisions now if these divisions were likely going to change so let's say it could be that I rename the game division to the games division it's better to use dynamic cell references and have these divisions somewhere in Excel cells but in this case I've added them to my master data tab and I've typed in the name of the division right here so now instead of fixing them I'm going to use cell references so let's go back to data validation and for source I'm just going to say equals and let's go to master and highlight this area and say okay and now I have made three different divisions so if I change the name of the game division to the games division it will automatically reflect in here what happens if I add in another division - this is my list gonna update automatically so let's say I'm gonna add in the Health Division on the bottom here I'm gonna go back click on this it's not included right because my list was restricted to that range that we saw here so to include it I need to update this range but there are ways around this where you don't need to update this range I'm going to show you two different ways here one is that when you add something to this list not to add it on the bottom here but to add it somewhere in the middle so let's say I'm just gonna insert a row in here and then push health in between now if I go back I can see health shows up here now you can also do that by using the shift key and bringing something up so let's say I'm going to add a new one here now it's not going to show up on this list yet if I go back here and I use the shift key and then I bring it up here you basically insert a row before this so when I go back here I can see that new has been added to the list okay so that's one approach but that's something that you have to remember to do every time you add in a new division and let's say this is something that is gonna update quite often and you don't always want to think about doing this you just want to add something to the bottom of this list and you want your data validation to update automatically to do that you can turn this into an official excel table all you have to do is to click somewhere inside this data set and then press ctrl T we get this pop up where it as does your table have headers in this case it's recognized that I have a header so I'm just going to go with okay now automatically Excel assigns a table style here I'm just gonna go and clear that table style right here on their table tools Excel has also given this a default table name so in this case it's called table one so I'm just going to update that name and call this table div and press Enter now the advantage of Excel tables is that your range is generally updates automatically so now if I make a formula I just say this equals and I'm going to highlight this area you can see that Excel uses this type of table referencing table div is the name of my table and division is the name of the header and is putting it in square brackets and that's the type of reference saying that I want to use in my data validation so I'm just going to copy this press escape to leave and go back to my data validation here for source here I want to use that table reference things I'm just going to say equals and I'm going to paste that in now here's the problem detail validation doesn't like the table referencing style directly in here the way around it is to use the indirect function I have separate video that explains how indirect works and I'm going to put the link to that in the description of this video basically what it does is that it turns a text into an address so I'm gonna give the indirect function this reference as text all it does is that it translates it into a range reference and I'm gonna say okay now I can get the data validation to work and now the great thing is if I add something below this so let's say I add in my health division I don't have to worry about bringing it in the middle of this data set I can see that my table here expanded automatically and if I go back here when I click on this drop down I automatically see health in here there is also another way around this and that's to use the name manager so instead of using the indirect function you can give that table reference in name in site name manager and use that name inside the data validation the indirect function just helps avoid this one extra step now let's improve on this a little bit let's add in some text here so that the user knows what they should be doing in here under data validation we have some options to add in an input message and an error alert message select division and the input message is please make a selection from the list under error alert you can define a custom message in the first step let's just not define a custom message let's just go with okay we can see selectively please make a selection from the list if they don't make a selection and they just type brought here they get this default Excel error alert message and it says this value doesn't match the data validation restrictions defined for this cell so you could either just go ahead with that one or you can customize it by putting in your error message in here and say okay so you don't necessarily need to add in a title so I'm just gonna go with prod now I see please select from the list okay so that takes care of the division now let's just take a quick look at the other options that we have under data validation let's say that for project start days we've added a comment here that says date must be larger than today's date so whenever they open this spreadsheet and they want to input they need to pick a future date and we don't want to rely on the people reading this comment because from experience we've seen that they don't read these comments so we just want to make sure that we add in checks inside the cells we're going to go back to data validation we're going to go back to settings make another selection here in this case it doesn't really make sense to add a list because it could be any date that's bigger than today's date so which one do you think we can go with let's go with the date option here we can choose that the date is between two different dates so let's say we want the dates to be in 2018 we could make sure that the start date is the first of 2018 and the end date is the last date in 2018 but in this case we just want to make sure that it's greater than today's date so our start date is going to be dependent on the date of today and for that we can use the today formula you just have to open and close the bracket because it's a function here we can add in an input message please input project start date and under our alert make sure it's a future date and say okay so please input project start date now if I input a date from the past let's do 7 7 2017 say make sure it's a future date so I'm going to retry change that to 2018 and it works next is the project number so we want to make sure that the project number is four digits and that those four digits are between 1,000 to 2,000 let's just say that these are the project numbers that our company has decided to sign to the projects that we're doing in the current year let's go back to data validation under settings which one do you think we can go with let's check hole number we can go with between and we can define a minimum and maximum and the minimum was 1,000 and the maximum was 2,000 and I'm gonna mention that under error alert okay so if I input 1,000 it's fine if I input this it's not okay next one is company code so the code must be five characters I want to make sure here that the people don't input the name of the company but the code of the company I could give them a list to choose from or I could just do a basic check that the code that they enter is five characters long because that's how we have defined the codes in the company so I can go back to data validation here go back to settings and which one do you think I can pick for this text length I can make sure that the text length is equal to five and say okay okay so it must be five characters so I would say s let's say twenty ten that's okay but if I do se 2010 it's not okay now what you can do is to take it a step further you could also make sure that the first character is a text and the remaining characters are numbers and that's when you can use the custom function and use formulas for this you could even take this step further and make sure that the first two or three characters or text followed by a number in between followed by text in the end right as long as there is some logic you can program that inside the custom function I'm not going to show it in this lecture because that's a more advanced topic that needs to be addressed separately so if you're interested to find out how that could be done just make a comment underneath the video last days for description here I could just let them in put any value but just to make sure that they do elaborate a little bit on this project I'm gonna make sure that the text that they input here is at least greater than let's say 20 characters and say okay so if I just say it will be amazing I get an error because it's for our health then this is accepted now of course for that you can also add in an error alert so that the people know why you're not accepting their input so now we've created all these data validations for the first line but they're not applicable to the next lines to copy and paste the data validations here all we have to do is to highlight this area press control C then just go down until where you think that people are going to import so let's go to here and I'm going to highlight this right mouse click go to paste special and click on validation because we only want to copy and paste the validations and not the contents of this okay sup now we can see them everywhere so if I go back to validation here we can see that my validation is in place okay so that's how you can use data validation checks to make sure that your template has less mistakes at least you can take care of the major ones by restricting the choices that the users have this actually ends up making both your lives easier now I'm curious do you use data validation in your files if yes let me know how you use it in the comments below and as always if you liked this video I'd appreciate it if you gave it a thumbs up and for more of these videos consider subscribing [Music] you

Channel: Leila Gharani

Views: 301,114

Rating: 4.9682355 out of 5

Keywords: excel how to make a drop down list, data validation examples, excel data validation, drop down list, excel data validation growing list, excel data validation list from table, copy data validation, excel data validation date, excel indirect function, data validation text length, excel data validation another sheet, Advanced Excel Tutorials, XelplusVis, Leila Gharani, Excel 2016, Excel 2013, Excel 2010, Advanced Excel tricks, Excel online course, Excel tips and tricks

Id: FRiFfKb_B_A

Length: 15min 41sec (941 seconds)

Published: Thu May 10 2018