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 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.
- 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.
- 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.
- 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.
