Why Excel VBA Dropdowns Fail in Older Versions and How to Fix Them
Question details
The user needs to understand why an Excel VBA routine that builds data-validation dropdowns with dynamic array functions works on one computer but fails on an older Excel installation.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A VBA routine uses SORT, UNIQUE, FILTER, Formula2, and spilled-range references to create data-validation dropdowns. The workbook runs correctly on a newer machine but fails on an older Excel installation.
- Observed behavior
- On the older Excel installation, the dropdowns fail or the formulas and validation source return errors because dynamic-array functions and spill references are not supported.
Open the affected workbook on the failing computer and note the Excel version. In an empty cell, test whether SORT, UNIQUE, or FILTER returns a spill range or an error before modifying the VBA code.
Check whether your Excel version supports dynamic array functions
Use this first to confirm whether the failure is caused by missing dynamic-array support in older Excel rather than by the VBA code itself.
SORT, UNIQUE, FILTER, Formula2, and spill references such as B3# require the dynamic calculation engine. Microsoft 365 and newer Excel builds support them, but many older desktop versions such as Excel 2019 and earlier do not.
Open the workbook where the VBA data-validation dropdown fails and go to the worksheet that contains the dropdown source formulas.
Enter =SORT(UNIQUE(range)) or =FILTER(range,condition) in an empty cell. If it returns #NAME? or another error instead of a spill range, dynamic array support is missing.
Go to File > Account > About Excel. If the version is not a Microsoft 365 subscription or is Excel 2019 or earlier, update to a supported version where possible.
Rewrite the VBA to create a sorted unique list without spill references
Use this when you cannot update the older Excel installation and need a backward-compatible validation source.
Use a searchable UserForm add-in for long data-validation lists
Use this when the list is long and you need a searchable dropdown in older Excel without rebuilding the entire VBA routine.
Use WPS Office as a Free Alternative for Excel Workbooks and Dropdown Lists
WPS Office is a free, lightweight, easy-to-use Office alternative with familiar Excel-style spreadsheets, strong .xlsx compatibility, and a familiar interface that makes migration seamless when older Excel versions cannot be updated.
- 1. Download WPS Office: Visit wps.com, download the free WPS Office installer for your Windows or Mac device, and run the setup.
- 2. Open your Excel workbook in WPS Office: Launch WPS Spreadsheets and open the .xlsx or .xlsm workbook that contains the validation lists or macro file.
- 3. Verify or recreate dropdown lists: Use Data > Data Validation to check the dropdown source and adjust it to a compatible range if needed.
Frequently Asked Questions
Why do SORT, UNIQUE, and FILTER work on one computer but fail on another?
These functions require dynamic-array support, which is tied to the Excel version and update channel. A Microsoft 365 or newer Excel build may support them, while an older desktop installation may return #NAME? or fail.
Can I use spill references like B3# in data validation in older Excel?
No. Spill references depend on the dynamic calculation engine. In older Excel versions, use a fixed range source such as =Helper!$A$2:$A$200 instead.
How do I check whether my Excel version supports dynamic arrays?
Go to File > Account > About Excel and check your version and update level. You can also enter =SORT(UNIQUE(range)) in an empty cell; if it spills, dynamic arrays are supported.
What can I do if I cannot update my older Excel version?
Rewrite the VBA to collect unique values, sort them in code or helper cells, and write them to a fixed range. Then point the data-validation source to that fixed range instead of a spilled formula.
Does WPS Office support Excel data-validation dropdowns?
WPS Spreadsheets can open Excel workbooks and create or edit data-validation dropdowns. However, if the workbook relies on Excel-specific VBA features such as Formula2 and spill references, test it in WPS Office first.
