Combo Box Drop Down for Excel Worksheet

Would you prefer a bigger font size for items in a data validation drop down list? Would you save typing time if the words were completed automatically, as you started typing them?

Not all your dreams can come true in Excel, but this tip might fulfill a few of them. Instead of using data validation, you can use a combo box on an Excel worksheet to show a drop down list. At the left, in the screen shot below, is a combo box, and you can compare it to the data validation drop down list on the right.


Unlike data validation, the combo box can be formatted and fine tuned. For example, in a combo box, you can:

  • change the font size
  • increase the number of visible rows in the list
  • use the AutoComplete feature to make data entry easier
  • always see the drop down arrow

Create a List

In this example, there is a named range -- MonthList -- with a list of the 12 month names. There are instructions on my Contextures website, for naming a range of cells.

This named range is used in the data validation list, and we will also use it as the source for the combo box.

Add a Combo Box to the Worksheet

To add a combo box on an Excel sheet, follow these steps:

  • On the Excel Ribbon, click the Developer tab (If you don't see a Developer tab, click here for instructions on how to add it)
  • In the Controls group, click Insert, then click the Combo Box in the ActiveX Controls section


  • Click on the worksheet, to create a default sized combo box (or drag with the pointer, to create a combo box in the size that you need)

Set the Combo Box Properties

After you create the combo box, follow these steps to change its properties:

  • Right-click on the combo box, and click Properties
  • To change the Font size, click the ... button at the right of the Font property


  • To add items to the combo box drop down list, type a range name or range reference in the List Fill box. In this example, we use the named range, MonthList. You could use an address instead, such as B2:B13


To change the number of visible rows in the drop down, increase or decrease the number in the ListRows property box. For a list of 12 months, we could change the setting to 12, so all the months can be seen, without scrolling through the list.


Activate the Combo Box

After you've made the changes to the combo box properties:

  • Click the Design Mode command on the Excel Ribbon's Developer tab, to exit Design Mode.


  • Click the drop down arrow on the combo box, to see the list, and click an item to select it.

Use a Combo Box With Data Validation

You can also use a combo box to supplement a data validation list, as described  on the Excel Data Validation Combo Box page on the Contextures website. In that example, there's one hidden combo box  on the worksheet, and it appears if you double-click a data validation cell.

Watch the Excel Combo Box Video

To see the steps for creating an Excel combo box with a drop down list, and changing its formatting, watch this short excel video tutorial.


You may also like...

29 Responses

  1. Gregory says:

    Combo boxes are much easier to use for lists than Data Validation. Great tutorial on how to set up a combo box.

    I’d like to point out to those who can’t find the Developer tab, that it’s not shown by default on the Ribbon. Right click anywhere on the Ribbon, select Customize the Ribbon, click the box beside Developer under Main Tabs, and click OK.

  2. Rick Rothstein (MVP - Excel) says:


    Are you perhaps using XL2010? I don’t have XL2010 installed yet, so I can’t check, but the procedure you outlined does not work in XL2007. For that version, it appears that you have to click the Office Button (the big round icon in the upper left corner of Excel’s window), click the Excel Options button on the dialog that appears, select Popular from the lefthand listing on the next dialog box that appears and put a check mark in the “Show Developer tab in the Ribbon” checkbox in the “Top options for working with Excel” panel on the righthand side. And, just to mention it for those using XL2003 and earlier version (that offer VB), you would click View/Toolbars/VisualBasic from the menu bar.

  3. Gregory says:

    I completely forgot they added new functionality in Excel 2010 to customize the Ribbon, and consequently the right-click toolbar has a new shortcut to “Customize the Ribbon” that’s not available in earlier versions of Excel. Good catch. My bad.

    And to be thorough, in Excel 2011 for Mac the Developer tab has to be turned on as well by using the “Customize Toolbars and Menus…” option from the View – Toolbars menu.

  4. marsha says:

    I’m using MS2007 and whatever I type does not save in the ListFillRange, thus I cannot assign the list name to use in the combobox. My combo box is in Workbook1, sheet 2; but my list is in Workbook 1, sheet 1. I have been unsuccessful even using a list in the same sheet. What am I missing? The video is great.

  5. marsha says:

    So I’m able to get the ListFillRange to work by typing in the range of cells within the same worksheet (e.g., J2:J2282), but this still does not solve the issue of a text name for a list. Further, I am trying to set up a data entry form, using the activex combobox so the data entry personnel can use the autocomplete feature. We will have thousands of records to enter and I am looking for a way to use the simplicity of data validation to copy the format to various cells but want to use the autocomplete and list features of the combobox. Additionally, I am using the lookup function to find the value selected in the combobox and return a cost value that is preset. If using the data validation, the lookup function works great, but I cannot figure out how to have this function apply to a combobox. Is there an easier way to tackle this? Thanks for any suggestions/help.

  6. Rob says:

    Thank you for an enlightening description of how to use this feature.

    I use the Mac version of Excel and can’t seem to find the way to adjust the Properties of the combo box. Right-click or control-click doesn’t bring up a Properties dialog box.

    Any suggestions?

  7. Michael says:

    Any way to publish the combo box to web page? When I tried on Excel 2007 the box appears on html, but it does not show the drop down values on clicking it.

  8. Clark Leslie says:

    Awsome site. You have helped me out greatly.
    I am fairly green to making these active x combo boxes but have figured out how to work it within one spreedsheet. My question now is how do I select a list fill range from another workbook? I can make it work with the vlookup formula to reference the data I want to retrieve but need to pull the info that is entered into the linked cell from another work book.

  9. Clark says:

    Found a way to reference the other work book. Just copied and past part of the vlookup formula. My delema now is if I can reference the other workbook without having it open?

  10. Wona says:

    One thing that would help is that most of the combo lists are used in more than one cell and sometimes copied down a column. when I am try to do that and make a choice in one then every single cell changes to the choice made in any one given cell so that the whole column populates the exact same data. I would like to know how to copy this down so that each cell populates a choice individual of the other cells.

  11. Mike says:

    Trouble is Vlookup doesn’t recognise “Combo Box’s” even though the numbers are nice and big.

    And “Data Validation” numbers are small as when you use the decrease size of sheet slider, with no way of increasing the font size.

    OMG found this “Spin Box’s” on “youtube”

    Just put beside your “Data Validation” keep them as they are and put in “Cell” address “b5” or what ever, follow video, OMG amazing better best fantastic.

    Came back special to share with You yes You : )

    This is a great site by the way

    Hope this helps someone out, “pay back” “pay forward” : )

    Thanks Mike

    New Zealand

  12. Bill P says:

    I have a combobox on a modeless userform (Excel 2010) that is populated from cells in the spreadsheet via the Sub UserForm_Initialize(). When I select an item in the combobox, the Sub ComboBox1_Change() is triggered and my code is executed correctly. My code sets various filters & such in the spreadsheet, depending on which item in the combobox was selected. At that point, I can maneuver around the spreadsheet doing edits, etc.
    There are times when I wish to select the same item in the combobox as previously selected to have the code in the Change Event run again. In this case, the Change event is not triggered since no change was made in the combobox.
    Is there a way to force the Change event to trigger when the same item is selected in the combobox? I would have thought that the Click event would work in this case but it does not. The Click event seems to function the same as the Change event.
    Any help yould be appreciated. Thank you,

  13. Rod says:

    This may be simple for some, but I’m scratching my head. What I have is a list of codes and their descriptions. I need to display the code and description for the user, but only store the code in the cell.

    code description
    10 New Entry
    20 Old Entry
    30 Delete Entry

    Only want the 10, or 20, or 30 saved when selected.

  14. Julie says:

    I have a involved spreadsheet that currently uses drop downs but think a combo box would be better suited. The first drop down is a mfg list the second drop down is the model#. There is current a data validation that narrows the second drop down based on the mfg you select. how do I link the second combo box to the first combo box?

  15. Joseph says:

    I am using this combobox with this VBA. In the example, only text were used. I am using this combobox with numbers. When I entered the numbers in the combobox drop down, I get this as a text instead of a number. How do I format this?


    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
    Cancel As Boolean)
    Dim str As String
    Dim cboTemp As OLEObject
    Dim ws As Worksheet
    Dim wsList As Worksheet
    Set ws = ActiveSheet
    Set wsList = Sheets(“Schedule”)

    Set cboTemp = ws.OLEObjects(“TempCombo”)
    On Error Resume Next
    With cboTemp
    ‘clear and hide the combo box
    .ListFillRange = “”
    .LinkedCell = “”
    .Visible = False
    End With
    On Error GoTo errHandler
    If Target.Validation.Type = 3 Then
    ‘if the cell contains a data validation list
    Cancel = True
    Application.EnableEvents = False
    ‘get the data validation formula
    str = Target.Validation.Formula1
    str = Right(str, Len(str) – 1)
    With cboTemp
    ‘show the combobox with the list
    .Visible = True
    .Left = Target.Left
    .Top = Target.Top
    .Width = Target.Width + 5
    .Height = Target.Height + 5
    .ListFillRange = str
    .LinkedCell = Target.Address
    End With
    ‘open the drop down list automatically

    End If

    Application.EnableEvents = True
    Exit Sub

    End Sub
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim str As String
    Dim cboTemp As OLEObject
    Dim ws As Worksheet
    Set ws = ActiveSheet
    Application.EnableEvents = False
    Application.ScreenUpdating = True

    If Application.CutCopyMode Then
    ‘allow copying and pasting on the worksheet
    GoTo errHandler
    End If

    Set cboTemp = ws.OLEObjects(“TempCombo”)
    On Error Resume Next
    With cboTemp
    .Top = 10
    .Left = 10
    .Width = 0
    .ListFillRange = “”
    .LinkedCell = “”
    .Visible = False
    .Value = “”
    End With

    Application.EnableEvents = True
    Exit Sub

    End Sub ‘====================================
    ‘Optional code to move to next cell if Tab or Enter are pressed
    ‘from code by Ted Lanham
    ‘***NOTE: if KeyDown causes problems, change to KeyUp

    Private Sub TempCombo_KeyDown(ByVal _
    KeyCode As MSForms.ReturnInteger, _
    ByVal Shift As Integer)
    Select Case KeyCode
    Case 9 ‘Tab
    ActiveCell.Offset(0, 1).Activate
    Case 13 ‘Enter
    ActiveCell.Offset(1, 0).Activate
    Case Else
    ‘do nothing
    End Select
    End Sub

  16. Jen says:

    I can’t access the properties menu after adding a combo box form control? I can access the properties when I first add it and unprotect the control. Once I click off of the control I can’t get back to properties. This is not the case when I add an active x control. Any suggestion?

  17. Bruce says:

    Take care when using these kinds of dynamic ActiveX objects and the .LinkedCell gets changed often.

    Running the type of code the Joseph has shown in the Worksheet_BeforeDoubleClick was a bit less stable for me. The .DropDown would often not appear.

    I had better luck using it within Worksheet_SelectionChange

    Joseph’s example did pretty much resolve one of the problems that I was having with my version and incorrect screen updating. When you clicked different cells reasonably rapidly you could leave orphan phantoms of the combobox, with or without the dropdown outlined, in the previously selected cells. Slowing things down a bit, as Joseph does, by first “frobbing” the box to the top-left, unlinking, removing the list, etc and then hiding it before going on to normal processing helped out quite a bit. Now he dropdown only occasionally fails to appear and I have no more phantoms in previously selected cells.

  18. Bruce says:

    I spoke a bit too soon. The issue with the screen updates was only temporarily resolved. It was likely dependant on how much else was going on at time time… like the vba source code stepping etc. More progress…

    If you find that you are still leaving ghosts around at the previous LinkedCell address when you move a ComboBox around the worksheet via vba code, then you might try bracketing the code that does not appear to work consistently with Application.ScreenUpdating = False and Application.ScreenUpdating = False as in…

    on Error GoTo ErrHandler
    Set OLETemp = ws.OLEObjects(“Test_select”)
    On Error Resume Next
    ‘ FROB away the previous ActiveX object
    With OLETemp
    .Top = 10
    .Left = 10
    .Width = 0
    .ListFillRange = “”
    .LinkedCell = “”
    .Value = “”
    Application.ScreenUpdating = False
    .Visible = False
    Application.ScreenUpdating = True
    End With


    Application.EnableEvents = False
    With OLETemp
    ‘show the combobox with the list appropriate to the column that we are in
    .LinkedCell = “=” + Target.Address
    .ListFillRange = “=” & tmp_list
    .Left = Target.Left
    .Top = Target.Top
    .Width = Target.Width + 15
    .Height = Target.Height + 4
    .Visible = True
    End With
    ‘ open the drop down list automatically
    Application.ScreenUpdating = False
    Application.ScreenUpdating = True
    Application.EnableEvents = True

    In the first case the application now flawlessly removes the display of the control at the previous cell. In the second case the dropdown appears almost all the time.

    I tried busy loops and calling Sleep in kernel32 to no avail.

  19. aa says:

    So ive made a calendar type thing almost exactly the same as this.

    But when i type something in to one month, it shows what i typed for all months even when i click the top left box to change months. Is there any way to make it stick only to that month???????

  20. Raghavendra Rao PV says:

    Thanks a lot. I was unable to load default values for ActiveX Combo. Now I entered the value for “ListFillRange” as “Calculations!$B$60:$B$66” without quotes and my problem is solved.


  21. Paula says:

    Using 2007, I’m able to create the ActiveX Combobox and my drop down works, no problem. What I am unable to do is format the box. The properties that appear are those of the worksheet and not the box. How do I get to the properties of the combobox itself and once there, how do I make it uniform in size throughout my document? Lastly, can I make it where the drop down arrow always shows (no matter whether the box is selected or not)?

    Thanks, Paula

  22. dockhem says:

    I want to add temporary combo box in all drop down data validation in my worksheet. But this code does not work. It says user defined type not defined.

  23. Graham says:

    Hi, I have 10 comboboxes on a sales form I am using and have created a command button to clear the sheet, and I want to clear the comboboxes as well – I used the following in the clear command sub-routine but get a runtime error “-2147467259 (80004005)” error on the first clear line.


    Would really appreciate your help on how to clear the 10 comboboxes please.

    Thanks a million

  24. Pierre says:

    Thanks a lot for this explanation, very usefull!
    I have one more question:
    My list is long, and when the autocompletion works the dropdown list displays the selected value at the bottom of the visible list.
    Do you know if it is possible to make it displaying the first matching value at the top?

    Here is an example based on monthes (thus with only 3 visible values) :
    text entered : ju

    displayed list today :

    expected display :

    Thanks again and best regards!

  25. J says:

    I have a series of data which contains booked and bookable hours for multiple departments. I want to create a graph with both booked and bookable hours for each corresponding dept. Can someone help point me to what this would be titled so I can find a how to video, or an excel workbook I could download and follow along, or easy to follow instructions? I just taught myself how to do this with one set of data, and with two side by side, but haven’t been able to figure out how to do with two. Help appreciated, this element of excel is still pretty new to me so I’m a little rusty.

  1. December 14, 2012

    […] of the AutoComplete Feature. The Contextures site has a code example and clear instructions…. Combo Box Drop Down for Excel Worksheet | Contextures Blog That article links to another that shows how to couple the ComboBox with […]

Leave a Reply

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