How to Use In-Cell Dropdown List in Excel
We frequently receive questions about creating dropdown lists in Excel worksheets. It’s no surprise—dropdown lists provide many benefits. They reduce data entry time by letting users pick from a predefined list. They also can eliminate inconsistent or erroneous data, because entries are standardized. What most people inquiring about dropdown lists don’t realize is that there are actually several options available to you in Excel. In this article, we’ll examine two features that attach dropdown lists directly to a worksheet’s cells.
Why type when you can pick?
No matter how good you are on the keyboard, you’re bound to enter typos now and then. The two tools we’ll look at reduce the chance for errors and can generally speed up entry tasks. The first feature we’ll discuss is the Pick From List feature, which is an extension of Excel’s AutoComplete feature. After examining AutoComplete to cover the basics, we’ll look at how to most efficiently use the Pick From List feature. Then, we’ll use the Data Validation feature to build dropdown lists based on existing worksheet entries and manually defined lists. Once they’re built, we’ll look at how to apply an existing validation dropdown list to other cells, change list items, ensure that existing entries match your list, and control whether users can enter items that aren’t on your validation list.
Speeding data entry with AutoComplete and Pick From List
The most commonly used Excel tool for reducing data entry time is undoubtedly AutoComplete. You hardly have to do any data entry before you stumble across this feature. As you enter text into a cell, Excel checks the preceding cells in the same column to see if any existing entries match what you’re entering. As soon as Excel detects a possible match, it fills the cell with the anticipated word. You can then exit the cell to accept the suggestion or keep typing to remove the suggested word and finish your entry. The Pick From List feature provides access to the same list of items AutoComplete uses, allowing you to select an item from a list box that’s anchored to your active cell.
Seeing AutoComplete in action
To demonstrate the AutoComplete feature’s basic functionality, open a new workbook and enter United States in cell A1. Then, enter U in cell A2. As soon as you press the U key, Excel displays United States in the cell, as shown in Figure A. Now, press the [Down Arrow] key to select cell A3 and enter United Kingdom, watching the text onscreen as you do so. Until you press the K key, Excel assumes that United States is the entry you want to make. As soon as you press K, Excel clears the suggested entry and you can finish typing Kingdom.
AutoComplete anticipates the entry you want to make based on previously existing data in the adjacent column cells.
At this point, select cell A4 and enter United States again. Excel can’t differentiate between the prior en-tries until you press the S key, at which point it makes an assumption about your current entry. In a case like this, waiting to get to the character that differentiates similar entries can be tiresome. Fortunately, you can speed the process using the Pick From List feature.
Eliminating the wait
To use the Pick From List feature, simply press [Alt] and the [Down Arrow] key when you begin your new entry ([option][down arrow] in Excel 2001). For example, select cell A5 and press [Alt][Down Arrow] (or [option][down arrow]). Excel displays a dropdown list of the column’s previous entries, as shown in Figure B. Use your mouse or keyboard’s directional arrows to select an item and press [Enter] to insert it into the cell. Likewise, you can display the AutoComplete item list by right-clicking on a cell ([control]-clicking on a Mac) and choosing Pick From List.
AutoComplete fills in cells as you type, but you can also display the list of items it provides.
Using partial matches to streamline scrolling
When using the Pick From List feature with a long list of column entries, enter the first few letters of the word you’re looking for prior to displaying the dropdown list so you don’t have to scroll through a lot of entries. To demonstrate, enter Canada, France, Uganda, Ukraine, and Zambia below the entries in the current column. Now, let’s say that you want to enter United States again. In the column’s next available cell, enter Un and press [Alt][Down Arrow]. The dropdown list opens and the first entry matching the criteria is selected, as shown in Figure C. Although you need to manually select the United States item, this technique is much faster than scrolling through the entire list or typing the name until Excel recognizes it.
Entering characters that partially match list items before you display the dropdown list lets you quickly access the first matching word entry.
There may be times when you find AutoComplete to be more of a hindrance than a help. To disable the feature, choose Tools | Options from the menu bar. Then, click on the Edit tab, clear the Enable AutoComplete For Cell Values check box, and click OK. Note that changing this setting affects all workbooks, not just the active one. It’s also worth pointing out that disabling AutoComplete doesn’t impact the Pick From List feature.
Simplicity has its shortcomings
One of the main advantages of the AutoComplete and Pick From List features is that very little work is required to create the lookup list—Excel builds it for you as a matter of course as you perform your data entry. Unfortunately, there are several drawbacks to consider. For instance, because the list is based on current entries, the Pick From List feature isn’t really useful until your column contains at least one occur-rence of each of the items that are most commonly used. In addition, you aren’t restricted to using the items in the lookup list. If you enter a typo or slightly different version of a list item, your new entry is simply accepted—and added to the list of available items. Finally, the Pick From List feature is only helpful if information is entered in consecutive column cells. Any gaps in the column reset the Au-toComplete list and you can’t apply the list available in one cell to a cell in some other column. Fortunately, all of these shortcomings can be addressed with the next technique we’ll examine.
Creating dropdown lists with Data Validation
Excel’s Data Validation feature also lets you display a dropdown list in certain cells. Although using the feature requires more setup work, there are many benefits to using it. The list items are predefined, so the complete value list is available at all times. In addition, you can configure the feature to only allow the defined entries in cells, eliminating typos or in-appropriate data. Once you’ve configured a cell’s va-lidation settings, they can be applied to any other worksheet cells, making it easy to reuse the dropdown list in any cells you want.
Building validation lists
There are two ways to populate a data validation list. You can manually enter the items or retrieve them from a worksheet range. A manual list is best used when your list will contain few items and likely re-main static. Otherwise, basing the list on a worksheet range is the approach you’ll want to use, as it allows you to easily make changes to list items. We’ll look at both techniques, as well as how to work with the validation settings to produce a dropdown list that best meets your particular needs.
To demonstrate working with validation lists, we’ll use the worksheet shown in Figure D. We’ll start by creating a validation list based on the names appearing under the Project Team label. Set up the basic worksheet, and then select range B10:B15. Un-like the AutoComplete list, which adapts as you make new entries, a data validation list is tied to specific cells.
We’ll use the names listed under the Project Team label to populate dropdown lists in several cells.
To apply list validation rules to range B10:B15, se-lect Data | Validation from the menu bar. Next, select List from the Allow dropdown list. Place your insertion point in the Source text box, and then select range A2:A5 with your mouse. Note that we skipped the cell containing the column label—if we had se-lected it, Project Team would also appear as an item in the validation list. Finally, click OK.
Now, we’ll create a validation list based upon a list of items you manually enter. Start by selecting range C10:C16. Then, choose Data | Validation from the menu bar and again select List from the Allow drop-down list. To manually create a validation list, enter the items in the Source text box, using commas to separate the items. For our example, enter the fol-lowing in the Source text box:
as shown in Figure E. Finally, click OK.
You can also manually enter items to create a dropdown list.