Excel – Creating a Dropdown Box ‘From data on another sheet’

KB ID 0000711 


My wife asked me how to do this today. I don’t use Excel much, most people think because I work in IT I know everything about all software packages, and they are surprised when I’m asked questions like this, and I say ‘I don’t know I’m a network engineer’.

But seeing as it was for the better half, I fired up Excel and worked it out. (This must be worth at least a cup of Tea!)


Step 1 – Create the source Data

1. Select the sheet that you want to populate the drop down list from, or create a new one and call it something sensible.

2. Type all your values into a column.

3. Select the column (you can select the cells but then if you add any new data it wont be added to the drop down).

4. In the ‘Name’ Section give the range a name i.e. garment, then press Enter.

Excel Drop Down

Step 2 – Create the Drop Down List

1. Select the sheet that you want the drop down box to appear on.

2. Select the Cell where you want the drop down.

3. On the ‘ribbon’ select data.

4. Data Validation.

5. Settings tab.

6. Change allow to ‘list’.

7. Set Source to ‘={the range name you used above}’ OK.

Excel Add a drop down list box

8. The drop down (list box) will be created.

insert list box in excel

Related Articles, References, Credits, or External Links

Excel: Calculate Cost, Margin, Sell Price

Excel – IP Address Formula for ‘Auto fill’

Author: Migrated

Share This Post On