April 24

Create Drop Down Box In Excel – “How To” Video Tutorial

0  comments

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…

[responsive_video type=’youtube’ hide_related=’0′ hide_logo=’0′ hide_controls=’0′ hide_title=’0′ hide_fullscreen=’0′ autoplay=’0′]https://youtu.be/QSaiqW17J-Q[/responsive_video]

 

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

bullet-1

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.

 

create drop down box in excel

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.

bullet-2Form 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.

form control gif 500

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”

bullet-3ActiveX 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.

activex gif 500

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

[responsive_video type=’youtube’ hide_related=’0′ hide_logo=’0′ hide_controls=’0′ hide_title=’0′ hide_fullscreen=’0′ autoplay=’0′]https://youtu.be/7qU5eRbmvfY[/responsive_video]

 

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

Rick Grantham
Follow Me

Tags


You may also like

April 9, 2011

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

October 26, 2014

Monte Carlo Simulation Formula in Excel – Tutorial and Download

September 17, 2012

The Excel Rollover Mini FAQ
{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

Free 24 Excel Tips Guide

Discover the secrets to Excel efficiency with our free guide: '24 Proven Microsoft Excel Tips to Help You Save Time'

A humble gift from us to you, to make your Excel journey smoother and more productive. Grab your copy now and start mastering Excel like never before!

>