Home  »  ExcelOffice   »   Add Dropdown List in Excel – In 6 Easy Steps with Screenshots

Add Dropdown List in Excel – In 6 Easy Steps with Screenshots

By | December 21, 2014

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 a user to select a color in cell B1 from the predefined list of colors as shown on the picture.
Typical scenario when describing items in stock in any store.

What we want to accomplish
image-694

Step 1

Click DATA -> Data Validation -> Data Validation

For lower resolution Excel window

Excel DATA Data Validation Data Validation
image-695

For maximized Excel window

Excel DATA Data Validation Data Validation
image-696

Step 2

On Data Validation choose List in Allow drop-down.

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

On Data Validation choose List in Allow dropdown
image-697

Step 3

Select range for Source that will be shown and used for validation in the dropdown.

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

You can use data in other spreadsheets – for example if you have one sheet that contains only definition data.

Clicking select range in Data Validation window
image-698

Click to enlarge

Selecting range for Data Validation window
image-699

Click to enlarge

At the end

Data Validation window with selected range

Step 4

You can enable Input Message.
It will be shown when user clicks on the cell with dropdown defined as shown in the screenshot below.

Defining Inpute Message

Step 5

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

There are 3 styles or messages (based on priority):
– info
– warning
– error

Table below shows how each type will be presented to the user if selected value is invalid.

DefinitionResult for user
Error style of Error AlertExample of Error window
Warning style of Error AlertExample of Warning window"
Info style of Error AlertInfo style of Error Alert"

Step 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.

Configured dropdown in cell

Leave a Reply

Your email address will not be published.