Note — this is a guest post from Kasper at Spreadsheeto. He and his business partner Mikkel are doing some awesome Excel training — check out their site! I was introduced to their work after Kasper’s Excel.TV appearance.
Kasper and Mikkel of Spreadsheeto.
We are kindred spirits! Spreadsheeto is also focused on teaching what’s not always evident to the new analyst from the standard tips and tricks. Check out this awesome, helpful, detailed post on drop-down lists and then visit their blog for more. Enjoy! –George
One of the coolest tools available to Excel users is the drop-down list. This tool allows us to limit the input of a given cell and therefore provides better control over our data. We will first take a look at how to create a simple drop-down list but then we will take things further by looking at how to create a dependent drop-down list. The examples will be performed in Excel 2016 for Windows.
Please download the exercise file and follow along as you read this guide.
Preparing for our drop-down lists by creating named ranges
Let’s look at the three largest nations by physical size: Russia, Canada, and the United States. We will make a list of the three and use as our primary drop-down list. Then we will create drop-down lists of the 5 largest cities in each of those by population.
The first thing we should do is create named ranges for our lists. We will do this in ‘Sheet2’ of our workbook.
In order to make a named range for our nations drop-down list, we select the range (‘A2:A4’) that includes nation names. Be mindful that we are only selecting the nation names for our named range and not the header ‘Nation’. Then we click on ‘Name Manager’ in the ‘Defined Names’ section of the ‘Formulas’ tab.
This will open the Name Manager and this is where the named ranges are stored. To create a new named range simply click on the ‘New…’ button.
By default, the ‘Refers to:’ box will populate with our pre-selected range but this can be changed as needed.
Then we simply type in ‘Nation’ in the ‘Name:’ input box.
Click ‘OK’ and we have now stored a new named range called ‘Nation’ that refers to the range of nation names ‘A2:A4’. We can create named ranges for our lists of cities in columns B through D by repeating the same process. When all is said and done, we should have three additional named ranges for ‘Russia’, ‘Canada’, and ‘United States’. These named ranges will form the drop-down lists that will be dependent on the selection in the ‘Nation’ drop-down.
Creating our first drop-down list
Now we will go to ‘Sheet1’ and select cell ‘B1’. Then we click on ‘Data Validation’ in the ‘Data Tools’ section of the ‘Data’ tab.
On the ‘Settings’ tab in the ‘Allow:’ drop-down, select ‘List’.
Once ‘List’ has been selected, we can leave the ‘Ignore blank’ checkbox checked and make sure ‘In-cell dropdown’ is definitely checked. Then we simply type in ‘=Nation’ (our named range) for ‘Source:’. Note that the equal sign (‘=’) is required unless we want our drop-down to be the single selection ‘Nation’.
Click ‘OK’.
Now we have our primary drop-down list. With a little practice, you can create a drop-down list in seconds. Click here and learn how.
Dependent drop-down list creation
Now that we have our primary drop-down for ‘Nation’, we can use the named ranges created earlier for cities to create the dependent drop-down lists that will populate based on the selection in our ‘Nation’ drop-down list.
The method for doing so is quite similar to what we already did when we created our first drop-down. So let’s create an identifier cell in C1 for ‘City:’ and then select cell D1 for the placement of our dependent drop-down list.
One thing we should be clear to note at this point is that our named range names for our city lists absolutely must match the contents of our primary drop-down list in order for the following method to actually work.
To put it a different way, we must always name our named ranges that serve as our dependent lists based on the actual text string values of the primary drop-down list selections.
This means that for our dependent list of Russian cities in ‘Sheet2’ must have a named range name of ‘Russia’. The following method uses a function that matches based on the value of cell ‘B1’ and therefore will only work if it finds a named range name that matches the value of that cell reference.
So with cell ‘D1’ selected, let’s open the ‘Data Validation’ dialog box again like we did to create our primary drop-down list. Again, we will select ‘List’ and make sure both the ‘Ignore blank’ and ‘In-cell dropdown’ boxes are checked.
However, instead of selecting a named range as our ‘Source:’ this time, we need to type ‘=INDIRECT(‘ and then click on cell ‘B1’. Once we close our parenthesis, it should look like the following.
What the ‘INDIRECT’ function essentially does is return the actual value in cell ‘B1’. So if ‘Russia’ is selected from the drop-down list in ‘B1’, ‘=INDIRECT($B$1)’ is actually ‘=Russia’. Since ‘Russia’ is the named range that contains the Russian cities, cell ‘D1’ will populate a drop-down list with the contents of that named range. Pretty cool!
Furthermore, by using the ‘INDIRECT’ formula on the cell reference ‘B1’, the dependent drop-down list in ‘E1’ will change dynamically based on selection changes in the primary drop-down list. Even cooler!
There it is. Dynamically changing content for dependent drop-down lists in Excel. Anyone who learns this simple skill can certainly impress friends and colleagues. It is a super useful skill when there is a need to control data input and improve the professional quality of Excel worksheets.
Again, remember those key elements of properly naming named ranges and using the ‘INDIRECT’ function. It takes a bit of practice to achieve a reasonable comfort level creating dependent drop-down lists, but it really is simple enough that anybody can do it.
This guide is written by Kasper Langmann from Spreadsheeto.com. If you like it, check out his free Excel training!
Leave a Reply