How to create a drop down menu in Excel 365

Stop Typing! How to Create Dynamic Drop-Down Menus in Excel (The Quickest Data Validation Method)

Welcome to Darren’s Tech Tutorials! We all know that manual data entry is boring, time-consuming, and worst of all, leads to errors. If you’re tired of typos messing up your spreadsheets, it’s time to incorporate one of Excel’s most powerful yet simple features: the Drop-Down Menu.

Drop-down menus force users to select from a predefined list, ensuring consistency across your entire dataset. This tutorial focuses on using Data Validation—the fastest and most reliable way to implement these menus.

We are using Excel 365 for this guide, but the steps are virtually identical for all modern versions of Excel (2016, 2019, etc.). Let’s dive in!

Why Use Drop-Down Menus in Your Spreadsheet?

Implementing a drop-down list isn’t just a matter of aesthetics; it’s essential for data integrity and analysis.

1. Eliminate Typos: Ensure that “Pending” is always spelled “Pending” and not “Pnding” or “Pening.”
2. Faster Input: Clicking a value is always quicker than typing it out every time.
3. Simplified Reporting: When data is consistent, running pivot tables and complex formulas becomes dramatically easier and more reliable.

Step 1: Prepare Your List Source

Before you can build a drop-down menu, you need to tell Excel what items should appear in it. This is your ‘Source List.’

Create the List of Menu Items

1. Choose a Location: We highly recommend creating your source list in a separate, dedicated sheet (or in a column that is hidden from the main view) to keep your main data sheet clean and professional.
2. Enter Your Items: In a single column (or a single row), type out every item you want available in your drop-down menu.
   • Example: If you are tracking project status, your list might be: Completed, In Progress, On Hold, Pending Review, Cancelled.

Tip: If you anticipate expanding this list later, include a few empty rows beneath your current list when selecting the source range in Step 3. This makes updating the menu much easier later on.

Step 2: Implement the Drop-Down Menu Using Data Validation

Once your source list is defined, we can move on to the main event: applying the Data Validation rule. This process takes only three quick clicks!

Step 2A: Select the Destination Cells

First, identify where you want the drop-down menu to appear.

1. Highlight: Click and drag to select all the cells (the entire range) where you want the new drop-down functionality.
   • Example: If you want the menu to appear in column C from row 2 down to row 100, select C2:C100.

Step 2B: Open the Data Validation Tool

The Data Validation tool is located under the main Excel ribbon.

1. Navigate to the Data tab on the Excel ribbon.
2. In the ‘Data Tools’ group, click the Data Validation icon (it often looks like a checkmark and a prohibit sign next to a cell).
3. The Data Validation dialog box will appear.

Step 2C: Configure the Drop-Down Settings

This is the most critical part where you define the menu.

1. Access the Settings Tab: Ensure you are in the Settings tab of the Data Validation dialog box.
2. Choose ‘List’: Under the Validation criteria section, click the ‘Allow’ dropdown menu and select List.
3. Define the Source Range:
   • Click the box next to the Source: field.
   • Now, without closing the dialog box, navigate to where you created your menu items (your source list).
   • Click and drag over the cells containing your list items (e.g., $A$1:$A$5). Excel will automatically populate the Source field with the cell references.
4. Important: Make sure the checkbox next to In-cell dropdown is checked.
5. Click OK.

Success!

You are done! If you click on any of the cells you selected in Step 2A, you will now see a small down arrow. Click the arrow, and your predefined list of items will appear, ready for selection.

Conclusion: Take Control of Your Data

Congratulations! You have successfully implemented a professional, error-proof drop-down menu in your spreadsheet using Excel’s powerful Data Validation feature. This simple change will instantly boost the accuracy and speed of your data entry, making your spreadsheets easier to use and much more reliable for reporting.

Now it’s your turn! Try creating a drop-down list for colors, names, departments, or project priorities today.

If this tutorial helped you clean up your data, please give the video a thumbs up and subscribe to Darren’s Tech Tutorials for more clear, actionable guides! We post new tips every week to help you master the tools you use every day. Happy Excelling!