DMW logo
contact@consultdmw.com

Dropdown Lists in Microsoft® Excel Cells

How to create and maintain dropdown lists in cells on an Excel spreadsheet to make data entry easy and to limit input to allowed or defined values.

On this page:

Last updated on 2022-12-01 by David Wallis.


Purpose of Dropdown Lists

A dropdown list applied to a cell or group of cells on an Excel spreadsheet serves purposes including:

Thoughtful deployment of drop-down lists contributes not only to spreadsheet accuracy but also to relief for users from possible concerns about exactly what to input into their spreadsheets.


An Approach to Dropdown Lists

There’s more than one method of creating a dropdown list in Excel; and all of these are well covered on the web.

In this article I explain the method that I’ve found most useful for creating dropdown lists in the majority of my own spreadsheets and in spreadsheets I’ve created for clients.

The two-stage approach:

  1. Create the list
  2. Create the dropdown, referencing that list.

With this approach, as I’ll explain below, the list items are readily accessible: they are not buried in the construction of the dropdown.


List Preparation

I recommend this way to prepare your list:

  1. Create a worksheet in your workbook for the purpose of storing lists. (I name this worksheet “Lists”)
  2. New Excel worksheet for lists

  3. In this worksheet create the list as you want it to appear in your dropdown, inputting items in cells down the same column. No empty cells
  4. Excel list of country codes

  5. Select the cells of the completed list. Then in Excel’s Name Box type a name for that list. Be certain to complete your typing by pressing Enter
  6. Excel range naming

    Such a name is what many Excel devotees refer to as a “range name”

    In the screen clip above I’ve given the list of country codes the range name “nmCountryCode”

  7. Test that your range name is properly set by selecting it from the list of names appearing in the dropdown list of Excel’s Name Box.

Now your list is ready for the dropdown to use. Remember its name for the next stage.


Creating the Dropdown

This is the way to create the dropdown:

  1. Select a cell to which you want the dropdown list to apply
  2. From Excel’s ribbon choose the Data tab
  3. From the Data Tools section on that tab, choose Data Validation
  4. Excel Data Tools

  5. Under Settings in the Data Validation dialog box, select “List” from the Allow dropdown list
  6. Excel Data Validation dialog box

  7. In the Source box, type an equals sign followed by the range name you’ve given to your list. (Take care not to omit the equals sign)
  8. Excel Data Validation dialog box

  9. Click OK to close the dialog box.

Your dropdown list should now be in place.


Applying the Dropdown List to Other Cells

Once you have your dropdown list applied to a cell, this is how to apply it to other cells:

  1. Select the cell containing the dropdown
  2. Issue the Copy command
  3. Select the cells to which you want to assign the dropdown list
  4. Issue the Paste Special command
  5. From the Paste Special dialog box, select the “Validation” option
  6. Excel paste special Validation

  7. Click OK.

Your chosen cells should now be afforded the dropdown list.


Names for Dropdown Lists

I recommend avoiding using a range name for your list that Excel uses in its own workings. This will spare users from confusion between, say, your “Date” name and Excel’s “DATE” function.

In naming a list I always include a prefix of two or three lowercase letters in the name, e.g. “nm” as in “nmCountryCode”

This naming convention has the advantage of distinguishing range names of mine from those, like “Print_Area”, that Excel may have applied for its own purposes.

For spreadsheets in which I’m not the only person applying names to ranges of cells, I use my initials, “dmw”, as the prefix to distinguish my efforts.


Adding to a List

There are two circumstances to take into account when adding an item to a dropdown list:

1 – Item to be inserted into the list

Simply insert a new cell at the point in which you want the new item to appear, and input the item.

Nothing further needs doing: all the dropdowns in cells on your spreadsheet will now include this new item.

2 – Item to be added to the bottom of the list

When you’ve input a new item to the bottom of your list, you need to extend the range name to include it:

  1. Select the cell item in the list
  2. Choose the Formulas tab on Excel’s ribbon and go to the Defined Names group
  3. Choose Name Manager to display the Name Manager dialog box
  4. Excel paste special Validation

  5. In that dialog box, select the Name in question
  6. Click in the Refers to box at the bottom of the dialog box
  7. Excel paste special Validation

    Excel will select the sells currently providing your list

  8. On the spreadsheet, select the list cells including the new one
  9. Click the
  10. Click Close
  11. Test that your range name is properly set by selecting it from the list of names appearing in the dropdown list of Excel’s Name Box.

Cautionary Note — Changing an Item in a List

If you change an item in your list — e.g. from “Streich-Waters” to “Streich-Waters LLP”— then that change will not update any of the cells in your spreadsheet containing the original list item.

This means that you’ll need to adjust entries in your spreadsheet before anyone uses it with the revised dropdown list.


Cautionary Note — Deleting an Item from a List

If you delete an item from your list, then that item will not disappear from any of the cells in your spreadsheet to which originally you applied it using the dropdown.

For this reason you’ll need to adjust entries in your spreadsheet before anyone uses it with the revised dropdown list.


Your Support for DMW TIPS

Please support this website by making a donation to help keep it free of advertising and to help towards cost of time spent adding new content.

To make a contribution by PayPal in GBP (£ sterling) —

To make a contribution by PayPal in USD ($ US) —

If you think of a quotation that relates this image and dropdown lists, please let me know.

contact@consultdmw.com