March 11

How to Create a Drop Down List in Excel


exceldropdown1Data validation, when used within the Excel framework, is basically a control function. In this excel tutorial I will show you how it prevents users from entering bad data into a particular cell. Most often, users inadvertently enter the information into the wrong cell, but if the cell is programmed with an excel drop down list, they will be required to choose the right information. Obviously, this feature adds credibility to a document, as there is less chance of error, and at the same time, information entered can be completed in an expeditious fashion. Creating a drop down list in Excel provides accuracy, efficiency and higher productivity through speed. bullet-step-1

Create a Table

  1. 2014-05-09_22-29-44Make a table by entering your title and parameters into a sheet in your workbook.
  2. Be sure that your title is in the cell right below the Letter Heading. Do not leave spaces between any cells.
  3. Your parameters will be entered in column fashion vertically, not by row horizontally.
  4. Now, name the table by clicking on any cell in the parameters and look for the Insert, click Table.
  5. Make sure your full list is highlighted, then click the Checkmark to include Headers.

create a table 500



Create a Dynamic Range


The next step is to attach a named range to the table.  The benefits of doing this is that it allows you to add items to your drop down list by simply appending the table.  But Data Validation doesn’t like to attached directly to tables.  So this intermediate step will solve the problem.


First you should select the data in the table to activate it.  Just highlight all of the data in the column but be sure to NOT select the header.  The data that you select will feed the syntax that is needed in later steps.



Thennamed range Select Name Manager, highlight your Table and Select “NEW”



Next, Select the Table from Step #1 and Select New



You will notice that the correct syntax is auto-filled.  This is why it was important to only select the data in the table to activate before selecting the Name Manager



Assign Named Range to Data Validation List

Next you must create the drop down list and attach it to the Dynamic Range that you created in the previous step.

2014-05-10_1-58-26First, select Data > Data Validation



Then select “LIST” from the drop down box2014-05-10_2-06-54


Now here is the COOL part.  Enter the Named Range from Step #2 and everything will work perfectly.  WOOT.

dynamic range


Test it —  It Works

Add more data at the end of your original list and you will see that the items are auto-magically added to the drop down list.  That’s AWESOME !!!




 Drop Down using a Dynamic Range in Excel

Download the Excel Drop Down Fileclick to download

Download the file and follow along with the video.

Yours for free. 🙂

get moreCheck out the rest of our Drop Down List Series


Well —-  Whatta ya’ think?  Got an extra twist to this that you think might help my readers?  Got something to add?  Don’t be shy.  Leave a comment below and let me know what you think.

Also, if you think that a colleague might get some value out of this, feel free to hit one of those social sharing thing-a-ma-jigs.  I’d appreciate it.  As I tell my kids…  sharing is caring 🙂

Until next time

Be A Champion

Rick Grantham


You may also like

December 20, 2013

100+ Best Excel Resources And Websites

August 6, 2019

How to Create a Relative File Path in Power Query

April 9, 2011

How to Create a Rollover Effect in Excel: Execute a Macro When Your Mouse is over a Cell

September 17, 2012

The Excel Rollover Mini FAQ

February 17, 2014

It’s time to say “Goodbye,” to Hungarian Notation
{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

Free Power User Quick Guide

Discover The BEST Ways To Use Lookups And Conditional Calculations Quickly And Easily With This Reference Guide -- You Won’t Want This To Leave Your Side

With so many ways to use Excel, it can be difficult to memorize all of the key functions, calculations, and techniques you can employ to meet your goal: simply get the job done.