Improve Excel XLOOKUP, PivotTable, and SQL Refresh Performance
Question details
The user needs to improve workbook performance when SQL imports, XLOOKUP formulas, PivotTables, filters, and slicers recalculate together.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A large workbook imports SQL data and uses thousands of worksheet lookups alongside PivotTables, filters, and slicers.
- Observed behavior
- Data refreshes and workbook recalculation are slow because lookup formulas add substantial worksheet calculation overhead.
Identify the column shared by the SQL data and the Excel lookup table, and confirm that the join key has matching data types and unique values where required. Convert the Excel lookup range to a table before opening the data-combination tools.
Replace XLOOKUP formulas with a Power Query merge
Use this approach when SQL data and an Excel table need to be matched during refresh; the join runs during data preparation instead of recalculating thousands of worksheet formulas.
A merge performs the lookup as part of the query and loads the combined result back into the workbook. This reduces worksheet calculation overhead and can make PivotTable and slicer refreshes more responsive.
Select the Excel lookup range, choose Insert > Table, confirm that My table has headers is selected, and give the table a clear name in Table Design.
With a cell in the table selected, open Data > From Table/Range and choose the option to create or load the query.
In the query editor, choose Home > Merge Queries, or use Data > Get Data > Combine Queries > Merge, depending on the Excel version.
Choose the SQL query and the Excel table query, select the matching key column in each preview, and select the appropriate join type, usually Left Outer.
Select the expand button on the merged column, choose only the fields needed from the lookup table, then select Home > Close & Load and refresh the PivotTables from the resulting table.
Reduce calculation work in the existing workbook
Use these checks when a full query merge is not possible or when the workbook remains slow after moving the lookup logic.
Move large Excel workbooks to WPS Spreadsheet for lighter data analysis
WPS Office provides a free, lightweight spreadsheet environment with a familiar interface and support for common Excel workbooks. Move the workbook to WPS Spreadsheet to review formulas, tables, filters, and PivotTable-style analysis without relying on a heavy Microsoft Excel installation, while preserving widely used Office formats for migration.
- 1. Install WPS Office: Download and install WPS Office, then open the workbook in WPS Spreadsheet from the Home screen or File > Open.
- 2. Review lookup formulas: Use Find to locate XLOOKUP formulas, then inspect their lookup ranges and replace unnecessarily broad full-column references with table columns where appropriate.
- 3. Check workbook data objects: Review tables, filters, PivotTables, and external data connections from the Data and Insert tabs, and remove unused fields or objects that increase recalculation work.
- 4. Save an Excel-compatible copy: Choose File > Save As, select Excel 2007-365 Workbook (*.xlsx), and save a separate copy before sharing it with Microsoft Excel users.
Frequently Asked Questions
Why are XLOOKUP formulas making my Excel workbook slow?
Thousands of XLOOKUP formulas recalculate whenever imported data, filters, slicers, or PivotTables change. Full-column references and large lookup arrays increase the calculation load further.
Can I replace XLOOKUP with a SQL join?
Yes. If the source data is in SQL, perform the lookup as a database join when possible. Joining the Excel table to the SQL query in the data-preparation stage avoids repeated worksheet-level lookups.
Where is the Power Query merge command in Excel?
Select Data > Get Data > Combine Queries > Merge, or open a query and choose Home > Merge Queries in the Power Query Editor. The exact menu wording can vary by Excel version.
Which join type should I use for an Excel lookup table?
Use a Left Outer join when you want to keep every row from the primary SQL query and add matching fields from the Excel table. Select the columns containing the same key in both sources.
What should I check if the merged results contain blanks?
Confirm that both join columns use compatible data types and contain matching values without extra spaces or inconsistent formatting. Also verify that the correct columns and join type were selected.
