Drop down lists in Microsoft Excel are great for ensuring that you enter accurate data is selected in your spreadsheet. It helps to minimize rework by ensuring that accurate data is input the first time. Here is a primer that explains why you would use drop down lists…

 

But not everyone knows that there are 3 different ways to make a drop down list. So… here they are.

Data Validation

Example 1 – This is the most common type of drop down list.  It is common because you don’t need to unhide the “Developer Tab” to do it.  Just point you mouse to the cell where you want the drop down list.  Select cell, make a few clicks in the dialog box in your worksheet, and wa-laa…  Drop Down list.  If you used a named range, be sure to put and equal sign and the named range in the “source” box. This will allow you to select the appropriate values from a range.

 

In the video below, I will show you the step-by-step process for creating this drop down list, but in the meantime, here are some PROS and CONS of taking this approach:

PROS

• Easy to find and learn.  The capability already exists in the normal Excel Tabs.  So, point, click, fill out some info and you have a drop down list.  Select your text.

CONS

• Contained within a cell.  You can’t make the drop down box larger or smaller than the cells.
• Can’t change the front size.  And by default, the font is small and difficult to read for end users.
• The drop down icon does not show up unless you active the cell (are in the cell).  This makes it difficult for end users to intuitively know that a drop down list exists.

Form Control Combo Box

Next Up – This dropdown menu approach is a little fancier than the Data Validation approach.  This requires that you unhide the “Developer Tab” in Excel.  Within the developer tab, there is a Form Control Combo Box that allows you draw a drop down box on your spreadsheet.  This is actually a pretty slick approach in that you are not restricted by the size of a cell.  Rather, the Form Control Combo Box is an object that you draw onto the spreadsheet.  So you are able to size it to your needs.

Here are some PROS and CONS of taking this approach:

PROS

• Drop down button is always visible
• Can resize the selected drop down box without resizing cell.  Spanning more than one column if necessary.
• The number of the item that you select can be linked to a cell.
• Can adjust the number of items that you view before you must scroll

CONS

• Can’t adjust the font
• Must unhide “Developer Tab”

ActiveX Combo Box

Now lets get “Double FANCY“.  Put on your Double Fancy Glitter Hat and lets get this thing started.  Under the same “Developer Tab” where you found the Form Control Combo Box, you will also find an ActiveX Combo Box.  Go ahead and click it, I DARE YOU... Please? This approach allows you to play with a ton of additional options, such as changing the font size, font type, and the number of items in the drop down list.  Click several or the options and look around.  I show you all of this in the video below.

Here are some PROS and CONS of taking this approach:

PROS

• Can adjust font size
• Can resize box
• The label of the item you select can be linked to a cell

CONS

• Must unhide the “Developer Tab”.  It doesn’t appear by default.

Create Drop Down Box In Excel – VIDEO Tutorial

 

Part 2 of the tutorial can be found below.

In part 2 we walk you through the step by step process of creating each of these drop down lists in Excel.

Here is the file

3 Types of Drop Down Lists <— Download the Excel File