Data Import & Export

Excel for the Web Data Validation from Another Workbook: Solutions & Workarounds

Emma Brown Oct 12, 2026 870 views

Question details

Determine whether Excel for the Web supports creating data validation dropdown lists that reference ranges located in a different external workbook.

How to Reference Data Validation Lists from Another Workbook in Excel for the Web

Product: Excel for the Web
Device & OS: not provided
Scenario: Attempting to create a data validation rule in an online workbook using a supplier list or data range stored in a separate OneDrive workbook.
Observed behavior: Excel for the Web does not natively support direct external workbook references for data validation sources; the source data must reside within the current workbook.

Before you start

Ensure you have access to both the source workbook containing your reference list and the destination workbook where you want to apply the data validation dropdown.

Solution 1Recommended

Copy the Source List into the Current Workbook

Since Excel for the Web requires the validation source to be in the same file, manually copying the external list into a new worksheet is the most reliable web-based workaround.

Excel for the Web lacks the ability to query closed external workbooks directly for data validation. By bringing the data into the active workbook, you bypass this limitation completely.

Open the source workbook

Open the external workbook stored in OneDrive that contains the list you want to use, and copy the required cell range.

Create a reference sheet

In your destination workbook, click the '+' icon at the bottom to create a new worksheet. You can name it 'ReferenceData'.

Paste the data

Paste the copied list into the new 'ReferenceData' worksheet. You can hide this worksheet later by right-clicking the tab and selecting 'Hide' to keep your workbook clean.

Apply data validation

Select the cell where you need the dropdown, go to the 'Data' tab on the ribbon, click 'Data Validation', choose 'List', and select the pasted range in your 'ReferenceData' sheet.

Copy the Source List into the Current Workbook

Keep Data Updated: If the original external list changes frequently, you will need to manually update this copied reference sheet in Excel for the Web.

Solution 2

Use the Excel Desktop Application

Switching to the full desktop version of Excel allows you to utilize advanced cross-workbook references for data validation.

Free Microsoft Office alternative

Use WPS Office for Advanced Data Management

If web-based spreadsheet limitations are slowing down your workflow, WPS Spreadsheet offers robust desktop-level features for free. It makes managing complex data validation rules and cross-sheet references seamless and efficient.

1. Download and Install: Get WPS Office for free from the official website and install the lightweight suite on your computer.
2. Open Your Workbooks: Launch WPS Spreadsheet and open your existing .xlsx files directly from your local drive or cloud storage.
3. Apply Validation Rules: Navigate to the Data tab, click Validation, and easily configure complex lists and referencing structures across multiple sheets.

Highly compatible with Microsoft Excel (.xlsx, .xls) file formatsFull support for advanced data validation without web browser limitationsLightweight, feature-rich desktop application with complete offline capabilitiesFamiliar interface ensures a smooth transition with zero learning curve

microsoft office alternative - wps office

Frequently Asked Questions

Can I use Power Query to sync external lists for data validation in Excel for the Web?

Power Query data can be refreshed in Excel for the Web, but the initial query connection must be created using the Excel desktop application. Once the external data is loaded into a local sheet via Power Query, you can use that local sheet for data validation on the web.

Why do I get an error when typing an external workbook path in Data Validation?

Excel for the Web strictly restricts data validation sources to the active workbook to maintain cloud performance and security. It cannot dynamically query or pull dropdown items from a closed, separate file stored on OneDrive.

Are external references supported for standard formulas in Excel for the Web?

Excel for the Web does support calculating and updating existing external links in formulas, but creating new cross-workbook references is heavily restricted compared to the desktop version. Data validation specifically does not support this.

Excel & Data Problems