Drop down lists can greatly facilitate data entry. Here’s a look at how to use Microsoft Excel’s data validation feature to create handy lists within your worksheets.
In web forms, surveys, and polls, it can be very useful to limit the choices for a selection with a simple drop down list. This is also possible in a Microsoft Excel spreadsheet, but the process isn’t very well known or very intuitive.
In Access, you can limit user entries by forcing users to choose a value from a list control. Microsoft Office applications use the same functionality in built-in drop down lists. For instance, the Highlight and Font Color controls on most Formatting toolbars use this flexible tool. Simply click the small triangle to the right of the icon to display a list of choices.
You can create the same type of control for your users in an Excel sheet, but the process isn’t intuitive. The option is in the Data Validation feature. Fortunately, once you know the feature exists, it’s easy to implement. You need only two things: A list and a data entry cell. Figure A shows a simple drop down list in an Excel sheet. You can work with your own data or download the demonstration .xlsx and .xls files.
Figure A
Users click the drop down arrow to display a list of items from A1:A4. If a user tries to enter something that isn’t in the list, Excel rejects the entry. To add this drop down list to an Excel sheet, do the following:
- Create the list in cells A1:A4. Similarly, you can enter the items in a single row, such as A1:D1.
- Select cell E4. (You can position the drop down list in most any cell or even multiple cells.)
- Choose Validation from the Data menu.
- Choose List from the Allow option’s drop down list. (See, they’re everywhere.)
- Click the Source control and drag to highlight the cells A1:A4. Alternately, simply enter the reference (=$A$1:$A$4).
- Make sure the In-Cell Dropdown option is checked. If you uncheck this option, Excel still forces users to enter only list values (A1:A4), but it won’t present a drop down list.
- Click OK.
SEE: How to create a drop-down list in Google Sheets (TechRepublic)
You can add the drop down list to multiple Excel cells. Select the range of data input cells (step 2) instead of a single Excel cell. It even works for noncontiguous Excel cells. Hold down the Shift key while you click the appropriate Excel cells.
A few quick notes:
- You can only see the drop down if you click on the Excel cell.
- Your users can now only choose one of the options in the drop down. If they try to enter their own data, then they’ll receive an error message.
- You can copy-and-paste this drop down cell to any other Excel cells in your spreadsheet, and you can create as many different drop downs like this as you’d like.
SEE: 10 Excel time-savers you might not know about (free PDF) (TechRepublic)
How to create an Excel drop down list from another tab
Table of Contents
The data you want to use in an Excel drop down list usually won’t be in the same sheet as the drop down. The good news is that identifying a list on another sheet requires only an extra click to access the sheet. We can illustrate this as follows.
- If you have only one sheet, add a new one. (“Different sheet” in the demonstration file.)
- Select E4 in the new sheet and repeat the earlier instructions for creating a drop down through step 4.
- When you get to the step 5, where you identify the source, click inside the Source Control.
- Click the Lists sheet tab or the tab that contains your list items.
- Select the list (A1:A4).
- Click OK, which will return you to the new sheet, where you’ll find a populated list in E4 (Figure B).
Figure B
With an extra click to identify the sheet, you can easily display list items on another sheet.
How to add color to a drop down list in Excel
Color can be a powerful element in an Excel drop down list, and it’s easier to add than you might think–you simply add conditional formatting rules to the cell containing the drop down list. Learn how to add a new list and validation control, and then add the conditional format rules to include visual cues.
To begin, add a new sheet and then add a new list with the text items red, blue, green, and yellow (Figure C). Using the instructions above, create a drop down list in E4 using the color list as the source. With the new list in place, you’re ready to add the conditional formatting.
Figure C
Add a new list of colors in Excel.
We’re going to use a pre-defined rule. To add a conditional rule that changes the font color to red when the selected list value is red, follow these steps.
- With E4 (in the new sheet with the color list) selected, click Conditional Formatting in the Styles group (Home tab).
- Choose New Rule from the submenu.
- In the top pane, select the Format Only Cells That Contain option.
- In the lower section, change the first drop down setting (Cell Value) to Specific Text.
- Change the second drop down to containing.
- In the third control, enter =A1, the cell that contains the text value red.
- Click the Format button, click the Font tab, choose red, and click OK. Figure D shows the formula and the format.
- Click OK again. If you select red, the font color is red, as shown in Figure E.
Figure D
Specify the conditions and format.
Figure E
The conditional formatting rule, uses red font when you choose red from the list.
When selecting the other three list items, the font is still black. Create a new rule for the other three using the instructions above while using the settings in Table A. You could also use the fill property to color the cell instead of the font.
Table A
|
Blue |
Specific Text |
contains |
=A2 |
|
Green |
Specific Text |
contains |
=A3 |
|
Yellow |
Specific Text |
contains |
=A4 |
Adding color is a helpful visual tool, but sometimes requirements change. For example, you might want to use one validation to limit another; in other words, you want to set a condition to filter or limit the list. We’ll cover this is the next section.
How to add a condition to a drop down list in Excel
So far, we’ve used an Excel drop down list to display a simple list; sometimes, you need a bit more control. Let’s suppose you have personnel scattered across four regions: North, south, east, and west. You want to work with personnel by region, not all personnel. A situation like that requires two drop downs: One that lets you specify the region, and the second that displays only the personnel in that region.
Note: This technique works only with a matrix-style data set; I’m showing the simplest technique, but it’s not the only technique. If you google, you’ll find complex and sometimes even convoluted solutions. Once we’re done, you’ll see that this solution isn’t perfect, but it is super easy to implement. If it doesn’t work for you, try embedding drop down controls instead.
Figure F shows a simple matrix-type data set listing representatives in each of the four regions. (Notice that the region list is in alphabetical order from left to right—it’s the only true limitation on this technique.) Representatives are listed for each region, and some of those representatives are listed more than once–I did that on purpose because it makes no difference to the technique.
Figure F
We’ll create two Excel drop down lists based on this simple data set.
We need two Excel drop down lists. The first Excel drop down list will set the condition, the region. The second Excel drop down list will display the representatives in that region. To the right, you see labels and formats in preparation for creating the Excel drop down list.
To create the 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, choose List.
- In the Source control, enter (or select) $B$2:$E$2 (Figure G).
- Click OK.
Figure G
The drop down displays the regions from row 2.
With the conditional drop down in place, it’s time to create the dynamic drop down that displays only the representatives from the region selected by the conditional drop down. For instance, if you select South in the conditional drop down, the second list will display Mary and Mike. Now, let’s create the second drop down:
- Select H3 and repeat the above steps through step 3.
- In the Source control, enter =Region $H$2 (Figure H). The formula references the conditional drop down in H2.
- Click OK.
Figure H
This drop down uses the selected item in the conditional drop down to determine its list.
Each time you change the region in the conditional drop down, the rep drop down updates accordingly. Remember that conditional list of header text must be in alphabetical order. In addition, you’ll notice the drop down in Figure H has space for four items because the list contains blanks. If you deleted Mary, the first line would be blank, the second line would display Mike, and the third and fourth lines would still be blank. This is one of those things you have to live with when you opt for an easy solution. For most of us, the two drop downs work well enough as is.
A Microsoft Excel bonus tip
This bonus Excel tip is also available in the free PDF 30 things you should never do in Microsoft Office.
Rely on multiple links
Links between two Excel workbooks are common and useful. But multiple links where values in workbook1 depend on values in workbook2, which links to workbook3, and so on, are hard to manage and unstable. Users forget to close files, and sometimes they even move them. If you’re the only person working with those linked Excel workbooks, you might not run into trouble, but if other users are reviewing and modifying them, you’re asking for trouble. If you truly need that much linking, you might consider a new design.
For more Excel tips, read 56 Excel tips every user should master.
Microsoft Weekly Newsletter
Be your company’s Microsoft insider by reading these Windows and Office tips, tricks, and cheat sheets.
Delivered Mondays and Wednesdays