magnify
Home Software Microsoft Office Microsoft Excel Insert or Delete a Drop-Down list in Excel

Insert or Delete a Drop-Down list in Excel

 Inserting a Drop-Down List

To make data entry easier in Excel, or to limit entries to certain items that you define, you can create a drop-down list of valid entries that is compiled from cells elsewhere in the workbook. When you create a drop-down list for a cell, it displays an arrow in that cell. To enter information in that cell, click the arrow, and then click the entry that you want.

To create a drop-down list from a range of cells, use the Data Validation command in the Data Tools group on the Data tab.

  1. To create a list of valid entries for the drop-down list, type the entries in a single column or row without blank cells. For example:
  A
1 Sales
2 Finance
3 R&D
4 IT
  1. If you want to use another worksheet, type the list on that worksheet, and then define a name (name: A word or string of characters that represents a cell, range of cells, formula, or constant value. Use easy-to-understand names, such as Products, to refer to hard to understand ranges, such as Sales!C20:C30.) for the list.
    • How to Define a Name
      1. Select the cell, range of cells, or nonadjacent selections (nonadjacent selection: A selection of two or more cells or ranges that don’t touch each other. When plotting nonadjacent selections in a chart, make sure that the combined selections form a rectangular shape.) that you want to name.
      2. Click the Name box at the left end of the formula bar (formula bar: A bar at the top of the Excel window that you use to enter or edit values or formulas in cells or charts. Displays the constant value or formula stored in the active cell.).
        Insert or Delete a Drop-Down List in Excel 1
      3. Type the name for the cells, for example, ValidDepts.
      4. Press ENTER.
  2. Select the cell where you want the drop-down list.
  3. On the Data tab, in the Data Tools group, click Data Validation.
    Insert or Delete a Drop-Down List in Excel 2
  4. In the Data Validation dialog box, click the Settings tab.
  5. In the Allow box, click List.
    Insert or Delete a Drop-Down List in Excel 3
  6. To specify the location of the list of valid entries, do one of the following:
    • If the list is in the current worksheet, enter a reference to your list in the Source box.
    • If the list is on a different worksheet, enter the name that you defined for your list in the Source box.
    • In both cases, make sure that the reference or name is preceded with an equal sign (=). For example, enter =ValidDepts.
  7. Make sure that the In-cell dropdown check box is selected.
  8. To specify whether the cell can be left blank, select or clear the Ignore blank check box.
  9. Optionally, display an input message when the cell is clicked.
    1. Click the Input Message tab.
      Insert or Delete a Drop-Down List in Excel 4
    2. Make sure that the Show input message when cell is selected check box is selected.
    3. Type the title and text for the message (up to 225 characters).
  10. Specify how you want Microsoft Office Excel to respond when invalid data is entered.
    1. Click the Error Alert tab, and make sure that the Show error alert after invalid data is entered check box is selected.
      Insert or Delete a Drop-Down List in Excel 5
    2. Select one of the following options for the Style box:
      • To display an information message that does not prevent entry of invalid data, click Information.
      • To display a warning message that does not prevent entry of invalid data, click Warning.
      • To prevent entry of invalid data, click Stop.
    3. Type the title and text for the message (up to 225 characters).
  11. Click OK.

Note:  You may want to sort the data in the order that you want it to appear in the drop-down list.

Note:  You cannot name a cell while you are changing the contents of the cell.

Note:  If you don’t enter a title or text, the title defaults to “Microsoft Excel” and the message to: “The value you entered is not valid. A user has restricted values that can be entered into this cell.”

Deleting a Drop-Down List

To delete a drop-down list from an Excel spreadsheet,

  1. Select the cell that contains the drop-down list.
  2. On the Data tab, in the Data Tools group, click Data Validation.
    Insert or Delete a Drop-Down List in Excel 2
  3. In the Settings tab, click Clear All.
    Insert or Delete a Drop-Down List in Excel 3
  4. Click OK.

Print this entry