Contextures

Home > Validation > Drop Downs > Combo Box

Excel Worksheet Combo boxes

On a worksheet, you can add combo boxes to make data entry easier. These are similar to data validation drop down lists, but have more features and flexibility. For example, they autocomplete, when you start typing in them, and the font size can be changed.

Tip: See how to automate these combo boxes on the Worksheet Combo Box Macros page.

ListFillRange wiill not accept named range

NOTE: For UserForm combo boxes, see this page.

Video: Worksheet Combo Box

Use a combo box on an Excel worksheet to show a drop down list. This short Excel video shows you how to create an Excel combo box with a drop down list, and change the combo box formatting.

There are written instructions below the video.

Add a Combo Box

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

  1. 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)
  2. In the Controls group, click Insert
  3. In the ActiveX Controls section, click Combo Box
  4. Click on the worksheet, to create a default sized combo box, or drag the pointer, to create a combo box in a specific size

add combo box to worksheet

View the Properties

You can change the properties, to adjust the font size, number of visible rows, and other settings.

NOTE: Changing the size or location properties of a Combo Box can wipe out Excel's Undo stack, so you won't be able to undo any steps you've previously taken

To see the properties:

  1. Right-click the combo box, and click Properties
  2. If the popup menu doesn't appear, click the Developer tab, and click Design Mode, then right-click on the combo box.

combo box properties

Show a List

To show a list in the combo box, you can use a named range, or the address for a worksheet range. In this example, we'll use a named range -- MonthList.

NOTE: For names based on an Excel Table column, see the additional information in the next section.

To add the list:

  1. In the Properties window, scroll down to find the LinkedCell property
  2. Click in the ListFillRange box
  3. Type a range name or range reference -- you can't click on a range to select it.
  4. ListFillRange with named range

Show a List in Excel 2013

In Excel 2013 and later, in the ListFillRange property for a worksheet ActiveX combo box, you cannot change the property to use a named range that is based directly on an Excel Table.

In the screen shot below, there is a table named tblProducts. To create a named range with the product names:

  1. Select all the product names in the table (do not include the heading cell)
  2. Click in the Name Box, and type a one-word name for the range -- ProdList
  3. Press the Enter key, to complete the name.

create named range from table

If you enter this name in the combo box's ListFillRange property, the name will not be accepted -- the property will be cleared, and the list of names won't appear in the combo box drop down.

ListFillRange wiill not accept named range

To use this list in the combo box, you can create a second name, based on the first name:

  1. On the Excel Ribbon, click the Formulas tab, and click Define Name
  2. In the New Name window, type a one-word name -- ProdListB
  3. Click in the Refers to box, and press the F3 key
  4. In the Paste Name window, click on the table-based name -- ProdList -- and click OK
  5. Click OK to complete the name

create 2nd named range

Then, in the Combo Box, use the second name -- ProdListB -- in the ListFillRange property, and it will be accepted, because it is not directly based on a table.

ListFillRange wiill not accept named range

Link to a Cell

When a selection is made in a combo box, you can display the selected item in a cell on the worksheet.

To link the combo box to a specific cell:

  1. In the Properties window, scroll down to find the LinkedCell property
  2. Click in the LinkedCell box
  3. Type a range name or cell address -- you can't click on a range to select it.

linked cell on worksheet

Change the Font Settings

To make the list easier to read, to can select a different font and font size.

  1. In the Properties window, scroll down to find the Font property
  2. Click in the Font property box
  3. Click the ... button that appears at the right side of the Font box.

    font properties button

  4. Select a font and font size, then click OK

change font settings

Set the Number of Rows

By default a new combo box shows 8 rows, and you can scroll to see additional rows.

You can change the number of rows, so that more are visible. In this example, the list shows month names, so we can set the number of rows to 12, so all the month names are visible, without scrolling.

  1. In the Properties window, scroll down to find the ListRows property
  2. Click in the ListRows box
  3. Type a number, 12 in this example

linked cell on worksheet

Get the Sample File

To see how the combo box works, you can download the sample file. The zipped file is in xlsx format (Excel 2007 and later), and does not contains macros.

More Tutorials

Worksheet Combo Box Macros

Basic Excel UserForm

Create an Excel UserForm with Combo Boxes

Delete or Add Worksheet Objects

UserForm Combo Boxes

Naming a Range

Last updated: May 10, 2023 3:35 PM