Fix Excel Online Data Validation List Formula Is Not Valid
Question details
Resolve the Excel Online data validation list error "formula is not valid" when the source range or list separator is unsupported, especially for workbooks opened through Dropbox.

- Product
- Microsoft Excel Online
- Device & OS
- not provided
- Scenario
- Configuring a data validation list in Excel Online with a workbook hosted in Dropbox, OneDrive, or SharePoint.
- Observed behavior
- Excel Online rejects the data validation list source with "formula is not valid," even though the same source may work in desktop Excel.
Open the workbook in Excel Online and note whether the data validation source is a typed list or a cell range, and whether the file is hosted in Dropbox, OneDrive, or SharePoint.
Use a Cell Range as the Data Validation Source
Use this when Excel Online rejects a typed comma-separated list; a worksheet range is the supported source format.
Excel Online expects data validation list sources to be a worksheet reference, not a hard-coded list of values. Desktop Excel may accept typed lists, but the online editor can block them.
Select the target cells in Excel Online, click Data > Data Validation, and choose List in the Allow drop-down.
In the Source box, type =Sheet2!$A$1:$A$4, replacing it with your actual sheet and range, then click OK.

Define a Named Range for a List on Another Worksheet
Use this when the source range is on a different sheet and Excel Online does not accept a direct cross-sheet reference.
Configure the Validation in Desktop Excel and Save the Workbook
Use this when Excel Online still shows the error in Dropbox or when the online editor keeps rejecting the source.
Use WPS Spreadsheets as a Free Excel Online Alternative
If Excel Online keeps rejecting data validation lists, switch to WPS Spreadsheets. It opens .xlsx workbooks directly, includes Data Validation and Name Manager, and avoids browser or cloud-host restrictions while keeping full Microsoft Excel format compatibility.
- 1. Download WPS Office: Go to the WPS Office download page and install WPS Spreadsheets for Windows, macOS, or mobile.
- 2. Open the workbook: Launch WPS Spreadsheets, click Open, and select the .xlsx file you were editing in Excel Online.
- 3. Set the data validation list: Select the input cells, click Data > Validation, choose List, and enter a cell range or named range as the source.
- 4. Save the file: Save the workbook as .xlsx to preserve formatting, formulas, and validation rules for Excel Online and desktop Excel.

Frequently Asked Questions
Why does Excel Online say "formula is not valid" for a data validation list?
Excel Online usually requires the list source to be a worksheet cell range, such as =Sheet2!$A$1:$A$4. If you enter a comma-separated list or an unsupported reference, Excel Online may reject it even when desktop Excel accepts it.
Can I use a comma-separated list in Excel Online data validation?
It is safer to avoid comma-separated values. Type the list items into worksheet cells and then reference that cell range in Data Validation, or use a named range.
How do I reference a data validation list on another worksheet in Excel Online?
Create a named range from the source cells with Formulas > Name Manager, then enter =YourRangeName in the Data Validation Source box.
Does hosting the workbook in Dropbox affect Excel Online data validation?
Yes, some Dropbox-hosted workbooks can have editing limitations in Excel Online. Download the file or open it through OneDrive or SharePoint, configure the validation in desktop Excel, and save the file again.
Can WPS Office open and edit workbooks that have data validation lists?
Yes, WPS Spreadsheets supports Excel data validation lists. Open the .xlsx file in WPS Office, use Data > Validation to adjust the source, and save it back as .xlsx.




