Function Problems

How to Use a Dynamic Array Formula in an Excel Data Validation List

Algirdas Jasaitis Oct 23, 2026 868 views

Question details

Populate a dropdown list using dynamic array formulas such as SORT, UNIQUE, and FILTER.

How to Use a Dynamic Array Formula in an Excel Data Validation List

Product: Microsoft Excel
Device & OS: not provided
Scenario: Creating a dynamic dropdown list in a spreadsheet that automatically updates based on array formula results.
Observed behavior: Entering the dynamic array formula directly into the Data Validation source box returns no values, even though the formula works correctly when typed into a standard worksheet cell.

Before you start

Ensure you are using a version of Excel that supports dynamic array formulas and the spill range feature, such as Microsoft 365 or Excel 2021.

Solution 1Recommended

Use a Helper Cell and the Spill Range Operator (#)

Since Excel does not support evaluating dynamic array functions directly inside the Data Validation menu, you must output the array into a cell and reference its spill range.

Dynamic array formulas like SORT, UNIQUE, and FILTER generate multiple values that automatically 'spill' into adjacent blank cells. To use these values in a dropdown menu, you need to calculate them in a separate area of your worksheet and then use the spill operator (#) to feed that dynamic range into your Data Validation list.

Select a helper cell

Choose an empty cell in a hidden column or a separate configuration worksheet (e.g., cell Z1) to hold your array formula.

Enter the dynamic array formula

Type your formula, such as =SORT(UNIQUE(A2:A100)), into the helper cell and press Enter. The results will spill down the column automatically.

Select the target cell for the dropdown

Click on the cell or range of cells where you want the actual dropdown list to appear for users.

Open Data Validation settings

Navigate to the Data tab on the Excel ribbon and click on 'Data Validation'.

Configure the list source

In the Data Validation dialog box, choose 'List' from the Allow dropdown. In the Source box, type the absolute reference to your helper cell followed by the hash symbol (e.g., =$Z$1#), and click OK.

Use a Helper Cell and the Spill Range Operator (#)

Understanding the Spill Operator: The hashtag (#) appended to a cell reference instructs Excel to include the entire range of values generated by the dynamic array formula originating in that cell. If the list grows or shrinks, the dropdown options will update automatically.

Advanced Spreadsheet Solution

Create Dynamic Dropdown Lists Effortlessly in WPS Office

WPS Spreadsheet offers robust support for advanced data validation, array formulas, and managing dynamic lists with a highly intuitive user interface. You can easily build error-free dynamic dropdowns for your data entry tasks.

1. Open your workbook in WPS Spreadsheet: Launch WPS Office and open your spreadsheet file containing the source data.
2. Set up your dynamic list: Enter your array formula into a blank helper column to generate the dynamic list of items.
3. Apply Data Validation: Select your target input cell, navigate to the Data tab, and click the Validation button.
4. Reference the spill range: Select 'List' as your validation criteria and type the helper cell reference with the spill operator (#) into the source field.

Fully compatible with Microsoft Excel file formats (.xlsx) and formulasSeamlessly supports advanced array functions and data validation rulesLightweight, fast, and completely free to use for everyday office tasks

microsoft office alternative - wps office

Frequently Asked Questions

Why is my Data Validation list showing blank options at the bottom?

This usually happens if your source range contains empty cells or if your dynamic array formula evaluates to blank strings. Ensure your FILTER or UNIQUE functions are specifically set up to exclude blank values from the source data.

Can I hide the helper column used for the dropdown list?

Yes. Once you have set up your dynamic array formula in a helper column (like column Z), you can right-click the column header and select Hide. The dropdown list referencing that column will continue to function normally.

What does the hashtag (#) mean in spreadsheet formulas?

The hashtag, known as the spill operator, refers to the entire range of values returned by a dynamic array formula. For instance, referencing =$Z$1# captures all the resulting cells filled by the formula located in Z1, dynamically expanding or contracting as needed.

Do SORT and UNIQUE work in older versions of Excel?

No, dynamic array functions like SORT, UNIQUE, and FILTER were introduced in Microsoft 365 and Excel 2021. Older versions do not support these functions natively and require complex array formulas (Ctrl+Shift+Enter) or VBA scripting to achieve similar dynamic dropdowns.

Excel & Data Problems