Creating List Box Items in Excel – Tutorial

Creating List Box Items in Excel.

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.

Data for the tutorial.
Data for our combo list box.

Steps to Creating a List Box

Follow the steps below to create a list box in Excel.

Give Your Data a Name

Select the cells you would like to use in your list box.
Select the cells you would like to use in your list box.

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

Create the control to populate the items.
Create the list box control.
  1. Click Developer from the toolbar, to show the developer ribbon.
  2. Under the Controls group, click Insert.
  3. Select the Combo Box form control, second item from the top.
Spreadsheet with list box items control.
Excel spreadsheet with list box control.

Click somewhere on the sheet. You now have a list box on your sheet.

Excel tutorial - Resized list box control.
Resized list box control.

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

Excel tutorial - Accessing formatting properties for the list box control.
Accessing formatting properties for the list box control.

Right-click on the list box control and select Format Control… from the shortcut menu. The Format Control dialog box appears.

Excel tutorial - Assigning the data and linking to a cell for your list box.
Assigning the data and linking to a cell for your list box.
  1. 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.
  2. For Cell link, click on one of the cells on your sheet. This cell will display the index number of our selection.
  3. Click OK.
Excel tutorial - Showing the items in your list box control.
Showing the items in your list box control.

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.

List box control showing the active item from a selection.
List box control showing the active item from a selection.

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.”

Success!

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.


Questions

Feel free to reach out to us with any questions regarding this tutorial or any other training program we provide.

training@techronology.com


Tutorials

Techronology Home