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 - With the ComboBox active (Right Click it) move the Combo Box temporarily away
- Activate ce)ll
"B5"
and type in the following Formula:
=VLOOKUP(C5;Settings!B3:C9;2)
(if unsure how this works you can use the Formula Wizard by clicking the fx-button and choose VLOOKUP) - Test the Formula by changing weekdays in the ComboBox
- When Successful, Move the ComboBox back (step 6) over the Formula field to cover it.
Good luck to ya!
Oh, and if you have any questions about this, feel free to contact me here or in the Forum.
About Thomas
Computer geek from the age of 7, which amounts to 30 years of computer experience. From the early days (when every computer company had their own OS) of DOS, Windows 1.0 through Seven...
Search Windows Guides