How to Create a Dropdown List in Excel 2016, 2019, and Microsoft 365: A Comparison

---

Creating dropdown lists in Excel is a fundamental skill that significantly improves data entry accuracy and consistency. Whether you’re using Excel 2016, Excel 2019, or the ever-evolving Microsoft 365, the core process remains remarkably consistent. However, Microsoft 365 introduces some dynamic capabilities that can make managing your lists even easier.

Let’s break down how to create these helpful lists across different Excel versions and highlight any key differences you might encounter.

---

The Core Method: Data Validation (Consistent Across All Versions)

The primary tool for creating dropdown lists in Excel hasn’t changed. It’s the Data Validation feature, found in the Data tab of your Excel Ribbon. The steps you’ll follow are virtually identical in Excel 2016, 2019, and Microsoft 365.

Here’s the universal process:

1. Select the Cell(s): First, click on the cell or range of cells where you want your dropdown list to appear.
2. Go to Data Validation: Navigate to the Data tab on the Excel Ribbon. In the Data Tools group, click on Data Validation.
3. Choose “List” as the Validation Criteria:
   • The Data Validation dialog box will open.
   • On the Settings tab, click the dropdown arrow next to “Allow:” and select List.
4. Define Your List Source:
   • In the Source box, you have two main options:
     • Type your items directly: For short, static lists, you can type your options separated by commas (e.g., Yes,No,Maybe).
     • Reference a range of cells: For longer or potentially changing lists, this is the recommended method. Click the small arrow icon next to the Source box, then select the range of cells in your worksheet that contains your list items.
5. Configure Input Messages and Error Alerts (Optional but Recommended):
   • Input Message Tab: Add a helpful message that appears when a user selects the cell, guiding them on what to enter.
   • Error Alert Tab: Define how Excel should react if a user tries to type something not on your list (e.g., a “Stop” alert to prevent invalid entries).
6. Click OK: Your dropdown list will now appear in the selected cell(s).

Want to see this process in action? We have a quick, visual guide available at gif.how/excel-dropdown-basic that walks you through these exact steps!

---

Key Differences & Enhancements (Mostly in Microsoft 365)

While the core method is the same, Excel 365 offers some notable enhancements that make managing dynamic dropdown lists much simpler.

1. Dynamic Arrays and the Spill Range (#) Operator (Microsoft 365 Advantage)

This is the biggest game-changer. Excel 365 introduced Dynamic Arrays and new functions like UNIQUE and SORT. This allows for incredibly powerful and truly dynamic dropdown lists without needing complex OFFSET or INDEX formulas.

• In Excel 2016/2019: To create a dynamic dropdown list that automatically updates when you add or remove items from your source list, you typically needed to use a Named Range combined with a complex formula like OFFSET or INDEX and COUNTA. This worked, but it was more challenging to set up and understand.
• In Excel 365: You can simply create your source list, and then use formulas like =UNIQUE(A:A) (to get a unique list of items from column A) or =SORT(UNIQUE(A:A)) (to get a sorted, unique list). Then, in your Data Validation Source box, you just refer to the first cell of that formula’s output and add a hash symbol (#). For example, if your UNIQUE formula is in cell E2, your Data Validation Source would be =$E$2#. This “spill range” automatically expands or contracts as your source data changes, making your dropdown truly dynamic with minimal effort.
  • Benefit: Say goodbye to manually adjusting ranges or complex formulas for dynamic lists! This is especially powerful if your source data is an Excel Table, as adding new rows to the table automatically extends the range used by your UNIQUE formula.

2. Auto-Complete for Dropdown Lists (Microsoft 365 Advantage)

This is a minor but incredibly convenient feature available only in recent versions of Microsoft 365 (and potentially Office 2024).

• In Excel 2016/2019: When you type into a cell with a dropdown list, Excel doesn’t offer auto-complete suggestions from the list itself. You have to type the full item, or select it with the dropdown arrow.
• In Excel 365: As you start typing the first few characters into a cell with a dropdown list, Excel intelligently suggests matching items from the list. This speeds up data entry significantly, especially for long lists, as you don’t always need to click the dropdown arrow.
  • Benefit: Faster data entry and a more intuitive user experience.

---

Conclusion: Evolving for Efficiency

While the core mechanics of creating dropdown lists remain consistent across Excel 2016, 2019, and Microsoft 365, the newer versions, particularly Microsoft 365, offer significant quality-of-life improvements. The introduction of Dynamic Arrays and the auto-complete feature simplifies the creation and management of dynamic lists and enhances the user experience for data entry.

If you’re still on Excel 2016 or 2019, you can certainly create powerful dropdown lists. However, if you have access to Microsoft 365, leveraging its advanced capabilities will make your spreadsheets even more robust, user-friendly, and effortless to maintain.

No matter your Excel version, adding dropdown lists is a smart move for cleaner data. For more quick, visual guides on mastering Excel features, make sure to visit gif.how!