For business users of Microsoft Excel Learn to create Excel dashboards

Interactive Reporting

How to Set Up a Data-Validation List Box in Excel

The 'List' Feature of the Data Validation command offers a quick and easy way to set up a dropdown list box.


by Charley Kyd, MBA
Microsoft Excel MVP, 2005-2014
The Father of Spreadsheet Dashboard Reports

Example of Excel's validation list(Download the files here.)

Often, we want to set up a list box in Excel, a sorted list that allows us to choose an item from an Excel Table.

This figure 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:

And also, I created one download file for all three articles. You can download the files at this link.


Tags: #validation list, #dropdown list box, #list box, #sumifs function, #tables

 

How to Use Advanced Filters to Create a Unique List of Items from an Excel Table

Two Ways to Set Up Multi-Criteria Lookup Formulas in Excel
Free Excel Dashboards


Charley's SwipeFile charts