Add or remove items from a drop-down list in Excel 2016 for Mac

After you create a drop-down list, you might want to add more items or delete items. How you do that depends on how the list was created. When you create a drop-down list, you connect it to a list of entries, which are used to populate your drop-down list. When you hook up those entries to your drop-down list, you can point to either a name that you’ve given the list (called a named range), or to the cells that contain your list of entries (called a range of cells).

Edit a drop-down list that’s based on a named range

  1. Open the worksheet that has the named range for your drop-down list.

    Tip: To identify a named range, select the range and look for its name in the Name Box.

    Name box showing a named range

  2. Do any of the following:

    • To add an item, go to the end of the list and type the new item.

    • To remove an item, hold down CONTROL, and then click the cell. Then click Delete, and then OK to shift the cells up.

  3. On the Formulas tab, click Define Name.

    Click the Formulas tab, and then click Define Name

  4. In the Names in workbook box, click the named range you want to update.

  5. To the right of the Select the range of cells box, click the Collapse Dialog Box button image button to collapse the dialog box, and then on your worksheet select all of the cells that contain the entries for your drop-down list.

    Collapse Dialog Box button in the Name box

  6. Click the Expand button button to expand the dialog box.

    Expand Dialog Box button in the Name box

  7. Click OK.

Edit a drop-down list that’s based on a range of cells

  1. Open the worksheet that has the data for your drop-down list.

  2. Do any of the following:

    • To add an item, go to the end of the list and type the new item.

    • To remove an item, hold down CONTROL, and then click the cell. Then click Delete, and then OK to shift the cells up.

  3. On the worksheet where you applied the drop-down list, select a cell that has the drop-down list.

  4. On the Data tab, click Data Validation.

    Data Validation on the Data tab

  5. On the Settings tab, to the right of the Source box, click the Collapse Dialog Box button image button to collapse the dialog box, and then on the worksheet that has the entries for your drop-down list select all of the cells containing those entries.

    Collapse Dialog Box button in the Data Validation box

  6. Click the Expand button button to expand the dialog box.

    Expand Dialog Box button in the Data Validation box

  7. To update all cells that have the same drop-down list applied, check the Apply these changes to all other cells with the same settings box.

Working with your drop-down list

After you update a drop-down list, make sure it works the way you want. For example, check to see if the cell is wide enough to show your updated entries.

If the list of entries for your drop-down list is on another worksheet and you want to prevent users from seeing it or making changes, consider hiding and protecting that worksheet. For more information about how to protect a worksheet, see Lock cells to protect them.

If you want to delete your drop-down list, see Remove a drop-down list.

Share Facebook Facebook Twitter Twitter Email Email

Was this information helpful?

Great! Any other feedback?

How can we improve it?

Thank you for your feedback!

×