All Create a Drop Down List in Excel

Create a Drop Down List in Excel

| Written by some jerk | Print | Email | Hits: 330

You can make a worksheet more efficient by providing drop-down lists. Someone using your worksheet clicks an arrow, and then clicks an entry in the list.

Sample drop-down list in Excel

  1. On a new worksheet, type the entries that you want to appear in your drop-down list. The entries should be in a single column or row without any blank cells, like this:

    Create your drop-down list of entries in single column or row in Excel

    TIP    Now is a good time to Sort our data in the order you want it to appear in your drop-down list.

  2. Select all of your entries, right-click, and then click Define Name.

  3. In the Name box, type a name for your entries, for example, ValidDepts, and then click OK. Be sure your name doesn’t have any spaces in it. This name won’t show up in your list, but you need to name it so you can link it to your drop-down list.

    Type a name for your drop-down list entries in Excel

  4. Click in the cell in the worksheet where you want the drop-down list.

  5. Click Data >Data Validation.

    Validate the drop-down list by clicking Data > Data Validation in Excel

    TIPS    Can’t click Data Validation? Here are a few reasons why that might happen.

    • Drop-down lists can’t be added to tables that are linked to a SharePoint site. Unlink the table or remove the table formatting, and then try step 5 again.

    • The worksheet might be protected or shared. Remove the protection or stop sharing the worksheet, and then try step 5 again.

  6. On the Settings tab, in the Allow box, click List.

  7. In the Source box, type and equal sign (=), immediately followed by the name you gave your list in step 3. For example, =ValidDepts.

    Select the settings for this drop-down list in the Settings tab in Excel

  8. Check the In-cell dropdown box.

  9. If it’s OK for people to leave the cell empty, check the Ignore blank box.

  10. Click the Input Message tab.

  11. If you want a message to pop up when the cell is clicked, check the Show input message when cell is selected box, and type a title and message in the boxes (up to 225 characters). If you don’t want a message to show up, clear the check box.

    Select the message you want people to see when they start to use the drop-down list in Excel

  12. Click the Error Alert tab.

  13. Check the Show error alert after invalid data is entered box, pick an option from the Style box, and type a title and message. If you don’t want a message to show up, clear the check box.

    Type a message you want people to see if the drop-down list doesn't work correctly in Excel

    Not sure which option to pick in the Style box?

    • To show a message that doesn’t stop people from entering data that isn’t in the drop-down list, clickInformation or Warning. Information will show a message with this icon Message shows Information icon but doesn't stop people from choosing from the drop-down list and Warning will show a message with this icon Message shows Warning icon but doesn't stop people from choosing from the drop-down list.

    • To stop people from entering data that isn’t in the drop-down list, click Stop.

      NOTE   If you don't add 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."