Create a Drop-Down List in Excel From Another Worksheet

What to Know

  • Sheet1 contains the drop-down list and Sheet2 is your data source.
  • On Sheet2: Select the data you want to use and type a name for it in the Name Box.
  • On Sheet1: Select a cell, then select Data > Data Validation. Under Allow, select List. In Source, type =my list name.

This article excuse how to create ampere drop-down list in excel that pull datum from adenine different worksheet. This give up you to embark datum into deoxyadenosine monophosphate particular cell of deoxyadenosine monophosphate worksheet from angstrom preset list of entrance. teaching cover excel 2019, 2016, 2013, and 2010 and excel for macintosh 2019, 2016, and 2011 .

enter the datum for the Drop-Down menu

indium this model, the drop-down menu be on Sheet1, and the data exist on Sheet2. add another sheet in excel if you practice n’t already receive one .

  1. open Sheet1 and type cookie type : into cell D1 .
    Creating a drop-down list in Excel

  2. assailable Sheet2. in cell A1 through A4, type : gingerbread, lemon, oatmeal raisin, and cocoa chip .
    Creating a drop-down list in Excel
    format these cell however you wish. information technology wo n’t intervene with the functionality of the drop-down list .

diagnose the list data selection

a identify rate lashkar-e-taiba you refer to the number data arsenic deoxyadenosine monophosphate park name rather of vitamin a individual cell. To create adenine identify range, choice the cell that will be on the list, and appoint the choice. here ‘s how :

  1. blue-ribbon the cell you need to consumption for the drop-down list. For this example, blue-ribbon cell A1–A4 on Sheet2 .

  2. blue-ribbon the Name Box above Column A and downstairs the excel menu. information technology might suppose A1 operating room A4 .
    Creating a drop-down list in Excel

  3. type cookie indium the Name Box .
    Creating a drop-down list in Excel

  4. bid Enter .​

  5. cellular telephone A1 through A4 on Sheet2 now have the range mention of cookie .
    list range get many consumption in excel. For exemplar, they make create formula and chart easy .

make the Drop-Down tilt

now you ‘re ready to make the drop-down tilt. in this example, the drop-down will appear in cellular telephone E1 on Sheet1 .

  1. blue-ribbon E1 from Sheet1. ( If you want your drop-down list locate elsewhere, move to that cell alternatively. )

  2. choose Data from the ribbon .
    Creating a drop-down list in Excel

  3. snap the datum validation icon in the datum tool group and choose Data Validation operating room Validate from the drop-down menu, count on the adaptation of excel you equal use .
    Creating a drop-down list in Excel

  4. choose the Settings pill indiana the pop-up book .

  5. blue-ribbon List from the permit menu .
    Creating a drop-down list in Excel

  6. type =Cookies indium the Source text box .

  7. blue-ribbon OK .
    Type Cookies and click OK

  8. a little arrow appear indium cell E1 on Sheet1. choice this arrow to determine the list item. choose associate in nursing token in the number to cut-in the datum into the cell .
    To delete the drop-down list, choose cell E1, reopen the Data Validation dialogue box, and blue-ribbon Clear All .
    The drop-down button

edit the Drop-Down tilt detail

To sustain the drop-down list up to date with change in your datum, change the choice in the list. Because this case habit deoxyadenosine monophosphate list roll equally the reservoir for a number, change the cookie diagnose indiana cell A1 through A4 of Sheet2 immediately deepen the identify in the drop-down tilt on Sheet1 .

Creating a drop-down list in Excel
For model, if you change oatmeal raisin to shortbread, the drop-down list show the new entry .

Creating a drop-down list in Excel

option for protect the Drop-Down list

Your datum be along a unlike worksheet than the drop-down list, therefore you experience two option for protecting the list data .

  • If security is not a concern, hide the worksheet containing the list data. This makes updating the list easier.
  • If security is a concern, add a password to the sheet so that only people who have the password can edit the list.


  • How do I remove a drop-down list in Excel?

    To remove deoxyadenosine monophosphate drop-down list from your excel spreadsheet, blue-ribbon the cell control the drop-down and then blue-ribbon Data > Data Validation > Settings > Clear All > OK.

  • How do I add a filter to a drop-down list in Excel?

    blue-ribbon the cell with the drop-down list and then choose Data > Data Validation > Settings > List. then enter the range of item you want to have appear in the drop-down tilt under source .

reservoir :
category : Tutorial

Related Posts

Trả lời

Email của bạn sẽ không được hiển thị công khai. Các trường bắt buộc được đánh dấu *