July 4

0 comments

How to Create Drop Down Menu in Excel (Drop Down List)

By Sharyn Baines

July 4, 2021

Dropdown, Modify, Named Range

Learn how to create a drop-down list in Excel.

In this blog you will learn two different ways to create a drop-down list and the advantages of each as well as an extra step needed for older versions of Excel when creating a drop-down list from another worksheet.

Let us look at an example in Excel.

A SUMIF function is in cell B4, giving the total of sales made by Anne.

Excel create drop-down list

Note: The SUMIF function in B4 is looking at Anne’s name in cell B3 and bringing back her total sales from the ‘Total Sales $’ column.

Excel create drop-down list

Wouldn’t it be nice to have a drop-down option on cell B3 where you could select any member of the sales team and have their total sales be pulled through into cell B4? The following steps will show you how to achieve this.

Option #1 – Create a Drop Down List from the Same Worksheet

Start with a pre-existing list on your worksheet.

Excel create drop-down list

Tip: a good idea is to sort your list in a logical order before creating your drop-down list. In this example, the list is ordered alphabetically.

Click on the cell you want the drop-down list on. For this example, we want the drop down information in cell B3.

Excel create drop-down list

Go to the Data tab and select Data Validation.

Excel create drop-down list

Note: You can also select Data Validation by selecting the drop-down arrow then selecting Data Validation.

Excel create drop-down list

The Data Validation dialog box will appear.

Excel create drop-down list

On the Settings tab, select the drop-down arrow underneath Allow.

Excel create drop-down list

Select List.

Excel create drop-down list

Click into the Source box. Make sure your insertion point is blinking in the Source box.

Excel create drop-down list

Now select over your list. Be sure to highlight every item you want included in the list.

Excel create drop-down list

The range for the list should now be in the Source box. Click OK.

Excel create drop-down list

And your list should now be created.

Excel create drop-down list

Note: this range is dynamic, which means if you update or change names on the original list, this will instantly update the drop-down list. For example, if we changed Anne’s name to Ann, this will be updated over in the drop-down list in cell B3.

The instruction above works for a drop-down list based on a list in the same worksheet, but what about a list from a different worksheet?

Option #2 - Create a Drop-Down List from a Different Worksheet

In this example, there are two worksheets. One is named ‘Drop down list’, which holds our Sales Figures and is where we want our drop-down list located. The other worksheet is called ‘Sales Team’. This worksheet has been created for the sole purpose of containing items that will be in our drop-down list. 

Excel create drop-down list
Excel create drop-down list

Select the cell you want the drop-down list on. For this example, we want the drop-down list on cell B3 on the Drop down list worksheet.

Excel create drop-down list

Select the Data tab and then Data Validation.

Excel create drop-down list

In settings, select List.

Excel create drop-down list

Click your insertion point into the Source text box.

Excel create drop-down list

Click onto the worksheet that holds the list. In this example, it is the Sales Team worksheet.

Excel create drop-down list

Note: if at this stage you are unable to click on to another worksheet, please refer to Option #3 below.

Highlight the list that you want as your drop-down list items.

Excel create drop-down list

The worksheet name, Sales Team, will now be included in the range in the Source box.

Excel create drop-down list

Click OK.

Excel create drop-down list

Your drop-down list should now be created in the Drop down list worksheet.

Tip: Sometimes it’s a good idea to hide the worksheet that contains the drop-down list items. This prevents others from tampering with the list items. To hide a worksheet, right-click the worksheet tab, and then select Hide.

Excel create drop-down list

The worksheet will now be hidden. To edit the list at any time, just right-click any worksheet tab and select Unhide. Select the name of the hidden worksheet and then click OK..

Option #3 Create a Drop-Down List from a Different Worksheet (Older Versions Of Excel) 

If you are in an older version of Excel, in the Data Validation dialog box you cannot click into another worksheet when trying to source the list from another worksheet.

The following steps show you how to work around this.

Go to the worksheet that holds the drop-down list items. In this example that is the Sales Team worksheet.

Excel create drop-down list

Highlight your list.

Excel create drop-down list

Click into the Name box, which is located on the very left of the Formula Bar.

Excel create drop-down list

Delete the cell reference and type a name for the list.

Note: Do not put any spaces between the words. For this example, we will name the list SalesTeam.

Excel create drop-down list

Press Enter and you have now given the list a name.

Return to the worksheet where you want your drop-down list. In this example, that is the Drop-down list worksheet.

Excel create drop-down list

Click on the cell where you are wanting the drop down list. Select the Data tab and then Data Validation.

Excel create drop-down list

Make sure you are in the Settings tab and select the drop-down arrow under Allow.

Excel create drop-down list

In Allow, select List.

Excel create drop-down list

Put your insertion point into the Source text box and type the named range you created, preceded by = (the equals sign), for example, =salesteam

Note: The list name is not case sensitive, you just need to be sure you spell it correctly.

Excel create drop-down list

Click OK.

Excel create drop-down list

You should now have a drop-down list linked through to the named list items.

Was this blog helpful? Please let us know in the Comments below.

If you enjoyed this post check out the related posts below.

Sharyn Baines

About the author

Sharyn is an expert trainer. She became the first certified Microsoft® MOUS Authorised Instructor in New Zealand. She is endorsed by Microsoft® as a qualified Microsoft® Office Specialist and has more than 20 years of experience in the training industry, developing and delivering technology training workshops. Her approach to taking the “techie-speak” out of technology training has placed her as a preferred supplier to many of New Zealand’s leading organisations.

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

JOIN FREE! 'Excel at Work Insiders Group' for free tutorials, mini-courses, videos and blogs!

>