This Excel tutorial shows you the steps to creating list box items. In our case, for all the months in a year.
Creating List Box Items
Creating list box items in Excel is an excellent way to improve the user experience. It provides them with the ability to control what they see on the screen.
Before you start, you will need to show the developer tab in Excel.
Data for the Tutorial
Feel free to type in the data below, which will serve as the data we will use for our list box. Otherwise, you will just have to follow along.
Steps to Creating a List Box
Follow the steps below to create a list box in Excel.
Give Your Data a Name
Select all of the months, from A1 to A13.
Click in the Name Box and give your selected data a name. The Name Box is on the far left of the formula toolbar. We gave our data the name ref_Months.
Create a List Box Object
- Click Developer from the toolbar, to show the developer ribbon.
- Under the Controls group, click Insert.
- Select the Combo Box form control, second item from the top.
Click somewhere on the sheet. You now have a list box on your sheet.
Use the sizing nodes to show more of the control. If you have problems showing the nodes, try holding the control (Ctrl) key while clicking on it (Ctrl-Click).
Populate the List Box
Right-click on the list box control and select Format Control… from the shortcut menu. The Format Control dialog box appears.
- For Input range, enter the name you used to define your data. In our case, ref_Months. You must put an “=” equal sign before the name.
- For Cell link, click on one of the cells on your sheet. This cell will display the index number of our selection.
- Click OK.
Click on the down arrow on the list box. You should see all your data in the control. You can put this control anywhere on your sheet.
Test the Control
Remember we assigned F2 as our cell link? Well, that cell link should show us the index number of our selection. Let’s see if it works.
We select April, the 4th item from the list. And, as you see, “4” appears in cell F2. Hum, now you need to figure out how to convert that “4” to “April.”
Congratulations! Your combo list box control is ready to go. If you want to see an example of a list box in action, check out our Pie Chart Tool.
Feel free to reach out to us with any questions regarding this tutorial or any other training program we provide.