You can use a Microsoft Excel drop down list to display a simple list, though sometimes you need a bit more control. Let’s suppose you have personnel scattered across four regions: North, south, east, and west. You want to work with personnel by region, not all personnel. A situation like that requires two drop downs: One that lets you specify the region, and the second that displays only the staff in that region. You can work with your own data or download the demonstration .xlsx and .xls files.
Note: This technique works only with a matrix-style data set. I’m showing the simplest technique, but it’s not the only technique. If you Google, you’ll find complex and sometimes even convoluted solutions. Once we’re done, you’ll see that this solution isn’t perfect, but it is super easy to implement. If it doesn’t work for you, try embedding drop down controls instead.
Figure A shows a simple matrix-type data set listing representatives in each of the four regions. (Notice that the region list is in alphabetical order from left to right—it’s the only true limitation on this technique.) Representatives are listed for each region, and some of those representatives are listed more than once—I did that on purpose because it makes no difference to the technique.
We need two Excel drop down lists. The first Excel drop down list will set the condition, the region. The second Excel drop down list will display the representatives in that region. To the right, you see labels and formats in preparation for creating the Excel drop down list.
To create the Region list, do the following:
- Select H2.
- Click the Data tab and then click Data Validation in the Data Tools group.
- From the Allow drop down, choose List.
- In the Source control, enter (or select) $B$2:$E$2 (Figure B).
- Click OK.
With the conditional drop down in place, it’s time to create the dynamic drop down that displays only the representatives from the region selected by the conditional drop down. For instance, if you select South in the conditional drop down, the second list will display Mary and Mike. Now, let’s create the second drop down:
- Select H3 and repeat the above steps through step 3.
- In the Source control, enter =Region $H$2 (Figure C). The formula references the conditional drop down in H2.
- Click OK.
Each time you change the region in the conditional drop down, the rep drop down updates accordingly. Remember that conditional list of header text must be in alphabetical order. In addition, you’ll notice the drop down in Figure C has space for four items because the list contains blanks. If you deleted Mary, the first line would be blank, the second line would display Mike, and the third and fourth lines would still be blank. This is one of those things you have to live with when you opt for an easy solution. For most of us, the two drop downs work well enough as is.
Get more Excel tips
Read 56 Excel tips every user should master and the tutorials how to create an Excel drop down list from another tab, how to add color to a drop down list in Excel, how to change an Excel conditional format on the fly, and how to combine Excel’s VLOOKUP() function with a combo box for enhanced searching. Also, check out these free PDF download compilations Build your Excel skills with these 10 power tips and 13 handy Excel data entry shortcuts.