Home > Validation > Drop Downs > List Types

Excel Drop Down List Types

Most drop down lists in Excel are created with the data validation feature. The font in those drop down lists can't be changed, and they only show 8 items at a time. See two other types of Excel drop down lists, that you can use, and how they compare to data validation drop downs

Author: Debra Dalgleish

Excel Drop Down List Types

When I'm making drop down lists in Excel, most of the time I use the data validation feature. That creates an in-cell drop down, like the list of months in the screen shot below.

Drop Down List Font Size

However, you can't change the font size in those drop down lists -- the list is always in 8 point Tahoma font. If you reduce the zoom level on the worksheet, that small font can be almost impossible to read!

For example, here's the same drop down list, with the worksheet zoom setting at 75%.

Drop Downs Without Data Validation

In the video below, you'll see two other types of Excel drop down lists, that you can use, instead of data validation drop downs. Further down the page there are written steps for creating the 3 types of drop down lists in Excel:

1. Data Validation
2. Form Control Combo Box
3. ActiveX Combo Box

Video: Compare 3 Excel Drop Down List Types

To see a demonstration of 3 types of Excel drop down lists, you can watch this Drop Down Lists video.

There are written steps below the video.

Video Timeline

• 0:00 Introduction
• 0:20 Data Validation Drop Down
• o:58 Form Control Combo Box
• 1:28 Format the Combo Box
• 2:21 ActiveX Combo Box
• 2:42 Set Combo Box Properties
• 3:48 Exit Design Mode
• 4:20 Compare Drop Down Features

1) Data Validation Drop Down

Data validation drop down lists are helpful for data entry, and it's easy to add them to an Excel worksheet.

Data validation is a great feature, and you can even create dependent drop down lists, that show a customized list of items, based on what you selected in another cell on the worksheet.

However, data validation drop down lists have the following limitations:

• font size can’t be changed
• maximum of 8 rows are visible at a time
• only the active cell shows a drop down arrow

You can’t change those settings, but you can colour the data entry cells, to make them obvious to the worksheet users. For example, in the screen shot below, I used green fill colour in cell C3, for the data validation list.

Video: Make a Data Validation Drop Down List

To make data entry easy, and help prevent errors, make an in cell drop down list in Excel. First, create a list of items in an Excel table. Then use Excel data validation to make the drop down list with those items.

Video: Show Developer Tab on Excel Ribbon

To add the other two types of drop down lists, the Form Control combo box, or the ActiveX combo box, you'll use the Developer tab on the Excel Ribbon.

If you don't see the Developer tab on your Excel Ribbon, watch this short video to see how to add it..

It just tabkes a couple of quick steps, and there are written instructions below the video.

To add Developer tab in Excel Ribbon

To show the Developer tab, follow these steps:

1. Right-click on the Ribbon, and click Customize Ribbon
2. Add a check mark beside Developer, in the list at the right.
3. Click OK, to close the Excel Options window.

2) Make a Form Control Combo Box

Instead of using data validation, you could use a Form Control Combo Box. It gives you a bit more control over the appearance of the drop down list.

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

Features

• You can set the number of visible rows in the drop down list. For example, in the screen shot below, I changed the number of drop down lines to 12, so all the months are visible.
• You can link the combo box to a cell, so the selected item number appears on the worksheet.
• The drop down arrow is always visible, so the Form Control combo box is easy for users to find on the worksheet.

Limitations

• The font size can’t be changed, so the list items would be hard to read on a zoomed worksheet.
• You can’t type in the combo box -- you have to click the arrow, then select an item from the list.
• The selected item’s index number (its position in the source list) is entered in the linked cell, instead of the selected item's text value

Add Form Control Combo Box

o add a Form Control combo box on a worksheet, 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 Form 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

Format Control Dialog Box

To adjust the combo box settings, follow these steps:

• Right-click the combo box, and click Format Control
• The Format Control dialog box opens, with the Control tab active
• In the Input Range box, enter location of the drop down list items -- named range, or cell range
  • Note: The list must be down a column (vertical).
  • Values from that range will appear in the drop down list
• In the Cell Link box, type a cell address, or click on a worksheet cell
  • That cell will show a number for the selected item in the drop down list (the item's position in the Input Range)
• In the Drop Down Lines box, type the number of visible rows for the drop down list
  • To see other rows, you can scroll up or down

Formula to Show Selected Item

To show the item name, instead of the item number, you can use an INDEX function in another cell.

In this example, the following INDEX formula is in cell C5:

• =INDEX(MonthList,D5)

How the Formula Works

• Cell D5 is linked to the Combo Box
• May is selected, so cell D5 shows the list position for that month -- 5
• INDEX function return the 5th item in the MonthList range -- May

3) Make an ActiveX Combo Box

Another drop down list option is to use an ActiveX combo box. This is similar to the Forms Control combo box, but has more properties that you can adjust.

Features

• Many properties can be adjusted, such as font, font size, and number of list rows
• You can type in the combo box, and the text autocompletes as you type, finding the first matching item in the list.

Limitations

• ActiveX cannot be used with Excel for Mac, so be sure that your clients or co-workers are using the Windows version of Excel

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

Video: Worksheet Combo Box

This short video shows you how to create an ActiveX combo box with a drop down list, and then change the combo box formatting. There are written steps below the video, and on the Worksheet Combo Box page.

Add ActiveX Combo Box

To add an ActiveX 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

Change Combo Box Properties

After you insert an ActiveX combo box, right-click the combo box, and click Properties.

Adjust Combo Box Properties

In the Properties window, you can adjust any of the settings in the list. For example, in the screen shot below, I made the following changes:

• Set the font to Cambria, and selected 14 pt font size
• Linked combo boxe to worksheet cell C7
• Entered MonthList as the ListFillRange (source data for drop down list)
• Increased ListRows to 12, so all months will show

There are more details and examples on the Worksheet Combo Box page.

Download Sample File

Drop Down Types: To test the different drop down list types, you can download the Excel Drop Down Lists sample workbook. The file is in xlsx format, and is zipped. There are no macros in the file.