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

Posted July 4, 2021

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? I'm here to empower your journey with Excel, aiming to make your daily tasks more efficient and boost your potential.

Share your thoughts in the Comments below - your insights not only enrich others, they also help me tailor future content to your needs.

And if you're looking to take a step further, join our exclusive '
Insider Group'. As a member, you'll get early access to our latest tips, and in-depth tutorials. Sign up Today!"

Happy Excel-ling!!

Excel drop down list

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

Want to create a drop down menu in Excel? Follow this step-by-step guide to learn two different ways to create a drop down list in Excel.

Instructions

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

  1. Start with a pre-existing list on your worksheet.
  2. Click on the cell you want the drop-down list on.
  3. Go to the Data tab and select Data Validation. The Data Validation dialog box will appear.
  4. On the Settings tab, select the drop-down arrow underneath Allow.
  5. Select List.
  6. Click into the Source box. Make sure your insertion point is blinking in the Source box.
  7. Select over your list. Be sure to highlight every item you want included in the list. The range for the list should now be in the Source box.
  8. Click OK.

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

  1. Open two worksheets. One with the items in a list and another where you would like the drop down list to be located.
  2. Select the cell you want the drop-down list on.
  3. Select the Data tab and then Data Validation.
  4. In settings, select List.
  5. Click your insertion point into the Source text box.
  6. Click onto the worksheet that holds the list.
  7. Highlight the list that you want as your drop-down list items. The worksheet name where you have the list of items should now be included in the range in the Source box.
  8. Click OK. Your drop-down list should now be created in the worksheet.

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

  1. Go to the worksheet that holds the drop-down list items.
  2. Highlight your list.
  3. Click into the Name box, which is located on the very left of the Formula Bar.
  4. Delete the cell reference and type a name for the list (do not put any spaces between the words).
  5. Press Enter and you have now given the list a name.
  6. Return to the worksheet where you want your drop-down list.
  7. Click on the cell where you are wanting the drop down list.
  8. Select the Data tab and then Data Validation.
  9. Make sure you are in the Settings tab and select the drop-down arrow under Allow.
  10. In Allow, select List.
  11. Put your insertion point into the Source text box and type the named range you created, preceded by = (the equals sign).
  12. Click OK.

    Notes

    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.

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

    Note: the range for the drop down list is dynamic, which means if you update or change names on the original list, this will instantly update the drop-down list. 

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

    Did you try this?

    If you found this blog helpful please leave a comment below.

    Elevate your Excel game and become a pro with our exclusive Insider Group

    Be the first to know about new tutorials, videos, and tips for Microsoft 365 products. Join us now and claim your exclusive bonus, your list of Essential Excel Skills to become proficient in Excel!

    {"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}
    Skip to Instructions
    >