HOW TO… Excel: Create a hierarchical series of drop down boxes

As a result of prodding around the excel subreddit I found a query from user innocuous_gorilla about creating drop downs that are dependent upon each other. Due to working at NHS England where we have a massive array of organisation types and hierarchies, I quickly learnt how to do this! For example, in NHS England we have different regions, sub-regions and commissioning groups which tend to be massive long lists if un-filtered, so it makes it hard to select one in a drop down where all options available. Using the following method, you can make a set of drop downs that all depend on a master drop down(s), so choosing a region or sub-region filters the commissioning group drop down box to only those groups that belong to the chosen parent region.

How to How to... Excel