Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Create a Drop Down List From Another Sheet in Excel

This tutorial will demonstrate how to create a drop-down list from another sheet in Excel. When you are working with a large database and you need to pick a specific item from a list, a drop-down list can help you in this situation. Using a drop-down list you can select any specific data in seconds. You can make your drop-down list from different worksheets too. Today, in this article we will discuss how to create a drop-down list from another sheet in Excel.


Download Practice Workbook

Download this practice sheet to practice while you are reading this article.


2 Effective Methods to Create an Excel Drop-Down List From Another Sheet

We’ll use a sample dataset overview as an example in Excel to understand easily. If you follow the steps correctly, you should learn how to create a drop-down list from another sheet in Excel on your own. The methods are:


1. Excel Drop-Down List from A Single Worksheet

In this case, our goal is to create a drop-down list from another sheet in Excel. To do so, we will use a single worksheet and follow the below steps of this method.

  • First, we are given a data range of the “Name” of some students and their “Subject” and “Department”. Name this worksheet “Source Data”. We need to make a drop-down list from these data in a different worksheet.

Dataset to Create excel drop down list from another sheet

  • Next, create a new table in a different worksheet containing columns “Name”, “Subject”, and “Department” where we will create the drop-down list.

Using Another Worksheet to Create an Excel Drop-Down List From Another Sheet

  • Now, we will make a drop-down list of the names. Select cell “B5”, go to “Data” and click on the “Data Validation” option. In Short: B5→Data→Data Validation.

Using Data Validation to Create an Excel Drop-Down List From Another Sheet

  • After that, a new window will pop up that shows how we want to have our drop-down list. Now click on this icon and select the “List” option.

Choosing List Option to Create an Excel Drop-Down List From Another Sheet

  • Subsequently, make sure “Ignore Blank” and “In-cell Dropdown” are checked. Now click on this source icon to select your drop-down list of data from a different sheet.

Ticking Desired Options to Create an Excel Drop-Down List From Another Sheet

  • Moreover, a new window will appear on the screen where we have to insert our drop-down data. To do this, click on “Source Data” and go to this different sheet.

Selecting Data Range to Create an Excel Drop-Down List From Another Sheet

  • In addition, we are now on our “Source Data” sheet. Select the data from the column “Name” and click on the data validation icon to confirm the selection.

Choosing Data Range from another worksheet to Create an Excel Drop-Down List From Another Sheet

  • Afterward, that our drop-down data is ready, click “OK” to confirm.

Fulfilling The Data Source option to Create an Excel Drop-Down List From Another Sheet

  • Furthermore, our drop-down list is ready. Click on this icon icon2  to show the list.

Result for Column Name to Create an Excel Drop-Down List From Another Sheet

  • Then do the same steps for the rest of the two lists. The “Drop-Down” list for the “Subject” column is this.

 

  • Again, do the same steps to find the “Department” column in the drop-down list and you will get the following result.

Thus, you have created a drop-down list from another sheet in Excel for a single worksheet.

Read more: How to Make a Drop-Down List in Excel (Independent and Dependent)


2. Excel Drop-Down List from Multiple Worksheets

Now, we want to create a drop-down list from another sheet in Excel. This time, we want to do this for multiple worksheets and to do so we have to follow the below steps of this method.                      

  • Firstly, let’s consider that we are given some data tables containing the “Name”, “Subject” and “Department” of some students in different sheets. We have to make drop-down lists in a new worksheet from those different sheets. For the “Name” data, the “Data 1” worksheet is created.

Dataset one to Create an Excel Drop-Down List From Another Sheet

  • Again, for the “Subject” data create another worksheet, “Data 2”.

Dataset one to Create an Excel Drop-Down List From Another Sheet

  • And for the “Department” column, create another worksheet, “Data 3”.

  • Then, make a new table in a different worksheet where the drop-down lists of these data will be created.
  • Following this, we will first make a drop-down list for the “Name” column. Select the cell range, go to “Data” and click on the Data Validation” option.

  • Simultaneously, a new window appeared, and click on this icon icon2 to open a chart where different styles of the drop-down lists are given. Select “List”. 
  • Concurrently, make sure to check “Ignore Blank” and “In-Cell Dropdown”. Click on this source icon to continue.

  • At this point, another new window popped out where we have to insert the data for our drop-down list. Go to the “Data 1” sheet to input data. Select the data and click on the icon to the right in the Data Validation window to continue.

  • Thereafter, in the new window, the final selection is given. Click “OK” to proceed.

  • In addition, we have got our drop-down list for the “Name”.

  • Next, do the same for the “Subject” and “Department” columns.
  • Lastly, the final selection for the “Subject” column is similar to the below image.

  • Lastly, the drop-down list for the “Subject” column will be like the following image.

  • And after collecting data from different sheets, after the drop-down list of the “Department” column, you will get the final result.

Hence, you have created a drop-down list from another sheet in Excel for multiple worksheets.


Things to Remember

  • To avoid errors, remember to check “Ignore Blank” and “In-cell Dropdown”.
  • If you want the same drop-down list to appear in multiple cells, select those cells and follow the mentioned procedure.

Conclusion 

Henceforth, follow the above-described methods. These methods will help you to create a drop-down list from another sheet in Excel. We will be glad to know if you can execute the task in any other way. Follow the ExcelDemy website for more articles like this. Please feel free to add comments, suggestions, or questions in the section below if you have any confusion or face any problems. We will try our best to solve the problem or work with your suggestions.


Further Readings

Asikul Himel
We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo