Often, we need to set up a list box in Excel, a sorted list that allows us to choose an item from an Excel Table.
The figure below illustrates a simplified version of that requirement…
- Columns A-C contain a simple sales table.
- The area beginning in cell E2 contains a sorted, unique list of the products contained in the table.
- Cell F6 contains the list of items from which we can choose.
- Cell F8 contains a formula that returns the total sales for the specified product.
Let’s see how this is done…
Set Up the List of Items
The first step is to create a list of items to display in cell F6. To do so, I entered the list in the range E3:E4.
The label in cell E2 is the name of the list. Although you can name it anything you want, I typically begin the name of the list with “l.”. Using the prefix groups all the names of my lists together alphabetically, which makes them easier to manage.
Assuming you want to do the same, enter the name as shown; select the range E2:E4; choose Formulas, Create from Selection (or choose Ctrl+Shift+F3); in the Create Names dialog make sure that only Top row is checked; and then choose OK.
Set Up the Validation List
There are several ways to create a dropdown list box in Excel. As a general rule, using the Validation List method is the easiest and most flexible.
To do that, choose the cell where you want the list to reside. In the figure, I chose cell F6.
Choose Data, Data Tools, Data Validation to launch the Data Validation dialog. In the Settings tab, choose Allow List. In the Source list box select the named list or else enter the formula… =l.Product
Then press OK.
After you do so, your active cell should display the list-box control at its right. (I typically assign a yellow fill to cells like this, which alerts me to the cells that contains manual settings.)
To make the setting easier to use in formulas, you can assign the label in cell E6 as the range name for the yellow cell. To do so, select the range E6:F6; choose Formulas, Create from Selection (or choose Ctrl+Shift+F3); in the Create Names dialog make sure that only Left column is checked; and then choose OK.
Finally, you can use the specified product name in formulas. To illustrate, here’s the formula I set up to use the specified product:
F8: =SUMIFS(Table1[Sales],Table1[Product],Product)
Here, the SUMIFS function has this syntax:
=SUMIFS(sum_range, criteria_range, criteria)
You’ll find related content here:
