Dependent List Data Validation in Excel – how can I use this more advanced example of data validation in my spreadsheets? Watch the video to learn more
This week’s hint and tip is about dependent list data validation in Excel. This looks at how you can use data validation to create drop down lists that are dependent on the selection from another drop down list. This is covered in our Advanced Excel training course but we decided to do a hint and tip on it to cover it in more detail. We are going to go through it now below.
What it is and where to find it
As mentioned before, data validation is a feature in Excel that allows you to restrict what someone can enter into a spreadsheet. You can have a number of types of validation rules with number and list being a couple. The data validation option is found on the Data Tab and in the Data Tools section on the ribbon.
What is dependent list data validation?
Dependent list data validation is a slightly more advanced example of how you can use data validation. Standard data validation allows you to just select from a list and it will insert the selected option into the cell. The dependent list type allows you to have a drop down list with options that appear depending on the outcome of a previous drop down list selection. It allows for the second list choices to be dependent on the first list selection.
Setting up the first drop down list data validation
Firstly, make sure you have a sheet with all your named ranges in it. Make sure that the named ranges for the second list match up to the first drop down list choices, this makes it easier when creating the dependent list data validation. Once you have done this, create the first drop down list data validation as you normally would do. As you named the ranges, you are able to use the named range in the source area to make it easier. For example, in the video this is =Module.
Setting up the dependent list data validation
Once the first drop down list is set up, you can set up the second dependent list. In the example, this second list will be the levels of the module that you choose from the first list. To set this dependent list up, we will use the INDIRECT function. This function returns a reference (or in this example a range of references) specified by text. This is where ensuring the named ranges match the module names helps, as it is how the second list will be created.
For the second data validation list, follow the same steps as previously but in the source area, type in =INDIRECT($A$2). $A$2 will be the cell reference of the cell the first data validation you created is in. When you click ok an error message might appear if there is nothing selected in the first drop down list. Click yes to continue as it will work once an option has been chosen from your first list.
You can now test it out by selecting a module and then seeing the level choices available.
The video below shows you how to set up the both standard and dependent list types of data validation. Both of these types of data validation can be very useful and time saving in your spreadsheets.
Take a look below at the video to find out more and then try it out on your own computer!
We hope you have enjoyed this hint and tip on dependent list data validation in Excel. Why not take a look at our previous one on using time calculations in your spreadsheets in Excel?