List box in Excel (Table of Contents)
List Box in Excel
In this tutorial, I will discuss how to create a list box in Excel and use the list box.
The list box is very useful to do data entry. We can select the data from the list box and make an entry to the worksheet.
List Box includes pre-determined values in it like our data validation list. This will restrict the users to make their own entry to the worksheet.
List Box is located under Developer Tab in excel. If you are not a VBA macro user you may not see this DEVELOPER tab in your excel.
Follow below steps to unleash Developer Tab.
If the Developer tab is not showing then follow the below steps to enable the Developer tab.
- Go to FILE option (top of the left-hand side of the excel).
- Now go to Options.
- Go to Custom Ribbon and click on Developer tab check-box.
- Once the Developer Tab is enabled you can see on your excel ribbon.
How to Create List box in Excel?
Follow below steps to insert List box in excel.
Step 1: Go to Developer Tab > Controls > Insert > Form Controls > List Box.
Step 2: Click on List Box and draw in the worksheet than Right click on the List Box and select the option Format Control.
Step 3: Create a month list in column A from A1 to A12.
Step 5: Once you have selected Format Control, it will open the below dialog box, go to the Control tab, in the input range select the month lists from A1 to A10. In the cell link, give a link to the cell B1.
We have linked our cell to B1. Once the first value has selected the cell B1 will show 1. Similarly, if you select April it will show 4 in the cell B1.
Examples of List Box in Excel
Let’s look at a few examples of using Lise Box in Excel.
Example #1 – List Box with Vlookup Formula
Now we will look at the way of using List Box in excel.
Assume you have a salary data month-wise from A2 to A13. Based on the selection made from the list it has to show the value for the selected month.
Step 1: Draw a List Box from the developer tab and create a list of months.
Step 2: I have given a cell link to H2. In G2 and G3, create a list like this.
Step 3: Apply VLOOKUP formula in the cell H3 to get the salary data from the list.
Step 4: Right now it is showing error as #N/A. select any of the months it will show you the salary data for that month.
I have selected March month and the number for March month is 3 and VLOOKUP showing the value for number 3 from the table.
Example #2 – Custom Chart with List Box
In this example, I will explain how to create the custom chart using List Box. I will take the same example that I have used previously.
Step 1: Create a table like this.
Step 2: Insert List Box from the Developer tab. Go to Format Control give link to month list and cell link to F1.
Step 3: Apply IF formula in newly created table.
I have applied IF condition to the table. If I select the month March the value in the cell F1 will show the number 3 because March is the third value in the list. Similarly, it will show 4 for April, 10 for October and 12 for December.
IF condition checks if the value in the cell F1 is greater than equal to 4 it will show for the value for the first 4 months. If the value in the cell F1 is greater than equal to 6 it will show the value for the first 6 months only.
Step 4: Now apply chart for the modified table with IF condition.
Step 5: Now it is showing the empty graph. Select the month to see the magic. I have selected the month May, that is why it is showing the graph for the first 5 months.
Things to Remember
- There is one more List Box under Active X Control in excel. This has mainly used in VBA macros.
- Cell link is to indicate which item from the list has selected.
- We can control the user to enter the data by using List Box.
- We can avoid wrong data entry by using List Box.
Recommended Articles
This has been a guide to the List box in Excel. Here we discuss the List box in Excel and How to create the List box in Excel along with practical examples and downloadable excel template. You can also go through our other suggested articles –
