Excel ComboBox: How to create a combo box
With a ComboBox in an Excel worksheet, you can make it easier for users to enter values. Such a combo box can be used to select a value from a dropdown list. We show you how to use a ComboBox.
ComboBox in Excel: Activate developer tools
This guide was created for Excel 2016. In other versions, the menu navigation may differ slightly.
- In order to create a combo box, you need to activate the developer tools in Excel. Open Excel and click File> Options.
- Select "Customize Ribbon" and look for "Developer Tools" in the list on the right.
- Activate the developer tools using the corresponding button.
- Confirm with OK".
Create combo box in Excel
In Excel, combo boxes can create as a form control or as an ActiveX control. The ActiveX control is more flexible - text formatting and appearance can be customized using programming commands. In most cases, a simple form control is sufficient.
- Before you insert the combo box, you need input values that should appear in the list. Write them one below the other or next to each other in a free area on your Excel sheet.
- Go to the "Developer Tools" tab and click "Paste".
- Now select the combo box, either as an ActiveX control or as a form control.
- Draw the box anywhere on the Excel sheet.
Form control: Format ComboBox
You can format a ComboBox created as a form control as follows:
- Right-click the item and choose Format Control.
- Switch to the "Control" tab.
- Click on the arrow at "Input area" and mark the area in which your values are located.
- You can select any cell as the cell link. Depending on the selection, a number between one and the number of selection elements appears in this. You can use this cell to query the selected value and use it for other formulas and functions.
- With "Dropdown cells" you can specify the desired number of cells in the dropdown list. If you select fewer cells than there are selection elements, the box becomes scrollable.
- Confirm with OK".
ActiveX control: Format ComboBox
You basically format a combo box as an ActiveX control in exactly the same way as a form control combo box. Right click on it and select "Properties". You can now use some formatting. Once you have made all the settings, close the window and exit the design mode in the menu bar. The main settings are as follows:
- ListFillRange: Here you specify the area in which the selection values are located. For example, specify a range from A1 to A10 with "A1: A10".
- LinkedCell: If necessary, enter a cell link as described in the previous paragraph.
- ListRows: Here you enter the number of desired dropdown cells.
- Font: If you want a different font, you can set it here.
- ForeColor: Select the desired font color here.
- BackColor: Here you can set a background color.
These instructions were created with Excel 2016. In other versions, the menu navigation may differ slightly. On the next page we will show you how to create a slider in Excel.