Copyright infringement

How to add a condition to a drop down list in Excel

Microsoft Excel users, here’s a quick tip on adding a condition to a dropdown list.

Table

Photo: Imam Al-Fathoni, Getty Images / iStockPhoto

You can use the Microsoft Excel drop-down list to display a simple list, although sometimes you need a little more control. Suppose you have a character spread across four regions: North, South, East, and West. You want to work with individuals by region, not all individuals. Such a situation requires two drop-down lists: one that allows you to select a region, and the second that only displays employees in that region. You can work with your own data or download the .xlsx and . xls.

Noticeable: This technique only works with an array-style dataset. I introduce the simplest technique, but it is not the only one. If you use Google, you will find complex and sometimes complex solutions. Once we’re done, you’ll see that this solution isn’t perfect, but it is is being Very easy to implement. If it doesn’t work for you, try including the dropdown controls instead.

Figure A Displays a simple matrix-type dataset that lists the actors in each of the four regions. (Note that the region list is arranged alphabetically from left to right – it’s the only real limitation on this technique.) The actors are listed for each region, and some of these actors are listed more than once – I did it on purpose because there is no difference in the technique.

Figure A

microsoftexceldropdownlist-figure-f.jpg

We will create two dropdown lists for Excel based on this simple data set.

We need two dropdown lists in Excel. Excel’s first dropdown menu will select the condition, Region. The second drop-down menu for Excel will display the actors in that region. To the right, you’ll see labels and formats in preparation for creating the Excel dropdown list.

To create a 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 list, choose List.
  • In source control, enter (or select) $B$2: $E$2 (Figure B).
  • Click OK.

Figure B

microsoftexceldropdownlist-figure-g.jpg

The dropdown list displays the regions from row 2.

With the conditional dropdown in place, it’s time to create a dynamic dropdown that only displays the actors from the region specified by the conditional dropdown. For example, if you select South in the conditional dropdown, the second menu will display Mary and Mike. Now, let’s create the second dropdown:

  • Select H3 and repeat the above steps through step 3.
  • In the source control, enter = region H$2 (Figure C). The formula refers to the conditional dropdown in H2.
  • Click OK.

Figure C

microsoftexceldropdownlist-figure-h.jpg

This dropdown list uses the selected item in the conditional dropdown list to define its list.

Every time you change the region in the conditional dropdown, the dropdown delegate is updated accordingly. Remember that the conditional list of the title text must be in alphabetical order. Additionally, you will notice the dropdown menu in Figure C It has room for four items because the list contains spaces. If you delete Mary, the first line will be blank, the second line will display Mike, and the third and fourth lines will remain blank. This is one of those things that you have to live with when you choose an easy solution. For most of us, the two dropdown menus work just as well.

Get more tips about Excel

Read 56 Excel tips every user should master and tutorials on how to create an Excel dropdown list from another tab, how to add color to a dropdown list in Excel, how to change Excel conditional formatting on the fly, and how to combine the VLOOKUP() function in Excel with an edit box Listed to improve search. Also check out these free PDF download kits, Build your Excel skills with these 10 powerful tips and 13 handy Excel data entry shortcuts.