Forum Discussion
Excel Workbooks with external workbook links loading extremely slowly
Using Excel via Office 365, I have noticed a dramatic increase in the amount of time required to open any member of a group of files, each of which have links to other workbooks.
These are files that I open daily, and have opened daily for quite some time. Each of them have links to other workbooks, very simple in nature, simply referencing cells in the other workbooks; no calculations are performed in the reference.
The files themselves are fairly small, on the order of 2 MB. They previously opened in 2-8 seconds, I would guess. Now, they can take up to 30 minutes(!) to open.
I noticed this first on the morning of May 25th. No 365 updates are shown for the 24th or 25th; however, I did have a new message from Windows on my computer that morning letting me know that service for Windows 10 would be ending soon.
All other Excel files open totally as normal.
Anyone have ANY idea how to make this make sense?
I fixed this issue for myself by changing from each cell having an individual formula, to the sheet using an array instead.
You do this by selecting all of the cells you want to take from the other document, going to the other document, selecting all of the data on that side, then hitting Ctrl + Shift + Enter.The formula will end up looking like this
{=path/[data_document.xlsx]Sheet1'!A1:Z500}
FAIR WARNING: If you do this over empty cells, they will become 0 instead of empty, so if you have code that expects a string, you will get a data type mismatch error.
- I am having the exact same issue. I have these files on my local drive so I should not be accessing the web to open them. This is a real issue because I built a lot of important files using this feature.
- Same here, all of my files are stored locally so the issue is not web related as far as I can tell.
I am also having this issue.
One workbook is 15 MB, and it takes 7 minutes to open, where before today it took 10 seconds.I made a small test workbook that is 14 KB that pulls from a blank workbook. The test workbook takes 60 seconds to open.
In my testing I have noticed that if you already have the sheets open where the workbook needs to pull from, it opens quickly (ie: I opened the blank workbook, and the test workbook opened almost instantly after, instead of 60 seconds). It seems to be some recent super slowdown with the workbook links.Unfortunately that is not possible given the size of the source workbooks that I have. If I opened all of them it would crash Excel.
- I've found the same, if the workbooks are open they work as before, no significant lag of any kind. The delay is simply in opening the document. And in my case at least, some documents link to quite a few other workbooks; opening every single one would be beyond inconvenient. The whole point is to consolidate various data into a single source to provide quick overviews, at least the way I use it. Thanks for the input!
- Same issue here massive regression this need to be fixed asap.
I fixed this issue for myself by changing from each cell having an individual formula, to the sheet using an array instead.
You do this by selecting all of the cells you want to take from the other document, going to the other document, selecting all of the data on that side, then hitting Ctrl + Shift + Enter.The formula will end up looking like this
{=path/[data_document.xlsx]Sheet1'!A1:Z500}
FAIR WARNING: If you do this over empty cells, they will become 0 instead of empty, so if you have code that expects a string, you will get a data type mismatch error.
- Great information! I tested this with a workbook linking to 14 other workbooks, and the load time was effectively instant.
The issue with empty cells is not relevant for my needs, but at the very least the workbook opens quickly enough to be usable, and if empty cells are an issue they could be dealt with on the imported side.
While I would still be fascinated to learn why this change of performance occurred so suddenly after many years of consistency, I'm really glad to have a workable solution. Perhaps Excel is just built to favor working with arrays now. At any rate, hope this helps everyone else looking for a solution as well. - Sorry but this is not a long term fix...
- Certainly not a long-term fix for all situations, although it does help for instances where entire ranges of cells are being referenced. It seems like every instance of a formula referencing an outside workbook increases the loading time, so any way to reduce the number of formulas helps.
As for what caused this to happen and why it has not been addressed, I have no idea. I'm sure there are many, many users being affected by this, and many probably have not yet made the connection between slow load times and external references. Needs to be resolved ASAP.
- Thank you for the workaround. Unfortunately, that won't work for my application because I can't have zeros and I use the data in the links to create custom text.
- It probably still won't totally work for you but you *can* use the array reference as part of an IF statement, in the form
IF(ExternalWorkbookReference="","",ExternalWorkbookReference).
This will leave all of your imported empty cells as empty cells but will still not increase load time.
- I have same issue since a week, with excel template pointing on 6 other excel files. Where is MS team? They definitely changed something that create this issue... looking for a fix asap
- Whatever the problem was it seems to have been resolved this morning. I tried to run my macro last night and it took 7 hours to run. This morning I made some changes to the data and ran the macro again and it was done in 5 minutes, same as it has been for the last 5 years.
- Same here, problem has magically resolved itself. Guess we'll never know what happened....
