The Best Way to Create Drop-Down Lists in Excel That Are Easy to Maintain, Re-use and Put YOU in Full Control
BY JASON MORRELL
Grab the free 'Dropdown Lists in Excel'
1. Why is a drop-down list useful?
Using a drop down list in Excel serves a number of useful functions:
2. How to create a dropdown list in Excel
You can create a drop down list in 2 entirely different ways. The first way is to create a combo box using the form controls on the Developer tab. That method is not the focus of this post. The second way, discussed below, is to use the Data Validation feature.
Step 1: Create Your List
The first thing you need is a list. Many people dedicate a worksheet to any lists they use. This worksheet can be hidden to keep it from prying eyes, but you know it’s there so you can unhide it and maintain it in the future.
Let’s say you create a list of 5 items in cells A1 to A5.
An optional, but useful step is to name the range.
1. Select cells A1:A5.
2. Click in the name box (to the left of the formula bar).
3. Overtype the current cell reference (A1) with a name, e.g. Locations.
If you ever want to name a cell or cell range using 2 or more words, just separate them with an underscore (_), e.g. Customer_Locations or butt the words up against each other, e.g. CustomerLocations.
One handy benefit of naming cells is that you can use the name in formulas wherever you are in the spreadsheet, so you don’t have to navigate backwards and forwards to find the cells that contain the list.
Step 2: Add a drop down list to cell(s)
Next you need to add the drop down lists to your cells.
1. Select the cell(s) where the drop down list will appear.
2. Click the Data tab.
3. Click the Data Validation icon (i.e. not the drop-down component) to display the Data Validation dialog box.
4. In the Settings tab, in the Allow box, choose List.
5. Position the cursor in the Source box.
6. Press F3 to display a list of all named cells or cell ranges.
7. Choose your named range from the list. The Source box will now say: =YourListName.
8. Click the Input Message tab.
9. Untick the box labelled ‘Show input message when cell is selected’ as most people already know what to do with a drop down arrow and a drop down list.
10. If you want to ensure that only an item from your list is selected, click the Error Alert tab and complete the title and message information to display a more user-friendly message than the default that Microsoft give you.
11. [Optional step] Change the Style option from Stop (fix the error or cancel) to Warning (Whoops! Can’t find that entry – wanna keep it – Yes or No) or Information (Whoops! I don’t recognise that – OK or cancel).
11. Close the dialog box.
Step 3: Test it
The setup is now complete, so let’s see how the drop down list looks.
1. Click any cell that has the data validation. A drop down arrow will appear.
2. Click the drop down arrow and the drop down list is displayed.
3. Select the item from the list.
4. Type in one of the entries, correctly. It is accepted, no problems.
5. Mis-type an entry. The error message will be displayed.
3. Re-using the same drop down lists in other cells
What if you need to apply the same validation to other cells?
1. Select the last cell that has validation on it.
2. Use the autofill handle (the block on the bottom-right corner of the cell) to copy the validation downwards, thereby extending the range of cells using the drop down list.
3. You can also Copy and Paste Special the Validation directly to other cells.
As you can see, it’s pretty neat.
4. Maintaining your list
At some point you’ll probably want to extend the list, remove some items or change some of the entries on the list. Let’s re-visit the list.
Any entries that are edited are effective immediately, so all drop down lists will now use the new values.
Let’s add a couple of new items to the list.
To check that the new items are included:
1. Click the Formulas tab.
2. Select the Name Manager icon to display the Name Manager dialog.
3. Select your list name and check that the cell range is correct. If it isn’t you can very carefully edit the current range in the Refers To box or reselect the new larger range directly on the sheet.
4. Close the dialog box.
5. Applying validation to existing data
One last thing. If you have been handed a spreadsheet, you can apply validation to existing data. The data may be good, bad or mixed. There’s a great tool to highlight the bad data so it can be rectified.
1. Set up the drop down lists using the data validation tool as described above.
2. Click the drop-down arrow on the Data Validation tool.
3. Select Circle Invalid Data. All entries that don’t match one of the items on your list will be circled. Can’t miss it!
6. Watch the video (over the shoulder demo)
7. What next?
So there you have it.
If you don’t currently use drop down lists, try creating one (or several) today. You can literally be up and running within 60 seconds.
If this post helped, or you have a question, drop a quick comment below. I always love to hear from my readers. Here's to your learning and success. Enjoy the rest of your day.