How to Create Drop-Down List in Multiple Columns in Excel

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.


Example

 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.

Applying Offset and Match Functions in Multiple Columns

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

Applying Offset and Match Functions in Multiple Columns to Create Drop-Down List in Multiple Columns in Excel

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

Applying Offset and Match Functions in Multiple Columns to to Create Drop-Down List in Multiple Columns in Excel

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

=OFFSET($D$5,1,MATCH($D$12,$D$5:$F$5,0)-1,5,1)

  • 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


Example

 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.

Create Drop-Down List in Multiple Columns in Excel Using dependent Drop-Down List

  • 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.
=INDIRECT(D11)

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.

Creating a Dependent Drop-Down List

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

Create Drop-Down List in Multiple Columns in Excel Using dependent Drop-Down List

  • 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.
=INDIRECT(E11)

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.

Create Drop-Down List in Multiple Columns in Excel Using dependent Drop-Down List

  • A dependent drop-down list is created.

Create Drop-Down List in Multiple Columns in Excel Using dependent Drop-Down List

📕 Read More: 5 Ways to Link a Cell Value with a Drop-Down List in Excel


Example

 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.

Generating Independent Drop-Down List to to Create Drop Down List in Multiple Columns

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

Generating Independent Drop-Down List to to Create Drop Down List in Multiple Columns

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

Generating Independent Drop-Down List

  • A drop-down option has been created in the E11 cell beside the Toyota Model option.

Create Drop-Down List in Multiple Columns in Excel Using Independent Drop-Down List

  • 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$8Generating Independent Drop-Down List to to Create Drop Down List in Multiple Columns
  • For Ford models, we created a drop-down list-

Create Drop-Down List in Multiple Columns in Excel Using Independent 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.

Create Drop-Down List in Multiple Columns in Excel Using Independent Drop-Down List


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


Related Articles

(Visited 128 times, 1 visits today)
Lamisa Musharrat

Lamisa Musharrat

Hey there! I am Lamisa Musharrat, a Marine Engineer and a Content Developer. I love to help people by writing articles for them. Hope you will enjoy my blog.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo