I’m starting a new series here, which involves daily usable tips for different programs. This time I’m going to show you how to create a usable Drop Down Menu in Excel. In this mini-tutorial I am using Excel 2010, but the steps are pretty much the same no matter which Office Version you use. The main difference is where you find the different tools, and where possible I will try to mention where things differ.

Creating a usable Pull-down Menu

If you use Excel on a day-to-day basis filling out forms and information, you might find yourself typing in the same information over and over. Either by typing it in or using copy and paste. When filling in forms you will normally use the same cells over and over (this is where we can use Pull-down Menus). Pull-down Menus can also control your Excel document in different ways like retrieve information or navigate between Sheets and Documents.

Activate the Developer Toolbar (Office 2010 Users Only)

To access the Form tools we have to activate the Developer Toolbar.

Click on the Green File Tab

Choose Options

In the Options Dialogue, click “Customize Ribbon”

In the List Window to the right, there is a list of all the available Ribbons.
Locate the one Called: “Developer” and Check the box next to it

Click OK to Save

Creating a Settings Sheet.

First thing I do (when starting a new Excel Project) is to create a Sheet called Settings. This is to keep everything as clean and neat as possible. For this tutorial we will benefit from having a “Settings Sheet” as we get a place to keep the options for our Pull-Down Menu. You should always Index the Options when creating Pull-Down Menu Content. I will explain a bit later.

Create a new Sheet (or Choose an existing one) and Right Click it.
Choose Rename from the context menu, Name the Sheet “Settings”

In the first column type the index numbers from 1 to X (where X is the number of lines needed).

In the Second Column, type in the Options (Weekdays for this example)

Now, head over to our form-sheet to add our menu.

Click the Developer Toolbar (Earlier Office, look for the FORM Controls)

In the Developer Toolbar there’s a menu-item called Insert, click the little arrow underneath to show the Form Controls menu.

Click on the ComboBox Control (#2 from left)

Draw the ComboBox in place (or simply click where you want it)
Resize the control by dragging the handles in each corner.

Right-Click the ComboBox and Choose “Format Control”.
In the dialogue box, we need to assign which cells from which we will collect the data,
which cell to store the user choice, and what appearance we like.

Place the cursor within the Input Range Fieldbox (click it)

Click the Settings-Sheet and highlight the options area (Weekdays)

Activate the Cell-Link Field (this should automatically open the Form-Sheet) and Type in the CellReference to save the user choice in. In this example we use "B5" (which is hidden by the ComboBox).

Decide how many lines to display in the ComboBox (I set it to 7) and check the 3D-Shadow option.

Click OK

Click somewhere outside the ComboBox to activate it

Now when you click the ComboBox the weekdays should be appearing.

Practical use

It looks good right? So how do we put this to good use? If you were to print this form you would see the ComboBox appearing on the print. If you Right-Click the ComboBox and click the Properties Tab. Uncheck “Print Object” to make it invisible on print.

IF you do then the print will look something like this:
The weekday (or user choice) is represented by an index number (in this case #3).

Use ComboBox as LookUp Tool

We can use the index number to look up data corresponding to the user’s choice. If you use the ComboBox to retrieve Customer Names from a list, then we can use formulas to retrieve more data like contact person, address, phone number etc. In this example we will retrieve the Weekday from our Settings-Sheet, making it appear in real text instead of an index number. The procedure for fetching more data is exactly the same.

Right-Click the ComboBox and choose “Format Control”

Click the Properties Tab and uncheck the “Print Object”.

Click the Control Tab and Change the Cell-Link to "C5" instead of "B5"

Click OK

Now format the Text Color in Cell "C5" to white, making it seem invisible