Data Import & Export

How to Query Excel Data from SharePoint Forms with 50,000 Rows

Elise Williams Sep 29, 2026 869 views

Question details

The user needs a method to dynamically query and retrieve data from an Excel file containing 50,000 rows stored in a document library using SharePoint forms.

How to Query Excel Data from SharePoint Forms with 50,000 Rows

Product: Microsoft SharePoint and Excel
Device & OS: not provided
Scenario: Attempting to create dynamic lookups in SharePoint forms that pull values from a massive Excel dataset.
Observed behavior: SharePoint lookup columns cannot directly query Excel files, and migrating the data to a SharePoint list risks hitting the 5,000 item list view threshold, causing performance and querying errors.

Before you start

Ensure your Excel data is formatted as a formal Table (press Ctrl+T) and that you have appropriate permissions to create Power Automate flows in your SharePoint environment.

Solution 1Recommended

Use Power Automate for Dynamic Excel Lookups

Create a Power Automate flow to bypass SharePoint's native lookup column limitations and query the Excel table directly.

Since SharePoint native lookups only work with other SharePoint lists, you can bridge the gap using Power Automate. For datasets as large as 50,000 rows, it is critical to use OData filter queries instead of retrieving all rows to prevent flow timeouts.

Create a Power Automate Flow

Go to Power Automate, click 'Create', and select an 'Automated cloud flow' triggered by a SharePoint action (like 'When an item is created or modified').

Add an Excel Action

Add the 'List rows present in a table' action from the Excel Online (Business) connector. Select your SharePoint site, document library, Excel file, and the specific Table.

Configure OData Filter Query

In the action's advanced options, use the 'Filter Query' field to query only the required row (e.g., ID eq 'YourSharePointValue'). This prevents downloading all 50,000 rows.

Enable Pagination (Optional)

If you need to retrieve multiple matches across the massive dataset, click the three dots on the Excel action, select 'Settings', turn on 'Pagination', and set the threshold to 100000.

Update SharePoint Item

Add the 'Update item' SharePoint action to write the retrieved Excel data back to the relevant fields in your SharePoint list form.

Use Power Automate for Dynamic Excel Lookups

Performance Tip: Using OData filter queries dramatically reduces flow execution time from several minutes to just a few seconds when dealing with 50,000+ rows.

Solution 2

Migrate Excel Data to an Indexed SharePoint List

Move the 50,000 rows into a SharePoint list natively and use indexed columns to manage the 5,000 item list view threshold.

Free Microsoft Office alternative

Handle Massive Datasets Locally with WPS Office

While SharePoint requires complex workarounds to query large Excel files, analyzing and editing 50,000+ rows locally doesn't have to be sluggish or expensive. WPS Spreadsheet provides a lightweight, highly responsive environment to handle massive datasets for free. It serves as an excellent Microsoft Office alternative with full format compatibility and familiar data tools.

1. Download WPS Office: Visit the official WPS Office website and download the free installation package.
2. Open Your Large Dataset: Launch WPS Spreadsheet and open your .xlsx or .csv file containing the 50,000 rows instantly.
3. Analyze Data Effortlessly: Use built-in VLOOKUP functions, Pivot Tables, and Data Filters directly on your desktop.

Smoothly edit and filter spreadsheets with 50,000+ rows without performance lag100% compatible with Microsoft Excel formats (.xlsx, .xls, .csv)Built-in advanced data filtering, pivot tables, and lookup functionsLightweight desktop installation with a familiar, intuitive interface

microsoft office alternative - wps office

Frequently Asked Questions

Why can't native SharePoint lookup columns read Excel files?

SharePoint lookup columns are natively designed to build relational links strictly between SharePoint lists. They do not have the capability to parse external files, including Excel workbooks (.xlsx) stored in document libraries.

What is the SharePoint list view threshold and how does it affect massive datasets?

SharePoint enforces a 5,000 item limit for list views to optimize server database performance. While a list can store up to 30 million items, you cannot view, filter, or query more than 5,000 unindexed items simultaneously without encountering an error.

Can Power Automate process 50,000 rows in Excel without timing out?

Yes, but you must avoid retrieving the entire dataset. You should use OData filter queries in the 'List rows present in a table' action to pinpoint specific rows. If retrieving large batches is necessary, you must enable Pagination in the action settings and increase the threshold limit.

Excel & Data Problems