Drop Down Lists In Excel (and the best bits they don't tell you)
Office Mastery
Assign a 'primary' menu

The Best Way to Create Drop-Down Lists in Excel That Are Easy to Maintain, Re-use and Put YOU in Full Control

Drop down lists in Excel


Drop-down lists in Excel (cover)


Grab the free 'Dropdown Lists in Excel'
cheat sheet

1. Why is a drop-down list useful?

Using a drop down list in Excel serves a number of useful functions:

  • It improves the accuracy pf data that is input.
  • It speeds up the data entry process, especially when each entry is quite long.
  • It makes analysis more accurate, e.g. when counting the number of customers in London, if somebody has manually entered ‘Lindin’ that entry won’t be counted.
  • Maintenance is easier, as it’s done in one place.
  • Lists can be re-used in multiple locations so you don’t have to re-invent the wheel.
Using a drop-down list has many advantages

Figure 01: Using a drop-down list has many advantages

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.

Type your list items

Figure 02: Type your list items

An optional, but useful step is to name the range.

1.       Select cells A1:A5.

Select the cell range containing your list

2.      Click in the name box (to the left of the formula bar).

Click in the Name Box

3.      Overtype the current cell reference (A1) with a name, e.g. Locations.

Click in the Name Box

Figure 05: Click in the Name Box

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.

Select the cell(s) that will have the drop-down listsFigure 06: Select the cell(s) that will have the drop-down lists

Figure 06: Select the cell(s) that will have drop-down lists

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.

Click the top half of the Data Validation iconClick the top half of the Data Validation icon

Figure 07: Click the top half of the Data Validation icon

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.

The Settings tab

Figure 08: The Settings tab

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.

The Input Message tab

Figure 09: The Input Message tab

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.

The Error Alert tab

Figure 10: The Error Alert tab

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).

The 3 different Error Alert styles

Figure 11: The 3 different Error Alert styles

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.

Using a drop-down list has many advantages

Figure 12: Select an item from the drop-down 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.

Name Manager dialog

Figure 13: 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.

Job done.

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!

Invalid data can be circled in red so that you can quickly see what needs to be fixed

Figure 13: Invalid data can be circled in red so that you can quickly see what needs to be fixed

6. Watch the video (over the shoulder demo)

Click to watch video

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.

Jason Morrell blog signature
Jason Morrell, Office Mastery

About the author

Jason Morrell

Jason loves to simplify the hard stuff, cut the fluff and share what actually works. Things that make a difference. Things that slash hours from your daily work tasks. He runs a software training business in Queensland, Australia, lives on the Gold Coast with his wife and 4 kids and often talks about himself in the third person!