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.)
- Make sure the Forms toolbar is displayed. (Choose View | Toolbars | Forms.)
- Click on the Combo Box tool in the toolbar. The mouse pointer changes to a small crosshair.
- 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.
- Right-click on the newly created combo box. A Context menu appears.
- Choose the Format Control option from the Context menu. Excel displays the Control tab of the Format Control dialog box. (See Figure 1.)
Figure 1. The Control tab of the Format Control dialog box.
- 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.
- 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.
- 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.
Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!
Leave your own comment:
Comments for this tip:
Josephuz 06 Sep 2015, 22:16
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.
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
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
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
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.