The Best Way To Create Drop-Down Lists In Excel That Are Easy To Use & Easy To Maintain

Drop down lists: Functional and Awesome

Feature Download: Grab a printable PDF of everything in this post (completely free). Never forget a key point again!

​Why is a drop-down list useful?

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

  • It improves the accuracy of data that is input.
  • It makes the process of data entry faster, especially when each entry is quite long.
  • It makes later 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

Using a drop-down list has many advantages

You can create a drop down list in 2 entirely different ways. The first way is to 

create a combo box or list 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 01: Type your list items

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

  1. Select cells A1:A5.

  2. Select the cell range containing your list

    Figure 02: Select the cell range containing your list

  3. Click in the name box (to the left of the formula bar).
  4. Click in the Name Box

    Figure 03: Click in the Name Box

  5. Overtype the current cell reference (A1) with a name, e.g. Locations.
  6. Click in the Name Box

    Figure 04: 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.
  2. Select the cell(s) that will have the drop-down lists

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

  3. Click the Data tab.
  4. Click the Data Validation icon (i.e. not the drop-down component) to display the Data Validation dialog box.
  5. Click the top half of the Data Validation iconClick the top half of the Data Validation icon

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

  6. In the Settings tab, in the Allow box, choose List.
  7. Position the cursor in the Source box.
  8. Press F3 to display a list of all named cells or cell ranges.
  9. Choose your named range from the list. The Source box will now say, =<YourListName>.
  10. The Settings tab

    Figure 07: The Settings tab

  11. Click the Input Message tab.
  12. 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.
  13. The Input Message tab

    Figure 08: The Input Message tab

  14. 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.
  15. The Error Alert tab

    Figure 09: The Error Alert tab

  16. [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).
  17. The 3 different Error Alert styles

    Figure 10: The 3 different Error Alert styles

  18. 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. Using a drop-down list has many advantages

    Figure 11: Select an item from the drop-down list

  5. Go to a different cell that has the same validation.
  6. Type in one of the entries, correctly. It is accepted, no problems.
  7. Go to a different cell that has the same validation.
  8. Mis-type an entry. The error message is displayed.

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.

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.

  1. To check that the new items are included:
  2. Click the Formulas tab.
  3. Select the Name Manager icon to display the Name Manager dialog.
  4. Name Manager dialog

    Figure 12: The Name Manager dialog

  5. 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.
  6. Close the dialog box.

Job done.

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!
  4. 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

Let’s wrap up this party!

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.

Please share your thoughts and feedback in the comments below, and if you found this post useful, please share a link on your social networks.

Cheers. Here's to your learning and success. Enjoy the rest of your day.

Jason Morrell blog signature

Jason Morrell photo and bio

Drop-down lists in Excel (cover)

FREE CHECKLIST

Download a printable PDF of this post

PLUS, GET A FREE COPY OF MY 30 EXCEL POWER TIPS

Leave a Reply

Be the First to Comment!

Notify of
avatar
wpDiscuz