How to Create Drop Down List in Excel – Step By Step Guide

This article will show you how to add/create a dropdown list control in Excel so that the user can select only predefined value from custom list.

What we want to accomplish

We want the user to be able to select a color in cell B1 from the list of predefined colors. This is a typical situation for invoices or stock inventory lists. Exactly as in the image below.

Excel drop-down example
Excel drop-down example
  1. Click Data ribbon -> Data Validation section -> Data Validation button.

    Ribbon layout depends on the size of your screen. Below are three possible versions.

    Excel - Data Validation Item for Small Size Screen
    Excel - Data Validation Item for Small Size Screen
    Excel - Data Validation Item for Medium Size Screen
    Excel - Data Validation Item for Medium Size Screen
    Excel - Data Validation Item for Small Wide Screen
    Excel - Data Validation Item for Small Wide Screen
  2. In Data Validation window choose List option in Allow drop-down.

    Excel - Data Validation
    Excel - Data Validation
  3. Select range for Source that will be shown and used for validation in the drop down.

    You can type in named range or use mouse to select data like shown in the pictures below.

    excel-add-dropdown-data-validation-range-button
    Click to Enlarge
    excel-add-dropdown-data-validation-select-range
    Click to Enlarge
    excel-add-dropdown-data-validation-selected-range
    Click to Enlarge
  4. You can show a message when user selects cell with dropdown. To enable it go to Input Message tab and click Show input message when cell is selected. This window also allows you to set text that will be shown.

    excel-add-dropdown-input-message
    Click to Enlarge
  5. In Error Alert tab you can specify what will be shown if user types or selects invalid data.

    Warning
    excel-add-dropdown-warning-window
    Click to Enlarge
    Information
    excel-add-dropdown-information-window
    Click to Enlarge
    Error
    excel-add-dropdown-error-window
    Click to Enlarge
  6. Click OK button on Data Validation window. That's it! You have now created a dropdown control that lets user choose value from custom list.

1 thought on “How to Create Drop Down List in Excel – Step By Step Guide”

Leave a Comment