How to Create Drop Down List in Excel

.

Drop down list is made in Excel Cell to limit the Entry in to a Cell from a predefined set of values. It means you are restricted to select one value out of set of Predefined values. On creating a drop down list on a cell or cell-range, one drop-down will be displayed with set of predefined values, on selecting that cell and user can select one value out of that. You can not enter any value other than those predefined values.
Now we will see how to make Drop Down List in Excel 2003, 2007 and 2010.

How to Create Drop Down in Excel 2003

Follow below steps to Create Drop Down List in Excel 2003:

Step 1. Click on Data –> Data Validation option from the Top Menu

drop-down-list

Step 2. In validation criteria Select “List” from the Drop down

Step 3. Click on Settings tab in the dialog box.

Step 4. Select “List”option from the Drop Down

drop-down-list

Step 5. Now Enter the List of Values in Source, you want to Add in the Drop down. Write all the Values separated by Comma (,).

drop-down-list

Note: If you want to populate this list from the Values entered in to a Cell Range.

For Example: If you want to populate the Values from the Cell Range A1 to A5. So Enter the Source as =$A$1:$A$5. Whatever you enter in this Cell Range, will be shown in the Drop Down List.

Step 6. A down arrow should appear in the Selected Cell as shown below.

drop-down-list

How to Create Drop Down in Excel 2007/2010

Follow below steps to Create Drop Down List in Excel 2007/2010:

Step 1. Click on Data Tab –> Data Validation option from the Ribbon

drop-down-list

Step 2. In validation criteria Select “List” from the Drop down

Step 3. Click on Settings tab in the dialog box.

Step 4. Select “List”option from the Drop Down

drop-down-list

Step 5. Now Enter the List of Values in Source, you want to Add in the Drop down. Write all the Values separated by Comma (,).

Note: If you want to populate this list from the Values entered in to a Cell Range.

For Example: If you want to populate the Values from the Cell Range A1 to A5. So Enter the Source as =$A$1:$A$5. Whatever you enter in this Cell Range, will be shown in the Drop Down List.

Step 6. A down arrow should appear in the Selected Cell as shown below.

drop-down-list

How to Create Drop Down using named Range

Follow below steps to Create Drop Down List using Named Range in Excel:

Step 1. For this first we need to make the Named Range in the workbook.
Step 2. To make the Named Range, Select the Range
Step 3. Press Ctrl+F3

defined-names-in-excel

Step 4. Click on “New Button” in the Name Manager Dialog Box.

defined-names-in-excel

Step 5. Give a Name and Select the Scope
Step 6. Click OK

defined-names-in-excel

Step 7. Click Close.
Step 8. Now Ranged Name is created.

defined-names-in-excel

Step 9. To Create the Drop Down List Follow the below Instructions:
Step 10. Select the Cell or Cell Range where you want to Create the Drop down list.
Step 11. Go to Data Validation –> Select List
Step 12. In Source Data enter =Ranged Name which is created above


defined-names-in-excel

Buy a coffee for the author

Adsense

Download FREE Tools and Templates

There are many cool and useful excel tools and templates available to download for free. For most of the tools, you get the entire VBA code base too which you can look into it, play around it, and customize according to your need.

Dynamic Arrays and Spill Functions in Excel: A Beginner’s Guide
Dynamic Arrays and Spill Functions in Excel: A Beginner’s Guide

In today's tutorial, we'll be diving into the exciting world of dynamic arrays and spill functions in Office 365 Excel. These features have revolutionized the way we work with data, providing a more flexible and efficient way to handle arrays. I am going to explain...

How to Declare a Public Variable in VBA
How to Declare a Public Variable in VBA

While programming in VBA sometimes you need to declare a Public Variable that can store the value throughout the program. Use of Public Variable: Let's say you have 4 different Functions in your VBA Code or Module and you have a variable that may or may not be...

How to Copy content from Word using VBA

As many of us want to deal with Microsoft Word Document from Excel Macro/VBA. I am going to write few articles about Word from Excel Macro. This is the first article which opens a Word Document and read the whole content of that Word Document and put it in the Active...

What is Excel Formula?

Excel Formula is one of the best feature in Microsoft Excel, which makes Excel a very very rich application. There are so many useful built-in formulas available in Excel, which makes our work easier in Excel. For all the automated work, Excel Macro is not required. There are so many automated things can be done by using simple formulas in Excel. Formulas are simple text (With a Syntax) which is entered in to the Excel Worksheet Cells. So how computer will recognize whether it is a formula or simple text? Answer is simple.. every formula in Excel starts with Equal Sign (=).

You May Also Like…

5 Comments

  1. Sudhir

    I am unable to get to saqure one in your code (either version) because I get stopped on the Set cnn = ActiveWorkbook.Connections(cnnName).OLEDBConnection.ADOConnection line of code. cnnName is equal to PowerPivot Data . It gets past the initial Refresh but this gives me an Error 1004 Application or Object Defined Error.This happens with cnn set as either an Object or as an ADODB.ConnectionI tried to play with the References and tried the ActiveX Ojects 2.8 Library. OI am running this on Win7 SP1.with CPT3 Denali PP.Any thoughts?ThanxDick

    Reply
  2. Santosh

    Very nice article. Thanks for the help

    Reply
  3. Riduan

    How to create dropdown list with 3 coloumns and 5 rows
    Thx a lot

    Reply
  4. Srinath

    How to create dynamic drop down list (i.e. searchable)

    Reply

Trackbacks/Pingbacks

  1. Learn Excel Macro Name Range in Excel - [...] How to Create Drop Down using named Range [...]

Submit a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Join and get a FREE! e-Book

Don't miss any articles, tools, tips and tricks, I publish here

You have Successfully Subscribed!

Pin It on Pinterest