Adding and Using a Combo Box

by Allen Wyatt
(last updated December 6, 2016)

9

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.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Adding Page Numbers

Ever want to add page numbers to your document? Word allows you to control many aspects of page numbering. Here's how to add ...

Discover More

Automating Copying Macros

You can manually copy macros from one workbook to another, but what if you want to automate the copying process? Here's some ...

Discover More

Creating New Windows

A great way to work on different parts of the same document at the same time is to create windows. These function as ...

Discover More

Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!

More ExcelTips (menu)

Using Check Boxes

Check boxes, just like those used in Windows dialog boxes, can be a great addition to a worksheet. Here's how to add them and ...

Discover More

Comparing Lists for Duplicates

Do you have two worksheets on which you need to see if there is duplicate information? Here is a couple of quick ways to ...

Discover More

Making Revisions

You've turned on Highlight Changes, but how do you know what has been changed? This tip explains how Excel displays those ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 6 - 0?

2015-09-06 22:16:49

Josephuz

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.


2014-10-29 17:07:33

Alvaro

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.


2014-09-04 09:46:57

Waheed Hussain

Sir I'm Using & Lurning Advance Excel Calss Please Help Me


2014-08-19 00:55:51

CHRISTINA ROMERO

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"


2014-03-26 21:05:07

Farman

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,


2014-02-01 14:42:10

Steve Nadel

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.


2013-05-30 13:36:47

Steve

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,


2013-05-06 09:23:09

Chipster

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!


2013-05-05 19:27:29

Ingrid Kennedy

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


This Site

Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.