Excel Drop Down Lists

Table of Contents

There are three different types of Excel drop down lists available:

  1. “Pick from” lists which are generated automatically.
  2. Data Validation lists which are very easy to do.
  3. Combo box and List box controls which require a bit more work.

Pick from lists

As you type a list of data into a column in your worksheet you are automatically prompted with previously entered list elements that match your typed characters. In the illustration, I have typed in an “f” and the cell is filled-in with “fred” as this entry is already in the list.
 
To accept the prompted text, press ENTER or press the Down Arrow key. To decline the suggestion, keep typing.
To generate the list entries without your having to do any initial typing, you can use the shortcut key combination ALT + Down Arrow to display the list elements and then, if you’re a keyboard fan, press the Down Arrow key again to go down through the list and then ENTER to accept the entry.
 
If you can’t be bothered with all this good old-fashioned shortcut keys business then just right-click in the cell and choose Pick from Drop-down list in the shortcut menu.
 

Data Validation lists

These lists are used where you want to restrict the entry made in a cell to only those items available in a drop-down list. The list of valid entries can be typed in or you can refer to a range of cells which contains your valid entries.  The range of valid entries can be entered into a different worksheet in the same workbook.

Data Validation lists are ideal not only where you need to make sure that people make the right entry but also for data entry generally as it is so easy to choose from a drop-down list. To create the validation list, select the cell or range of cells where you want the list to be displayed and then click the Data Validation control on the Data tab (look for Data Validation in the Data menu if you are using an older version of Excel)

In the Settings tab, choose List from the Allow drop-down list and then type in a comma-separated list of your valid entries into the Source box. In this case, the list items are Yes, No and Maybe. Click the OK button and you’re done. The list is available when you click on one of the cells where the validation has been applied and data entry into the cell is restricted to only those items in the list. 

Click the Error Alert tab if you want to change the standard “the value you entered is not valid” error message for invalid entries. You may prefer something a little more personal. 

Using a range of cells to provide the source

A range of cells is preferable for your data validation where you either have a long list or you want to be able to change the list regularly.

You can set the Source data validation as either a horizontal or vertical range of cells or an entire column or row. Either type in the range reference or (far easier) select the range or click the column letter or row number.

When you click the column letter remember that any heading at the top of your list on the worksheet will be included as the first item in your validation list. But if you add to the list on the worksheet then these additional items will automatically appear in the validation list. So there are advantages and disadvantages to selecting the whole column.

If you specify a range of cells then then you don’t have the headings issue to deal with but if you then add to the list on the worksheet then you will also have to remember to reset the Source data range to include your additions.

Displaying the drop-down list in alphabetical order

The drop-down list is linked to the values in the worksheet cells, so sort the list in the cells in alphabetical order if you wish to show the drop-down list in the same order. But you can’t have the drop-down list in a different order.

Dependant Data Validation lists

Sometimes you need the choice that you have made in one list to control the list shown in another cell; one of your validation lists is dependant on the other.

In the illustration there are two columns, the first column shows the class of date required, “Month” or “Day”. This is entered from a simple comma separated validation list which is typed in. The second column needs to show either a list of months if “Month” is entered in the corresponding cell in the first column or a list of days if “Day” is entered.

There are two ranges of cells in the worksheet named as Month and Day respectively which are going to be the alternative source ranges for the second column (see the next section if you are not sure how to Name a range) When you set the source data range for the second column enter the following formula: =INDIRECT(A2)

Where A2 is the first cell in the first column where you have your “Day” or “Month” entry. Of course, you should change this cell reference to suit your own worksheet. The INDIRECT function is well named as it makes the reference indirect, i.e. not A2 but the range reference entered in A2. In other words, if it’s “Day” then show the Day list, if it’s “Month” then show the Month list. You’re not restricted to just having a choice between two alternative lists, with this method you can have the choice of as many different lists as you like.

If this does not seem to work for you then try checking the following points:

  1. Did you select the area for the dependant range before applying the Data Validation? In the example above it would have been the second column. It’s very easy to apply validation to a single cell when you intended to set it for a range.
  2. Check the spelling of the range names (in this case “Day” and “Month”) in the first list. They must be spelled correctly and you have to remember them as you type them in as comma separated values.
  3. Did you remember to name the ranges?

Naming a Range

If you’re not sure how to name a range of cells then I would recommend using the Name Box, which is that area on the extreme left-hand side of the formula bar where you normally see your active cell reference. It’s not the only way of naming a range but most people find it to be the easiest way.

Type in the name (in this example it is “Months”) and then press the ENTER key to register the name correctly. Please don’t click out of the box after typing the name, press ENTER. Sorry for being boring but that’s where it’s so easy to go wrong. Click the drop-down arrow to check that the name is available.

Combo box and List box controls

These are interactive graphics which are drawn on top of the worksheet cells and are then linked to certain key worksheet cells. They are commonly employed as a user-friendly graphical device to make user-unfriendly processes involving formulas much easier to control. 

In the above example, there is a combo box control containing a list of months. When a month is selected from the list the heading in the worksheet report is updated (“Sales Report for …”) and the relevant monthly numbers are extracted from another worksheet where the full twelve month’s Forecast Sales figures are entered.

All this is done by using Excel formulas but you don’t need to know anything about the formulas to use the worksheet. You just choose a month from the drop down list. Combo box and List box controls are quite easy to do, the hard bit is doing all the formulas.

Form controls

There are two sets of graphical controls available in Excel, Form controls and ActiveX controls. They both do much the same job but in rather different ways so it doesn’t matter which set you use.
 
Form controls were designed and built for Excel, ActiveX controls are more general controls used in many different applications. Form controls are simple and robust whereas ActiveX controls are more sophisticated and the process of using them takes far longer. It’s a judgement call. I shall be using Form controls in this example and deal with ActiveX in a future article.

Finding the Form controls

This is the first job. Form controls are not openly available, you need to display the Developer tab on your Excel ribbon. Click the File tab and then click Options. Click Customize Ribbon and then click the Developer check box in the Main Tabs list on the right-hand side. Click OK. Form controls are in the Insert control on the Controls group of the Developer tab.

Click the Insert control, the Form Controls are the top group. The Combo Box (1) control is the second one in from the left. The List box (2) control is second in from the right. Both controls behave the same; the Combo box is a drop-down list, the List box is an open list.

Creating the Combo Box

You need to go through a few steps to get the Combo box working. Firstly, create some cell ranges where you are going to store the data that you want to have displayed in the drop-down list and a cell to link the Combo box to. When you format your Combo box these ranges are going to be your Input range and your Cell link.

Personally, I would always put these data ranges on a separate worksheet so that I can then hide it. In the example I shall be using Named Ranges, as they are so easy to work with, rather than normal cell references but this is just personal preference. Use Named Ranges or the standard A1-style cell references as you prefer, they both work fine.

In the illustration, I have a list of my twelve months (shortened for clarity) these are the grey cells and are named MonthList. The pink cell is going to be the cell link and is named MonthPick and the green cell is MonthName, where I am going to enter a formula which will calculate the current month. Now it’s time to create the Combo box, there’s three steps:

  1. Draw and format the Combo box
  2. Set the Input Range and Cell Link
  3. Test the Combo box and the enter the formulas
  1. Draw the Combo box. Click the Combo box control and then drag to draw a horizontal rectangular shape. You can resize it later if it’s not quite right. Right-click the control to display its shortcut menu. Try to right-click when your mouse pointer is pointing to the middle of the control as there are different shortcut menus available, you want the one that contains Format Control.
  2. Format the Combo box. In the Format Control dialog enter the Input Range (in this example MonthList) and the Cell link (MonthPick) Click OK.
  3. Test the Combo box. Click a worksheet cell to take the focus away from the Combo box. It’s now active. Click the drop-down arrow and choose an item from the list. You should see that a value is returned into your link cell. It’s an index value, it’s not the text from the list. If you choose “March” from the list then the index is the value of 3 because it’s the third item in the list.

That’s the Combo box control completed and now it’s time to enter our formulas in the worksheet to make everything happen.

Entering the formulas

When you choose a month from the list you want to trigger the following processes in your formulas:

  1. Update the month name definition. This formula goes in the cell named MonthName so that we can use the name in our formulas wherever we need the selected month declared.
  2. Update the report heading with the name of the month.
  3. Update the report by showing the relevant data for the selected month.

1. Formula in the MonthName cell

Use the index value MonthPick to return the relevant item from the MonthList list using Excel’s INDEX function. The formula is:

=INDEX(MonthList,MonthPick)

2. Update the report heading with the name of the month

The named range MonthName updates to show the currently selected month so we concatenate it’s value with the static text “Sales Report for …”. This formula is entered on the worksheet where we have our report. The formula is:

=”Sales Report for “&MonthName

3. Update the Sales Forecast figures to show the currently selected month

There’s various ways of doing this but the most obvious way is to use the index value MonthPick in a Lookup formula to return the current month’s figures from the range containing all the year’s Sales Forecast figures.

In the illustration above, the grey cells are named SalesForecast. This range is on a different worksheet but that’s not a problem when we use a named range. When the selected month from the Combo box is “March” then the value of MonthPick is 3. The corresponding “March” figures are in the 4th column of the SalesForecast range so if we add the value of 1 to MonthPick then we calculate the correct column index value for our lookup formula. The formula is:
 
=VLOOKUP(B5, SalesForecast, MonthPick+1, FALSE)
 
Where B5 is the cell reference of the first Product in the main report (it contains the text “Product 1”), SalesForecast is the lookup range, MonthPick+1 the column index value and FALSE because it is an Exact Match VLOOKUP based on matching the product name in the main report to the products listed in the first column of the lookup range.

Related Courses

Microsoft Excel Intermediate – Link

Microsoft Excel Advanced – Link