Function Problems

Why the SORT Function Doesn't Work in Excel Data Validation & How to Fix It

John Wilson Oct 1, 2026 869 views

Question details

The user is attempting to use the SORT function directly as the source for an Excel Data Validation drop-down list, but the list fails to generate because the function evaluates as an array error instead of a standard cell range.

How to Fix the SORT Function Not Working in Excel Data Validation

Product: Excel
Device & OS: not provided
Scenario: Creating an alphabetically or numerically sorted drop-down list using Data Validation.
Observed behavior: Excel rejects the SORT function in the Data Validation Source field or evaluates it as an error because the function returns a dynamic array rather than a direct worksheet range reference.

Before you start

Ensure you are using a modern version of your spreadsheet software that supports dynamic arrays (such as Microsoft 365 or the latest WPS Office) to properly utilize spill range references.

Solution 1Recommended

Use a Helper Column with a Spill Range Reference

By placing the SORT function in a separate helper cell and referencing its spilled results, you provide Data Validation with the exact physical range it requires.

Data Validation requires a strict range reference (like A1:A10) to generate a drop-down list. Because the SORT function produces a dynamic array in the background, typing it directly into the Source box violates this rule. The workaround is to let the array "spill" into actual worksheet cells and then point the Data Validation tool to that specific spilled block.

Enter the SORT formula in a helper column

Pick an empty column in your worksheet. Click a blank cell (e.g., D2) and type your sorting formula, such as =SORT(A2:A20).

Allow the array to spill

Press Enter. The sorted values will automatically populate downward from your chosen cell, creating a spilled dynamic array.

Open Data Validation

Select the specific cell or cells where you want the drop-down list to appear. Go to the Data tab on the top ribbon and click on Data Validation.

Reference the spill range

In the Data Validation dialog box, select 'List' under the Allow drop-down. In the Source box, type the reference to your helper cell followed by the '#' symbol (for example, =D2#), and click OK.

Use a Helper Column with a Spill Range Reference

Dynamic Updates: By using the '#' symbol, your drop-down list becomes fully dynamic. Whenever you add or modify data in the original list, the helper column automatically resorts, and the drop-down list updates without manual adjustments.

Advanced Data Management

Create Dynamic Sorted Drop-down Lists Seamlessly in WPS Spreadsheet

WPS Spreadsheet fully supports advanced data handling, helper columns, and dynamic range references. You can easily overcome validation limitations by utilizing WPS's highly compatible and intuitive interface to build properly sorted, error-free drop-down lists.

1. Open your data in WPS Spreadsheet: Launch WPS Office, open your spreadsheet document, and locate the list you want to sort.
2. Sort the data in a helper column: Use a blank column to arrange your data. You can apply standard sorting tools from the Data tab or use array formulas if supported.
3. Apply Data Validation: Select the cell for your drop-down menu, go to the Data tab, and click Data Validation.
4. Set the list source: Choose 'List' as the validation criteria and highlight the newly sorted range in your helper column to serve as the reference.

Fully compatible with Microsoft Excel file formats (.xlsx)Supports robust data validation rules and dynamic list creationLightweight application with a fast, familiar user interfaceFree to use with comprehensive formula support

microsoft office alternative - wps office

Frequently Asked Questions

Can I use the SORT function directly in the Data Validation Source box?

No. The Data Validation Source box strictly requires a range reference (like A1:A10) or a named range. The SORT function returns an array of values, not a range, which triggers an error if entered directly.

What does the '#' symbol mean when typing =D2# in the Source box?

The '#' symbol is the spilled range operator. It tells the software to include the entire dynamic array that originates from the formula placed in cell D2, no matter how many rows it expands or shrinks to.

Why is my sorted drop-down list showing blank options at the bottom?

This usually happens when your original data range contains empty cells, which are then sorted to the bottom of your dynamic array. To fix this, wrap the SORT function around a FILTER function to remove blanks, for example: =SORT(FILTER(A2:A20, A2:A20<>"")).

Does this spill range workaround work in older spreadsheet versions?

Older versions of Excel (prior to Microsoft 365 or Excel 2021) do not support dynamic arrays and the '#' operator. In those versions, you must manually sort the source data or create a dynamic named range using the OFFSET and COUNTA functions.

Excel & Data Problems