Data Validation List with Dynamic Arrays in Excel

Add to Favorites
Author: | Edits: don

How to make a drop down menu list using data validation and dynamic arrays in Excel 365.

This is a simple procedure, but very easy to forget.


Select the first cell of the dynamic array > put a # at the end of it.


Note: absolute and relative cell references work the same way, there is no change for this.

Excel Function: FILTER(), SORT(), UNIQUE()
Excel Version: Excel 365
Downloadable Files: Excel File

Question? Ask it in our Excel Forum

Our Excel Courses

  • Skill Level: All Levels
  • |
  • 5 hours+

How to make a fully featured professional form in Excel that is unbreakable. This includes how to use the form to store, view, edit, and delete data from a data storage worksheet.

  • Skill Level: All Levels
  • |
  • 2 hours

Send Emails from Excel using VBA and Macros. This course starts from the Basics and builds up to more advanced examples with attaching workbooks, worksheets, PDF's, automatically sending emails, including a signature, error handling, increasing speed, and more.

Similar Content on TeachExcel
Ignore Blanks in a Data Validation List in Excel
Tutorial: I will show you 3 ways to remove the blanks from a Data Validation dropdown menu in Excel...
Automatically Updating Dependent Drop Down List Menus in Excel
Tutorial: Step-by-step instruction on how to make automatically updating dependent data validation ...
Add an Input Message to a Cell - Message Appears When the Cell is Selected in Excel - Data Validation Macro
Macro: Add data validation input messages to cells in Excel with this free Excel macro. These mes...
Make Dates from Drop Down Lists in Excel
Tutorial: How to use a drop down list (data validation) to create a date in Excel. This technique c...
Dynamic Charts in Excel - The Easy Way
Tutorial: How to create dynamic charts that update automatically when new data is added to Excel. T...
List All Named Ranges in Excel - Displays the Name and Value for Every Named Range Within the Active Workbook in Excel
Macro: List all of the named ranges in a workbook in Excel and the corresponding values store...