Office Settings & Configuration

Fix Excel Online Data Validation List Formula Is Not Valid

Huda QurayshiHuda Qurayshi Nov 13, 2026 870 views

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.

Fix Excel Online Data Validation List Formula Is Not Valid
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.
Before you start

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.

Solution 1Recommended

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.

1
Open Data Validation

Select the target cells in Excel Online, click Data > Data Validation, and choose List in the Allow drop-down.

2
Enter a cell range source

In the Source box, type =Sheet2!$A$1:$A$4, replacing it with your actual sheet and range, then click OK.

Use a Cell Range as the Data Validation Source
List works in Excel Online: Using a cell range makes the data validation formula valid in Excel Online and keeps the list easy to update.
Free Microsoft Office alternative

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. 1. Download WPS Office: Go to the WPS Office download page and install WPS Spreadsheets for Windows, macOS, or mobile.
  2. 2. Open the workbook: Launch WPS Spreadsheets, click Open, and select the .xlsx file you were editing in Excel Online.
  3. 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. 4. Save the file: Save the workbook as .xlsx to preserve formatting, formulas, and validation rules for Excel Online and desktop Excel.
Free, lightweight Excel alternative for editing .xlsx workbooksData Validation and Name Manager tools for list sourcesFull compatibility with XLSX, XLS, and CSV formatsFamiliar ribbon interface for easy migration from Microsoft ExcelOpen and edit workbooks offline without Dropbox or browser limitations
microsoft office alternative - wps office

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.