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!
