Lamisa Musharrat
You may have seen drop-down lists in forms or websites. No doubt, it is a widespread and helpful feature in any application. This article will teach you how to Create a Drop-down List in Multiple Columns Using Excel.
📁 Download Excel File
Download the Excel file we used to create this article so you can practice.
Learn to Create Drop Down List in Multiple Columns with These 3 Suitable Examples
You can use the three possible ways to make an Excel drop-down list. Here, we have used a dataset containing the data of some car companies’ names, their models, and the available colors of some models.
1. Applying Offset and Match Functions in Multiple Columns
In this first method, we want to discuss creating a drop-down list using the Excel OFFSET function. Follow the steps below.
⬇️⬇️ STEPS ⬇️⬇️
- In any location of your worksheet, create a table containing the option that you want to create a drop-down list. For instance, we created a table containing columns for the company name and car model.
- Select the cell where you want to see the options for the first component. For example, we have selected cell D12, where we will show options for companies.
- From the ribbon, click in the Data tab, go to the Data Tools group, and select Data Validation from there.
- A dialogue box named Data Validation will appear, and from the drop-down bar of Allow, select the list option.
- Then, a source box will appear; click here and select the range you want to see as the option in your drop-down menu. For example, we want a car company option, so we selected cells B6 to B8 or type ($B$6:$B$8).
- Then click OK.
- A drop-down list containing your desired options (here, the car companies’ options) will be created.
- Now we want to create a drop-down list again for Car Models. Follow the same process, select the cell, click in the Data tab, go to the Data Tools group, and select Data Validation from there. A dialogue box named Data Validation will appear and select the list from the drop-down bar of Allow.
- From the Source box, apply a formula using the Offset and the Match function.
The function that we have entered is given below.
- Now you can see a drop-down list.
🔨 Formula Breakdown
OFFSET($D$5,1,MATCH($D$12,$D$5:$F$5,0)-1,5,1)
👉 Here, $D$5 is the reference of the cell.
👉 1 denotes the row as every time we want to move it, 1 row down.
👉 MATCH($D$12, $D$5:$F$5,0)-1 is for the column, and we used the MATCH function to make the column dynamic. $D$12 is the Lookup value and $D$5:$F$5 is Lookup array. The [match_type] is EXACT.
👉 5 is for [height] of each row, and there are 5 rows also [width] is for each column.
📕 Read More: 3 Ways to Create List from Range in Excel
2. Creating a Dependent Drop-Down List
While creating a dependent drop-down list in multiple columns in Excel, we will use the INDIRECT function.
Here, we will select a drop-down list for the models’ Company Name, Model Name, and Color. Follow the steps below to learn how to do this.
⬇️⬇️ STEPS ⬇️⬇️
- To make a list for Company Name, select the cell where you want to see the options. For example, we have chosen cell D11, where the options of companies will appear.
- After that, click the Data tab, go to the Data Tools group, and select Data Validation.
- A Data Validation window will appear and select the list option from the drop-down bar of Allow.
- In the Source box, enter the source range here; we have entered $B$6:$B$8, and click OK to make a list.
- Now we will use Name Manager to create a drop-down option for car models. So, select the columns Toyota, Ford, and Ferrari. Next, go to the Formulas tab, click on the Defines Names group, and click Create Selection.
- A dialogue box will appear titled Create Names from Selection. Tick on the Top row and click OK.
- Now select the cell where you want to see your list options. So, we chose cell E11. Then again, from the Data tab, go to the Data Tools group and select Data Validation from there.
- A dialogue box named Data Validation will appear and select the list from the drop-down bar of Allow. Then, on the source box, type the following formula.
Now, select OK, and it will create a dependent drop-down list.
Here, D11 refers to the named range of selected companies and thus lists all the items of that category.
We selected the option ford in the company so we can see the options of Ford in the Car Model option. Thus the Car Model option is dependent on the data of cell D11.
- The next step is to follow the same process as before. Make another dependent list depending on the values of cell E11 or Car Model.
To do so, select the columns Corolla, Ranger, and LaFerrari. Next, go to the Formulas tab, click on the Defines Names group, and choose Create Selection.
- A dialogue box will appear titled Create Names from Selection. Tick on the Top row And click OK.
- Again, select the cell where you want to see your list options. So, we picked cell F11. Then again, from the Data tab, go to the Data Tools group and choose Data Validation from there.
- A dialogue box named Data Validation will appear, and from the drop-down bar of Allow, select the list Then, on the source box, type the following formula.
Now, select OK, and it will create a dependent list.
Here, E11 refers to the named range of selected models. Thus, it lists all the items of that category on which the colors depend.
- A dependent drop-down list is created.
📕 Read More: 5 Ways to Link a Cell Value with a Drop-Down List in Excel
3. Generating Independent Drop-Down List
Do you want to generate an independent drop-down list in multiple columns in Excel? Follow the steps below.
Here we have the car companies’ names and their models. We are using these columns to make our independent drop-down list.
⬇️⬇️ STEPS ⬇️⬇️
- Create a table in your worksheet where you want to generate the drop-down list.
- Select the cell where you want to see the options for the first component. For example, we have selected cell E10, where we will show the names of the companies.
- From the Data tab, go to the Data Tools group and select Data Validation.
- A dialogue box named Data Validation will appear, and from the drop-down bar of Allow, select the list option.
- Next, a source box will appear, click here and select the range you want to see as the option in your drop-down menu. For example, we want a car company option, so we set cells B6 to B8 ($B$6:$B$8). Then click OK.
- As a result, it will create a drop-down list containing your desired options.
- We have to make three other drop-down lists just following the same procedure.
- Here, for the Toyota model, we have selected cell E11, where we want to see the drop-down list. Then from the Data tab, we selected Data Validation.
- Again a dialogue box titled Data Validation will open, and we have to select the list option from there.
- Then in the Source box, we have selected the range ($D$6:$D$8) of the Toyota Model; you can choose the content according to your desire of options you want to see as dropdown menus. Finally, press OK to create the drop-down list.
- A drop-down option has been created in the E11 cell beside the Toyota Model option.
- Then, you have to follow the same procedure for the rest of your options.
- But you only have to change the source option for each case and select different ranges.
- Here for the Ford model, we entered the source as $E$6:$E$8.
- For Ford models, we created a drop-down list-
- Again, similarly, for the Ferrari model, we entered the source as $F$6:$F$8.
- Therefore, we finally got this as our drop-down option in Ferrari Model.
📝 Takeaway from This Article
📌 You can get an idea about the possible ways of creating a drop-down list in multiple columns.
📌 You can now use Data Validation to create any drop-down list option.
📌 Besides Data Validation, you now know how to use the functions.
Conclusion
Creating a drop-down list in multiple columns in Excel is easy if you follow the abovementioned process. Please leave a comment if you have any suggestions or questions. Also, don’t forget to visit our Excelden page to enhance your Excel-related knowledge.
