Loading
Excel.Tips.Net ExcelTips (Menu Interface)

Adding and Using a Combo Box

Excel includes several different form controls that you can add to your worksheets. One of these controls is a combo box. This control allows you to pick an option from a drop-down list, and then determine what was picked. To create a combo box, follow these steps:

Somewhere in your worksheet, create a list that specifies what you want to appear in the combo box. For instance, if you have a list of names you want to appear in the combo box, create that list of names in your worksheet. (For this example, let's assume that you create the list in cells K7 through K13.)

  1. Make sure the Forms toolbar is displayed. (Choose View | Toolbars | Forms.)
  2. Click on the Combo Box tool in the toolbar. The mouse pointer changes to a small crosshair.
  3. Create the actual combo box by clicking and dragging to define the parameters of the control. When you release the mouse button, the combo box appears in your worksheet.
  4. Right-click on the newly created combo box. A Context menu appears.
  5. Choose the Format Control option from the Context menu. Excel displays the Control tab of the Format Control dialog box. (See Figure 1.)
  6. Figure 1. The Control tab of the Format Control dialog box.

  7. In the Input Range box, specify the range used by the list you created in step 1. (For instance, K7:K13.) You can also click once in the Input Range box and then use the mouse to select the range in the worksheet.
  8. In the Cell Link box, specify the worksheet cell that you want to contain the index value of what is selected in the combo box.
  9. Click on OK.

Your combo box should now work properly. If you click on the down-arrow to the right of the combo box, you should see the items from you list. If someone selects an option in the combo box, the cell you specified in step 7 is updated to contain the relative position of the item selected in the combo box. In other words, if some selects the fourth item in the combo box, then the cell specified in step 7 will contain the value 4. (Similarly, if you change the value at that cell—the one specified in step 7—to a different value, then Excel changes what is displayed in the combo box.)

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2711) applies to Microsoft Excel 97, 2000, 2002, and 2003.

Related Tips:

Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!

 

Leave your own comment:

*Name:
Email:
  Notify me about new comments ONLY FOR THIS TIP
Notify me about new comments ANYWHERE ON THIS SITE
Hide my email address
*Text:
*What is 5+3 (To prevent automated submissions and spam.)
 
 
           Commenting Terms

Comments for this tip:

Josephuz    06 Sep 2015, 22:16
hi...
please help...

im making a supplies inventory. the scenario is

if combox is = to selected supply, it will automatically prompt the prize.

my head hurts analyzing how to do this in excel 2007.

please help, thank you in advance.
Alvaro    29 Oct 2014, 17:07
Hello, I have the same question that many of you already posted:

How do you copy/paste combo boxes so that the cell link corresponds to the line/position where the combo box is located?

When you copy paste, both the range and target cell remain fixed.

Thank you,

Alvaro.
Waheed Hussain    04 Sep 2014, 09:46
Sir I'm Using & Lurning Advance Excel Calss Please Help Me
CHRISTINA ROMERO    19 Aug 2014, 00:55
The whole "point" of Combo Boxes, is that, with drop down lists, you can't format the font. This is a real downer when you need to resize columns, but the drop down font remains so teeny tiny, you can't even read what your choices in the list are! With Combo Boxes; however, you CAN manipulate the size, and style of the font. For instance, if you had a list of students that you wanted to alphabetize,and also show what learning group they are in, by the color of the font ex: the "advanced" group in red font, the "meeting standards" group in blue, and the "needs improvement" group in green.

Another great thing about Combo Boxes is that you can drop down a list of more than 8 items. With the Drop List, you are limited to 8 items viewed at a time, so if you have an extremely long list to chose from, it would take forever to scroll through 8 at a time.

One other advantage of Combo Boxes, which, I believe, is truly neat...is that you can choose the option of "autofill". This fills the box with items from the list range based on the letters you are typing. EX: I start typing "A",the box displays "Alabama". I then add an r "Ar" and the box displays "Arizona". Lastly I add a k:"Ark", and the box displays "Arkansas"
Farman    26 Mar 2014, 21:05
Hi There,

I have a Combo box and what I would like to do is whenever I select any value in the Combo box I would like to display that value in a cell in a particular column, how would I be able to do this.

Appreciate all the help

Thanks,
Steve Nadel    01 Feb 2014, 14:42
Maybe I'm a little slow here.
I understand the creation steps 100%. What I'm a little backwards on is what is the point of the entire exercise.

If you know "Data Validation", you can create a "pick list" and fill in the correct corresponding answer into designated cells of your database.

The only purpose I see with the Combo box is to create a list, have it return back as text with a corresponding number in a cell not in your database.

Then what? What is my end goal?

Thanks for any clue.
Steve    30 May 2013, 13:36
Hello, I have a combo box and it's populated with my list. I'd like this box to appear in many cells within a column. How do I copy the combo box, say in every cell in a particular column? I see above where it states to hold the bottom right to get the + and drag, but that just expands the box over multiple columns and not put a separate box in each cell. thank,
Chipster    06 May 2013, 09:23
Once you have your first combo built the way you want it, hold the bottom right of the combo box to get the "+" sign. Hold that and drag it down to fill in as many rows as you need. If you (like me) tested your first box and it has a value in it, just delete the value so it is blank, then pull the blank box down. Hope that works for you!
Ingrid Kennedy    05 May 2013, 19:27
Hi folks,

Great tool this (Combo boxes) but does one apply this to several cells under each other, without have to repeat the whole process. Copy paste does not work, as it simply applied the same value to the pasted cell. I may need to apply combo boxes to lots of cells next to other, and hope even another tool may be available.
Thank you
Ingrid
 
 

Our Company

Sharon Parq Associates, Inc.

About Tips.Net

Contact Us

 

Advertise with Us

Our Privacy Policy

Our Sites

Tips.Net

Beauty and Style

Cars

Cleaning

Cooking

DriveTips (Google Drive)

ExcelTips (Excel 97–2003)

ExcelTips (Excel 2007–2016)

Gardening

Health

Home Improvement

Money and Finances

Organizing

Pests and Bugs

Pets and Animals

WindowsTips (Microsoft Windows)

WordTips (Word 97–2003)

WordTips (Word 2007–2016)

Our Products

Helpful E-books

Newsletter Archives

 

Excel Products

Word Products

Our Authors

Author Index

Write for Tips.Net

Copyright © 2016 Sharon Parq Associates, Inc.