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.

1. Click Data ribbon -> Data Validation section -> Data Validation button.

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

   

   

   

2. In Data Validation window choose List option in Allow drop-down.

   Make sure that In-cell dropdown is selected (should be by default).

   

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.

   You can use data in/from other spreadsheets - for example if you have one sheet with main user interface and other containing only definition data.

   

   

   

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.

   

5. In Error Alert tab you can specify what will be shown if user types or selects invalid data.

   Warning

   Information

   Error

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.