Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

Recent Slowdown in Excel - SQL - PivotTable - Xlookup

Copper Contributor

Hi,
At work I import data from SQL queries into Excel. Some of these data sets are up to 300k rows of data. I then link data sets with Xlookup functions and add on various other calculation into new columns on the data sets. Some Excel files would have multiple data imports and get quite large, while others being much smaller. Due to this size difference, when refreshing the data on the file, some would take 20 seconds to run, while other would take 5min to run. This was fine and understandable.
Then in September 2023 something changed and reports that would take 20 seconds to run, would now take 5min and the ones that took 5min would now take 15min.
I then did some testing, I had a data import which had 250k rows. To the side of this data set I created a new table of 50 rows. I then did an Xlookup on the data import table from the manual created table. I then created a PivotTable report and pressed refresh all. The report took 5min to run and in the past would have taken 20 seconds to run. I then cut the manual table of 50 rows and pated this onto a new sheet on the same workbook, with the Xlookup now looking at the new location. I pressed refresh all and the report took 20 seconds to run. Why would this change have such a dramatic impact on performance?
I have read some things Online around an update from September 2023 talking about real-time calculations and updates in Xlookup.
The main areas I have found to be affected are Xlookup and PivotTables with filters and slicers.
Does anyone know what may have cause this sudden drop in performance. I have over 50 reports created in Excel and I just want them to run how they did prior to September. Rolling back Excel is not an option due to restrictions in my works security.
Any help will be greatly apreciated!

3 Replies
Given that you are pulling in SQL data I wonder if you could dramatically improve things by having the SQL also do the lookups (it's called joining). You can also join data from a SQL database with a table that is in Excel. First use Data, From Table on the table in Excel and subsequently use Data, Get Data, Combine Queries, Merge to join both the SQL and the excel table. No more lookups needed.
Thanks for this, merging the data might help speed things up. I will have a play around with this option.

Do you happen to know if anything changed in Excel to reduce the performance, as it will be quite time consuming rebuilding all of my reports.

@XanderT-J No I haven't heard about recent changes to the calc engine. Best if you open one such model and click Help, Feedback, I don't like something. This will send some anonymized logs to MSFT along with your complaint. If you add your email address, they may even contact you for further information.