Add Combo Box to a Sheet in Microsoft Excel





What are the Advantages of working with Combo Box?

Choosing an item from a list in the Combo Box returns a number. The number then replaces the use of the Match formula to calculate the column number in the Vlookup formula (read tip) and replaces the use of the Match formula (read tip) to calculate the column number in the Index formula.

Working with the Combo Box

A list of the cell ranges in the sheet is attached to the Combo Box. After an item is selected from the list, the Combo Box enters into the linked cell the ordinal number of the item that is selected on the list.

 

Example:

The screen shot displays a profit and loss statement as compared to the previous year. Changing the month in the Combo Box will automatically change the display of data according to the selected month.


Step 1: Define names

1.Enter the months list – January-December into the cells A1 to A12 in the new worksheet.

2.Select cells A!:A12, press Ctrl+F3, enter the text MonthsList into the Names In workbook box,and click OK.

3.Select Cell B1, press Ctrl+F3, enter the text MonthNumber into the Names in Workbook box, and click OK.


Step 2: Add a Combo Box to a worksheet

1. Select one of the toolbars, right-click, and select the Forms toolbar.

2. Copy the Combo Box by clicking the Combo Box icon, and then release the mouse. Create a rectangle with the mouse in the worksheet, and then release the mouse.


Step 3: Format the Combo Box

1.Select the Combo Box; right-click; and from the shortcut menu that appears, select Format Control. Then select the Control tab

2.In the Input range box, type the name MonthsList (You cannot press F3 to paste a name with   an object).

3.In the Cell link box, type the name MonthNumber.

4.Click to select the 3-D shading box (more aesthetic).

5.Click OK.

Exit the formatting mode of Combo Box, and select a cell in the sheet. Open the list of items in the Combo Box, and select a month. Note that the new month number is shown in cell B1.
Advantages of working with names in a Combo Box

Attaching a list with Define Names causes your list references to be updated automatically in the Combo Box. Sorting the list in the sheet will automatically sort the list in Combo Box as well.

Deleting a Combo Box

Select the object; right-click; and from the shortcut menu that appears, select Cut.

Adjusting the size of the Combo Box to the cells

Select the Combo Box and right-click; the Combo Box is now in editing mode. Adjust the width or height of the Combo Box by dragging one of the corner boxes; adjust the placement of the Combo Box on the worksheet by clicking anywhere inside the box and dragging it.


Automatic adjustment of the Combo Box 

Hold down the Alt key while you click the mouse to adjust the size of the Combo Box.

Screenshot // Add Combo Box to a Sheet in Microsoft Excel
Add Combo Box to a Sheet in Microsoft ExcelAdd Combo Box to a Sheet in Microsoft Excel



64 thoughts on “Add Combo Box to a Sheet in Microsoft Excel

  1. How does Excel know that January=1, February=2, et cetera? Nowhere in the instructions do I see that we tell the sheet what to return when a certain month is selected. When I choose ‘September’, I get ‘September’.

  2. If you use the combo box control from the ‘Control Toolbox’ toolbar instead of the ‘Forms’ toolbar, you’ll be able to control the font

  3. “Ali-If you used the combo box from the ‘Control Toolbox’, there isn’t a ‘Control Tab’.
    Right click on the combo box and select ‘Properties’, then look for the LinkedCell and ListFillRange fields which should be the equivalent to what is on the ‘Control’ tab.”

  4. How do I get the link cell reference to be relative instead of absolute? I’ve tried all combinations of cell reference and $ using the F4 key. Any help would be appreciated – thanks.

  5. “Thanks JR and mseyf. One more question: I’m using the combo box control from the “”Control Toolbox”” toolbar–how do I change the button size?
    Thanks again!”

  6. instead of insert cell address, insert a name define to link cell ( keep in mind that you can’t use the keyboard short cut F3 in any box at the Format control window, so just type the name you defined ),then you may change the absolute reference to relative referance in the defined name window by pressing Ctrl+F3.

  7. “i would like the to enter text in the drop down list, and then when selected from the combo box, have some other text then displayed in a specific cell.
    Thanks”

  8. “What if, instead of use the combo from Forms toolbars I use the one in Control Toolbox and I want to fill it by using the field in a recordset, I know how to do it if this is in a form, but not into the spreed sheet directly, can you give me some help ?
    Best Regards !!”

  9. how can I get rid of the border of the comboxbox when I click on an item. i would like for it to look like a regular text without anything around it.

  10. “How do I get data depending on the selection I make in the combo box?
    eg. if my combo box includes Jan.Feb.Mar, and once I select Jan, the report for Jan is to be displayed on the same sheet, how do I do it. HELP please, i’m confused.”

  11. Hi there, Is there anyone who could tell me how to create a formula for the LinkedCell property. I want to make 6 combo boxes on each row, and approximately 1000 rows, but i don’t want to fill in the LinkedCell property over and over again. What should I do?

  12. “Well, using only 1 combo box is fine without caring about cell link’s being relative or absolute. But when I copy the cell containing the combo box, I need the cell link to be relative.
    Here’s something to visualize the problem:
    Think of an order form. I got 100 orders in the list. And I got a column which I want to put a combo box with selections “”paid”” or “”not paid”” . Well, I put my first combo box in my first order. But when I copy that cell down for the remaining 99 orders, all the combo boxes that I created (for the other 99 orders) all are linked to the FIRST order’s combo box’s cell reference. However I want that cell’s reference to be relative so that when I copy the cell(and the combo box as well, as that cell contains containing the combo box) their cell link is changed. I need this cos I may have 10000 orders for example and modifying all the cell refeences of all those orders’s combo boxes may be annoying.
    JR from LA ‘s solution is not working (on the subject cell link – absolute to relative reference)
    Any help is welcomed. Thanks in advance. “

  13. I think you both -Ali & Pim use Combo box instead of using Validation. to use Combo Box just for the purpose of selecting one option from two? add list with two items to Validation “paid” and “unpaid” and add a simple if formula that will return 1 or 2, the real purpose of using Combo Box is to change hundreds of formulas calculation results by choosing one of the item from list.

  14. I think you both -Ali & Pim use Combo box instead of using Validation. to use Combo Box just for the purpose of selecting one option from two? add list with two items to Validation “paid” and “unpaid” and add a simple if formula that will return 1 or 2, the real purpose of using Combo Box is to change hundreds of formulas calculation results by choosing one of the item from list.

  15. “Hmm, Pim! It’s good to know I’m not alone. Been 3 full working days, crawling the net, a thorough study in Excel Help and trial/error sessions, all seeking a solution to my question. And yes I can say,
    Excel CANNOT do that!
    In Excel, you CANNOT use relative cell references for ANY form & control toolbox managed item, such as ComboBox or Check Box. Poor Microsoft.
    Jim from UK may be right, critisizing our basic usage of Combo Box. But what I want is something anyone can want at any time. Hmm… Let’s see another very practical use of Combo box where we may desperately need relative references:
    Consider the following situation: (Jim read this! This is a good purpose to use spinners)
    Think of a warehouse record sheet. You need:
    1) Entrance date of a good
    2) Departure date of the good.
    Each requiring entering dates. We know how annoying it is to enter dates manually in excel. So, to solve that, I use a SPINNER. I format the cell as a date and put a spinner linked to it. Here, somethi,ng is extremely important : If u put the spinner from the “”Forms””, the maximum increment of the spinner is 30000. Considering the date format of excel starting from 01/01/1900 (stupid!!!) 30000 days later is some time in 1981. So spinner cannot come to 2003, failing my idea.
    Instead, try putting it from Control Toolbox. There, from properties, you may select 100000 to maximum value of the spinner and initial value to 37750 (to start initially from 2003 – You may not want to scroll down all 1900′s to come to 2003 :-)) So, I managed to put a very useful spinner to solve the terrible problem of entering dates in excel.
    But here comes the problem : Somebody may have thousands of goods to manage in a warehouse, each using my very way of date inputting. However, When you copy the cell of the first good (containing the spinner) to the remaining (lets say) 100 goods, all the copied spinners will change THE FIRST GOOD’s date. What an idiot!
    So, under such a circumstance, relative reference could have been a lifesavior. But where is Microsoft ? Still asking “”where do you want to go today?”” I’m not going anywhere! But you cannot even serve me here staying. Grrr…! Anyways, there is always a solution for intelligence. Here’s how you can approach the problem:(again mentioning to the warehouse case)
    Use the excel sheet to manage just your “”monthly”” entrance-departures. That will cut the number of items to a considerable amount, which will hopefully prevent you from boooming up while changing the cell link’s of the copied Combo Boxes/Spinners. Use the copy of the same sheet for all your monthly works. Though, as you may have noticed, that’s a short term solution. For medium&long term I suggest you learn programming :-) or wait for Microsoft’s new office packets.
    Will be here later on to read what you think,
    See ya. “

  16. “Hmm, Pim! It’s good to know I’m not alone. Been 3 full working days, crawling the net, a thorough study in Excel Help and trial/error sessions, all seeking a solution to my question. And yes I can say,
    Excel CANNOT do that!
    In Excel, you CANNOT use relative cell references for ANY form & control toolbox managed item, such as ComboBox or Check Box. Poor Microsoft.
    Jim from UK may be right, critisizing our basic usage of Combo Box. But what I want is something anyone can want at any time. Hmm… Let’s see another very practical use of Combo box where we may desperately need relative references:
    Consider the following situation: (Jim read this! This is a good purpose to use spinners)
    Think of a warehouse record sheet. You need:
    1) Entrance date of a good
    2) Departure date of the good.
    Each requiring entering dates. We know how annoying it is to enter dates manually in excel. So, to solve that, I use a SPINNER. I format the cell as a date and put a spinner linked to it. Here, somethi,ng is extremely important : If u put the spinner from the “”Forms””, the maximum increment of the spinner is 30000. Considering the date format of excel starting from 01/01/1900 (stupid!!!) 30000 days later is some time in 1981. So spinner cannot come to 2003, failing my idea.
    Instead, try putting it from Control Toolbox. There, from properties, you may select 100000 to maximum value of the spinner and initial value to 37750 (to start initially from 2003 – You may not want to scroll down all 1900′s to come to 2003 :-)) So, I managed to put a very useful spinner to solve the terrible problem of entering dates in excel.
    But here comes the problem : Somebody may have thousands of goods to manage in a warehouse, each using my very way of date inputting. However, When you copy the cell of the first good (containing the spinner) to the remaining (lets say) 100 goods, all the copied spinners will change THE FIRST GOOD’s date. What an idiot!
    So, under such a circumstance, relative reference could have been a lifesavior. But where is Microsoft ? Still asking “”where do you want to go today?”” I’m not going anywhere! But you cannot even serve me here staying. Grrr…! Anyways, there is always a solution for intelligence. Here’s how you can approach the problem:(again mentioning to the warehouse case)
    Use the excel sheet to manage just your “”monthly”” entrance-departures. That will cut the number of items to a considerable amount, which will hopefully prevent you from boooming up while changing the cell link’s of the copied Combo Boxes/Spinners. Use the copy of the same sheet for all your monthly works. Though, as you may have noticed, that’s a short term solution. For medium&long term I suggest you learn programming :-) or wait for Microsoft’s new office packets.
    Will be here later on to read what you think,
    See ya. “

  17. “Can anyone answer this question? I need to do this too.
    Thanks! » Combo Box border
    JJ from IL wrote on June 21, 2003 9:39 PM EST
    how can I get rid of the border of the comboxbox when I click on an item. i would like for it to look like a regular text without anything around it. “

  18. Does any one know how to create a combo box (or drop down list) within a cell? The usual combo box is just an add-on layer to a spreedsheet but not integrated into a cell. Help!

  19. Hi guys. Put the combo into edit mode using the Control Toolbox. Select the conbo and right-click to display Properties. Change the BorderStyle property.

  20. Put the combo in edit mode with Control toolbox. Right-click it and click Format Control. Select the Properties tab. Select the ‘Move and Size with cells’ option

  21. I’ve noticed that when i select a value from the combo box, the linked cell will display the number according to the item placed in the combo box. Can i have the value displayed instead of number?

  22. Here is what I’m trying to create. I want to use one combo box or drop down list box and use one item from that list in various cells on a spreadsheet. I’m new to creating combo boxes, I don’t even know if this can be done. Any help would be great. R

  23. How do you selecting something that starts with the letter t? I want to be able to do samething as hitting the letter t in the font drop down box? Any advice?

  24. “I have Created a Sheet for people to enter specs, and it automaticly Creates, P/N’s and Descriptions. Well The people I have created this for, are not so Bright at times. So there for I want to create a Button that Reset’s all Combo-Box list’s back to the top selection.
    Basicly you have
    Colors
    Black
    Blue
    Green.
    They selevet blue, Then when they are finished, they just hit a button to Select color again.
    See I have about 15 Combo Boxs Created, so its hard for them to go back and select the Original Settings. Well I know that they can just close the File and open the Original, but They sometimes Save over the top of that one, so I have to constitenty send them the Original.
    So this is why I’d like to create a reset Combo-Boxes Button.
    I have tried recording macro’s, and it does not work with, Control Tool Box, or Forms.
    Anyone have any Sugestions?”

  25. How does Excel know that January=1, February=2, et cetera? Nowhere in the instructions do I see that we tell the sheet what to return when a certain month is selected. When I choose ‘September’, I get ‘September’.

  26. If you use the combo box control from the ‘Control Toolbox’ toolbar instead of the ‘Forms’ toolbar, you’ll be able to control the font

  27. “If you used the combo box from the ‘Control Toolbox’, there isn’t a ‘Control Tab’.
    Right click on the combo box and select ‘Properties’, then look for the LinkedCell and ListFillRange fields which should be the equivalent to what is on the ‘Control’ tab.”

  28. How do I get the link cell reference to be relative instead of absolute? I’ve tried all combinations of cell reference and $ using the F4 key. Any help would be appreciated – thanks.

  29. Thanks JR and mseyf. One more question: I’m using the combo box control from the “Control Toolbox” toolbar–how do I change the button size? 

  30. instead of insert cell address, insert a name define to link cell ( keep in mind that you can’t use the keyboard short cut F3 in any box at the Format control window, so just type the name you defined ),then you may change the absolute reference to relative referance in the defined name window by pressing Ctrl+F3.

  31. i would like the to enter text in the drop down list, and then when selected from the combo box, have some other text then displayed in a specific cell. 

  32. What if, instead of use the combo from Forms toolbars I use the one in Control Toolbox and I want to fill it by using the field in a recordset, I know how to do it if this is in a form, but not into the spreed sheet directly, can you give me some help ? 

  33. how can I get rid of the border of the comboxbox when I click on an item. i would like for it to look like a regular text without anything around it.

  34. “How do I get data depending on the selection I make in the combo box?
    eg. if my combo box includes Jan.Feb.Mar, and once I select Jan, the report for Jan is to be displayed on the same sheet, how do I do it. HELP please, i’m confused.”

  35. Hi there, Is there anyone who could tell me how to create a formula for the LinkedCell property. I want to make 6 combo boxes on each row, and approximately 1000 rows, but i don’t want to fill in the LinkedCell property over and over again. What should I do?

  36. “Well, using only 1 combo box is fine without caring about cell link’s being relative or absolute. But when I copy the cell containing the combo box, I need the cell link to be relative.
    Here’s something to visualize the problem:
    Think of an order form. I got 100 orders in the list. And I got a column which I want to put a combo box with selections “”paid”” or “”not paid”” . Well, I put my first combo box in my first order. But when I copy that cell down for the remaining 99 orders, all the combo boxes that I created (for the other 99 orders) all are linked to the FIRST order’s combo box’s cell reference. However I want that cell’s reference to be relative so that when I copy the cell(and the combo box as well, as that cell contains containing the combo box) their cell link is changed. I need this cos I may have 10000 orders for example and modifying all the cell refeences of all those orders’s combo boxes may be annoying.
    JR from LA ‘s solution is not working (on the subject cell link – absolute to relative reference)
    Any help is welcomed. Thanks in advance. “

  37. “I think you both -Ali & Pim use Combo box instead of using Validation.
    to use Combo Box just for the purpose of selecting one option from two?
    add list with two items to Validation “”paid”” and “”unpaid”” and add a simple if formula that will return 1 or 2, the real purpose of using Combo Box is to change hundreds of formulas calculation results by choosing one of the item from list.”

  38. “Hmm, Pim! It’s good to know I’m not alone. Been 3 full working days, crawling the net, a thorough study in Excel Help and trial/error sessions, all seeking a solution to my question. And yes I can say,

    Excel CANNOT do that!

    In Excel, you CANNOT use relative cell references for ANY form & control toolbox managed item, such as ComboBox or Check Box. Poor Microsoft.

    Jim from UK may be right, critisizing our basic usage of Combo Box. But what I want is something anyone can want at any time. Hmm… Let’s see another very practical use of Combo box where we may desperately need relative references:

    Consider the following situation:

    (Jim read this! This is a good purpose to use spinners)

    Think of a warehouse record sheet. You need:

    1) Entrance date of a good
    2) Departure date of the good.

    Each requiring entering dates. We know how annoying it is to enter dates manually in excel. So, to solve that, I use a SPINNER. I format the cell as a date and put a spinner linked to it. Here, somethi,ng is extremely important : If u put the spinner from the “”Forms””, the maximum increment of the spinner is 30000. Considering the date format of excel starting from 01/01/1900 (stupid!!!) 30000 days later is some time in 1981. So spinner cannot come to 2003, failing my idea.
    Instead, try putting it from Control Toolbox. There, from properties, you may select 100000 to maximum value of the spinner and initial value to 37750 (to start initially from 2003 – You may not want to scroll down all 1900′s to come to 2003 :-)) So, I managed to put a very useful spinner to solve the terrible problem of entering dates in excel.

    But here comes the problem :

    Somebody may have thousands of goods to manage in a warehouse, each using my very way of date inputting. However, When you copy the cell of the first good (containing the spinner) to the remaining (lets say) 100 goods, all the copied spinners will change THE FIRST GOOD’s date. What an idiot!

    So, under such a circumstance, relative reference could have been a lifesavior. But where is Microsoft ? Still asking “”where do you want to go today?”” I’m not going anywhere! But you cannot even serve me here staying. Grrr…!

    Anyways, there is always a solution for intelligence. Here’s how you can approach the problem:

    (again mentioning to the warehouse case)

    Use the excel sheet to manage just your “”monthly”” entrance-departures. That will cut the number of items to a considerable amount, which will hopefully prevent you from boooming up while changing the cell link’s of the copied Combo Boxes/Spinners.

    Use the copy of the same sheet for all your monthly works.

    Though, as you may have noticed, that’s a short term solution. For medium&long term I suggest you learn programming :-) or wait for Microsoft’s new office packets.

    Will be here later on to read what you think,
    See ya.”

  39. “Hmm… Guess I was so quick to despair.

    Data validation does what we want to do.

    Check Data->Validation and choose list there.

    Special thanks to jim from UK. “

  40. “Can anyone answer this question? I need to do this too.

    Thanks!

    » Combo Box border
    JJ from IL wrote on June 21, 2003 9:39 PM EST
    how can I get rid of the border of the comboxbox when I click on an item. i would like for it to look like a regular text without anything around it. “

  41. “Does any one know how to create a combo box (or drop down list) within a cell?

    The usual combo box is just an add-on layer to a spreedsheet but not integrated into a cell. “

  42. “Does any one know how to create a combo box (or drop down list) within a cell?

    The usual combo box is just an add-on layer to a spreedsheet but not integrated into a cell. “

  43. “Put the combo into edit mode using the Control Toolbox.
    Select the conbo and right-click to display Properties.
    Change the BorderStyle property.”

  44. “Put the combo in edit mode with Control toolbox.
    Right-click it and click Format Control.
    Select the Properties tab.
    Select the ‘Move and Size with cells’ option.”

  45. “As far as I can see, the list displayed in a combo box is dependent on what is present in the named range when the sheet is opened.

    I am using an external query to return the list of options available in the combo box – and this data is not saved with the file (ie it refreshes on open, as it changes from day to day) – when the file opens, there is no list present in the combo box, and it does not refresh once the data list is present.

    Has anyone else had this problem? “

  46. I’ve noticed that when i select a value from the combo box, the linked cell will display the number according to the item placed in the combo box. Can i have the value displayed instead of number?

  47. “Here is what I’m trying to create. I want to use one combo box or drop down list box and use one item from that list in various cells on a spreadsheet. I’m new to creating combo boxes, I don’t even know if this can be done.

    Any help would be great. “

  48. “How do you selecting something that starts with the letter t? I want to be able to do samething as hitting the letter t in the font drop down box? Any advice?
    How do I create a button to reset.
    RON Posted on: 31-12-1969
    I have Created a Sheet for people to enter specs, and it automaticly Creates, P/N’s and Descriptions. Well The people I have created this for, are not so Bright at times. So there for I want to create a Button that Reset’s all Combo-Box list’s back to the top selection.
    Basicly you have
    Colors
    Black
    Blue
    Green.

    They selevet blue, Then when they are finished, they just hit a button to Select color again.

    See I have about 15 Combo Boxs Created, so its hard for them to go back and select the Original Settings. Well I know that they can just close the File and open the Original, but They sometimes Save over the top of that one, so I have to constitenty send them the Original.
    So this is why I’d like to create a reset Combo-Boxes Button.

    I have tried recording macro’s, and it does not work with, Control Tool Box, or Forms.

    Anyone have any Sugestions?”

  49. i have a problem that combo box is not integrated with the work sheet and after entering data with the help of a combo box i cannot use filter on that column. how it can be resolved

Leave a Reply

Your email address will not be published. Required fields are marked *


three × 9 =

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>