How to Fix Power Query Loading Small Excel Tables Slowly
Question details
The user needs to resolve an issue where Power Query takes an excessively long time to load data into an Excel worksheet, even though the data preview loads quickly.

- Product
- Microsoft Excel (Power Query)
- Device & OS
- not provided
- Scenario
- Loading processed or queried data into a final Excel worksheet.
- Observed behavior
- Power Query previews load fast, but the final output result loads extremely slowly due to query dependencies, workbook calculation, formatting, or Excel resource constraints.
Save a backup of your workbook and ensure your computer has sufficient available RAM before modifying your Power Query connections and calculation settings.
Optimize Query Loading and Connections
Reduce the load on Excel by changing intermediate queries to connection-only and removing unnecessary columns or merges.
Often, intermediate queries that are only used to merge or append data are mistakenly loaded into worksheets. Setting these to 'Connection Only' prevents Excel from drawing unnecessary tables, saving significant processing time.
Open your Excel workbook, navigate to the 'Data' tab on the ribbon, and click 'Queries & Connections' to open the side pane.
Right-click on any intermediate query (queries not needed as a final table in the worksheet) and select 'Load To...' from the context menu.
In the Import Data dialog, choose 'Only Create Connection' and click 'OK'.
Double-click your main query to open the Power Query Editor. Select any columns you do not need in the final output, right-click the header, and choose 'Remove' before clicking 'Close & Load'.

Disable Background Refresh
Disabling background refresh prevents Excel from queuing multiple data tasks simultaneously, which can cause the application to lock up or run slowly.
Adjust Workbook Calculation and Formatting
Complex formulas or conditional formatting applied to the output table can severely slow down the data writing process when the query refreshes.
Experience Faster Spreadsheet Processing with WPS Office
If you are constantly struggling with heavy Excel files, resource-draining add-ins, and slow query processing, consider trying WPS Office. It provides a lightweight, highly compatible, and completely free alternative for handling your spreadsheets, data analysis, and everyday office tasks without slowing down your computer.
- 1. Download WPS Office: Visit the official WPS website to download and install the free WPS Office suite on your device.
- 2. Open Your Spreadsheet: Launch WPS Spreadsheet, click 'Open', and select your existing heavy Excel file.
- 3. Process Data Smoothly: Experience faster loading times and smoother data manipulation without the heavy resource usage of standard Office applications.

Frequently Asked Questions
Why is my Power Query preview fast but the loading is slow?
The Power Query Editor only loads a preview of the top 1,000 rows into memory, making it incredibly fast. However, loading the query to a worksheet processes the entire dataset, writes it to the Excel grid, recalculates dependent formulas, and applies cell formatting, which takes significantly more time.
How does conditional formatting affect Power Query performance?
When data is loaded into an Excel worksheet, the application must evaluate the conditional formatting rules for every new cell generated by the query. If the rules are complex, overlapping, or applied to entire columns rather than specific data ranges, this can cause massive delays during the table writing phase.
What does 'Only Create Connection' mean in Power Query?
'Only Create Connection' means the query pulls and processes the data in the background but does not print the final table onto a visible Excel worksheet. This is highly recommended for intermediate queries, such as lookup tables used for merges, to save system memory and loading time.
Can upgrading to a 64-bit version of Office help speed up Power Query?
Yes. The 32-bit version of Office is artificially limited to 2GB of RAM. If you are processing large datasets or complex queries, upgrading to the 64-bit version allows Excel to utilize all available memory on your computer, drastically reducing load times and preventing out-of-memory crashes.




