Home > Validation > Drop Downs > List Types Excel Drop Down List TypesMost 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 TypesWhen 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 SizeHowever, 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 ValidationIn 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 Drop DownData 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:
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 ListTo 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 RibbonTo 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 RibbonTo show the Developer tab, follow these steps:
|
2) Make a Form Control Combo BoxInstead 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
Limitations
Add Form Control Combo Boxo add a Form Control combo box on a worksheet, follow these steps:
|
Format Control Dialog BoxTo adjust the combo box settings, follow these steps:
Formula to Show Selected ItemTo 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:
How the Formula Works
|
3) Make an ActiveX Combo BoxAnother 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
Limitations
Tip: See how to automate these combo boxes on the Worksheet Combo Box Macros page. Video: Worksheet Combo BoxThis 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 BoxTo add an ActiveX combo box on an Excel sheet, follow these steps:
|
Change Combo Box PropertiesAfter you insert an ActiveX combo box, right-click the combo box, and click Properties. Adjust Combo Box PropertiesIn 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:
There are more details and examples on the Worksheet Combo Box page. |
Download Sample FileDrop 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. |
Data Validation with Combo Box
Last updated: March 30, 2023 12:16 PM