Fix Excel Online Drop-Down List Using the Wrong Source Range in WPS Spreadsheets
Question details
The user needs to correct an Excel data validation drop-down list that references an adjacent or unintended source range in Excel for the web even though the same rule works correctly in desktop Excel.
- Product
- WPS Office Spreadsheets and Microsoft Excel Online
- Device & OS
- not provided
- Scenario
- The user is editing an Excel workbook in Excel for the web and finds that a data validation drop-down list shows values from the wrong source range, despite the workbook behaving correctly in desktop Excel.
- Observed behavior
- The drop-down list references an adjacent column or unintended range in Excel Online, which can indicate a corrupted validation rule or an online compatibility issue.
Open the workbook in Excel for the web and note the cell range or named range that should be used as the drop-down source. If the file is stored in OneDrive or SharePoint, download a local copy before changing the data validation rule.
Recreate the Data Validation Rule in WPS Office Spreadsheets and Re-upload the Workbook
Use this method when the drop-down works in desktop Excel but Excel Online reads the wrong source range. Recreating the rule in WPS Office produces a clean validation definition that syncs more reliably.
Excel Online can cache or misread a data validation source range after a workbook is edited in multiple clients. Recreating the rule in WPS Office Spreadsheets clears the incorrect online definition while preserving the worksheet data.
Download the affected workbook from OneDrive or SharePoint, then open it in WPS Office Spreadsheets.
Click the cell or select the range that contains the drop-down list so the data validation rule can be identified.
Go to the Data tab and click Data Validation or Validation to open the rule settings.
Clear the current Source box, click the collapse button, select the intended source range directly on the worksheet, and click OK to save the rule.
Save the file as an .xlsx workbook, remove the problematic online copy from OneDrive or SharePoint, and upload the corrected file.
Reopen the uploaded workbook in Excel for the web and verify that the drop-down list now shows the correct values.
Copy the Worksheet into a New Workbook to Clear a Corrupted Validation Rule
Use this method if recreating the rule in the same workbook does not fix the drop-down in Excel Online. A new workbook removes hidden corruption from the old file.
Repair the Excel Online Drop-Down Source Range with WPS Office Spreadsheets
Open the affected workbook in WPS Office Spreadsheets, recreate the data validation rule with the correct source range, and save it as an .xlsx file that is compatible with OneDrive, SharePoint, and Excel Online.
- 1. Download and open the workbook: Download the affected Excel workbook from OneDrive or SharePoint and open it in WPS Office Spreadsheets.
- 2. Open the Data Validation dialog: Select the drop-down cell or range, click the Data tab, and choose Data Validation.
- 3. Recreate the source range: Clear the existing Source value, use the collapse button to select the intended source range, and click OK.
- 4. Save and re-upload: Save the file as an .xlsx workbook, remove the old online copy, and upload the corrected file to OneDrive or SharePoint.
- 5. Verify the drop-down in Excel Online: Open the uploaded file in Excel for the web and confirm that the drop-down list displays values from the correct source range.
Frequently Asked Questions
Why does my Excel Online drop-down list use the wrong source range when desktop Excel shows the correct range?
Excel for the web can misread a cached or corrupted data validation definition. Opening the workbook in WPS Office Spreadsheets or desktop Excel and recreating the rule through Data > Data Validation usually clears the wrong online reference.
Can I fix the wrong source range without Microsoft Excel?
Yes. Download the workbook, open it in WPS Office Spreadsheets, select the drop-down cell, go to Data > Data Validation, enter the correct source range, save the file as .xlsx, and upload it back to OneDrive or SharePoint.
Do I need to remove the online workbook before uploading the corrected file?
It is best to remove or replace the problematic online copy so Excel Online uses the newly saved validation rule instead of the old cached version.
Will recreating the data validation rule delete my existing cell values?
No. Changing the source range or validation rule does not delete existing cell values, although Excel Online may flag values that no longer meet the validation criteria depending on your settings.
What should I do if the drop-down still shows the wrong range in Excel Online after uploading?
Copy the worksheet into a new workbook, recreate the data validation rule there, save and upload the new file, and test again. If the issue continues, clear the browser or OneDrive cache and reopen the workbook.
