Forum Discussion
Excel Workbooks with external workbook links loading extremely slowly
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 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.
- 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!
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.
