Excel taking 4 mins to Update Links to another Workbook
An user has an Excel Workbook (.xls) of about 1.5MB. She complains that many of the linked cells in her workbook are blank, although they contains formulae. Closer inspection of the workbook shows that there are hundreds if not thousands of links to another workbook (a 3MB file which is on the same local network share as it).
When she opens it, Excel 2003 asks if she wants to update the links. If she clicks Update, then it takes almost four minutes for all the links to update, despite the fact that they are all referencing the same worksheet. If she clicks on 'Don't Update', then many of the cells are blank, even ones with formulae in them. The annoying thing is, even when she goes through the process of Updating links and then saves the Workbook. She might open it another day and a quarter of the cells are blank again - ones that definitely had values in them the last time the workbook was saved.
I realise that updating the links would show the values again, but this is not an option for her because it takes so long. I also realise that an Access database would be more suitable for her needs, but that would be more a long-term answer for her. I'm looking for something short-term at the minute.
One thing to note is that, when Excel is updating, the CPU is hammered at 98%-99% throughout, rendering the PC more or less useless while it happens.
I have tried this with Excel 2010 and the Updating takes only about 2 mins, but I think this is more due to the fact that the Excel 2010 is installed on a much more powerful machine.
I read somewhere that, say for instance you had 500 links to the same wookbook, Excel actually opens and closes the file 500 times to get all the data, rather than opening it once, do 500 lookups, and then close it. If there a way to change this behaviour perhaps?
The user would be happy with one of two solutions - either reduce the amount of time it takes to update, or correct the fault in Excel that shows the cells as blank.
Can anyone shed any light on this issue? Thanks.
When she opens it, Excel 2003 asks if she wants to update the links. If she clicks Update, then it takes almost four minutes for all the links to update, despite the fact that they are all referencing the same worksheet. If she clicks on 'Don't Update', then many of the cells are blank, even ones with formulae in them. The annoying thing is, even when she goes through the process of Updating links and then saves the Workbook. She might open it another day and a quarter of the cells are blank again - ones that definitely had values in them the last time the workbook was saved.
I realise that updating the links would show the values again, but this is not an option for her because it takes so long. I also realise that an Access database would be more suitable for her needs, but that would be more a long-term answer for her. I'm looking for something short-term at the minute.
One thing to note is that, when Excel is updating, the CPU is hammered at 98%-99% throughout, rendering the PC more or less useless while it happens.
I have tried this with Excel 2010 and the Updating takes only about 2 mins, but I think this is more due to the fact that the Excel 2010 is installed on a much more powerful machine.
I read somewhere that, say for instance you had 500 links to the same wookbook, Excel actually opens and closes the file 500 times to get all the data, rather than opening it once, do 500 lookups, and then close it. If there a way to change this behaviour perhaps?
The user would be happy with one of two solutions - either reduce the amount of time it takes to update, or correct the fault in Excel that shows the cells as blank.
Can anyone shed any light on this issue? Thanks.
jppinto
One thing to do is to check if there are any broken links that are taking more time to try to update. On Excel 2003 go to Edit-Links.
Hello,
there is probably no quick fix to this, other than using a more powerful machine.
How are the links established? Are they part of formulae? Maybe volatile formulae, which will take a big toll on calculation speed?
Check out this article by Charles Williams and then have a look at the nature of the formulae that link to the other file
http://msdn.microsoft.com/en-us/library/aa730921%28v=office.12%29.aspx
You may find that the solution lies in a redesign of the spreadsheet. Probably not what you want to hear.
cheers, teylyn
there is probably no quick fix to this, other than using a more powerful machine.
How are the links established? Are they part of formulae? Maybe volatile formulae, which will take a big toll on calculation speed?
Check out this article by Charles Williams and then have a look at the nature of the formulae that link to the other file
http://msdn.microsoft.com/en-us/library/aa730921%28v=office.12%29.aspx
You may find that the solution lies in a redesign of the spreadsheet. Probably not what you want to hear.
cheers, teylyn
I would also recommend a new, more powerful computer to help reduce this time. Also, where is the linked file located? On a network drive? Is the user accessing this drive by wireless lan or corded network?
4 minutes to update this amount of links is not, in my opinion, to much. It can be slightly reduced by changing the computer and network conection but I don't believe that you could get this 4 minutes reduced...
4 minutes to update this amount of links is not, in my opinion, to much. It can be slightly reduced by changing the computer and network conection but I don't believe that you could get this 4 minutes reduced...
jppinto, the linked file is on the same network share as the file linking to it. The asker said so in the initial question. :)
OK teylyn, the question is so long that I got lost in the middle... :)
ASKER
jppinto - I can confirm that there is nothing in the Edit -> Links dialog to suggest that there are broken link. There is only one file referenced anyway. Clicking on Check Status gives a status of 'OK'.
Yes, the PC is connected via ethernet cable rather than wireless.
teylyn - I will have a look at the MSDN article.
Regarding the PC's power, another PC that I tried it on was a 2.6GHz Core 2 Duo with 4GB or RAM, Windows 7 with Office 2010 - and this still took 1 min 45 secs. Another of my test PCs is actually a Citrix Xenapp server, which is an Intel Xeon 2.8GHz 64-bit, Windows Server 2008 R2, and this still takes around 1 mins 45 secs. It seems that the 1:45 is some kind of lower limit for this spreadsheet.
I don't think this is a network issue, because Excel.exe is taking 99% of the CPU, so CPU seems to be the limiting factor here.
OK, from what you both are saying, I concede that there may not be anything that could be done about the Update speed.
But there is another issue here, namely that there are many fields in the spreadsheet that are blank, even after they were previously Updated and saved. The impact of this is that there are other cells in the spreadsheet that add them up in a SUM function. So these cells then become incorrect, because the data that is being summed up is not all there. This is surely a bug in Excel.
Do you know anything about this problem?
Yes, the PC is connected via ethernet cable rather than wireless.
teylyn - I will have a look at the MSDN article.
Regarding the PC's power, another PC that I tried it on was a 2.6GHz Core 2 Duo with 4GB or RAM, Windows 7 with Office 2010 - and this still took 1 min 45 secs. Another of my test PCs is actually a Citrix Xenapp server, which is an Intel Xeon 2.8GHz 64-bit, Windows Server 2008 R2, and this still takes around 1 mins 45 secs. It seems that the 1:45 is some kind of lower limit for this spreadsheet.
I don't think this is a network issue, because Excel.exe is taking 99% of the CPU, so CPU seems to be the limiting factor here.
OK, from what you both are saying, I concede that there may not be anything that could be done about the Update speed.
But there is another issue here, namely that there are many fields in the spreadsheet that are blank, even after they were previously Updated and saved. The impact of this is that there are other cells in the spreadsheet that add them up in a SUM function. So these cells then become incorrect, because the data that is being summed up is not all there. This is surely a bug in Excel.
Do you know anything about this problem?
Can you post a few of the formulas that sit in the cells that appear blank?
In the article I linked to above, the author says that linking sheets is a major source of spreadsheet slowness and should be avoided. If you link a 1.5 MB file to a 3MB file, four minutes does not seem all that bad, really. There must be a lot of cells to look up.
Depending on the nature of the links (again, please post some example formulas) this could well be the main reason.
cheers, teylyn
In the article I linked to above, the author says that linking sheets is a major source of spreadsheet slowness and should be avoided. If you link a 1.5 MB file to a 3MB file, four minutes does not seem all that bad, really. There must be a lot of cells to look up.
Depending on the nature of the links (again, please post some example formulas) this could well be the main reason.
cheers, teylyn
ASKER
Hi, here is an example of a formula. All linking formulae are in this form...
=IF(AND($I20>0.01,$I20<1.0
Can you suggest any way that that could be simplified? Thanks.
=IF(AND($I20>0.01,$I20<1.0
Can you suggest any way that that could be simplified? Thanks.
Hello,
try to open the HTMC Rates 2011.xls workbook, then open the workbook that links to the HTMC Rates 2011.xls file. With the source workbook open, the lookups should be a lot faster.
cheers, teylyn
try to open the HTMC Rates 2011.xls workbook, then open the workbook that links to the HTMC Rates 2011.xls file. With the source workbook open, the lookups should be a lot faster.
cheers, teylyn
ASKER
Hi, yes I thought that this would improve the times too.
But unfortunately it doesn't affect the link update time at all.
Any ither ideas?
With the fact that there is only one worksheet in one spreadsheet being referenced in all this, is there a way of sort of 'hosting' the source worksheet inside the destination, i.e. is Excel 2010 clever enough that I could drag the worksheet into the destination workbook and have it not copy the sheet, but just provide links to all cells of the source. Do you know what I mean? So, in the destination workbook, I would have an extra worksheet, which is a replica of the source worksheet, but not a copy, just a worksheet full of links to the original.
But unfortunately it doesn't affect the link update time at all.
Any ither ideas?
With the fact that there is only one worksheet in one spreadsheet being referenced in all this, is there a way of sort of 'hosting' the source worksheet inside the destination, i.e. is Excel 2010 clever enough that I could drag the worksheet into the destination workbook and have it not copy the sheet, but just provide links to all cells of the source. Do you know what I mean? So, in the destination workbook, I would have an extra worksheet, which is a replica of the source worksheet, but not a copy, just a worksheet full of links to the original.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
This method drastically improved the update time. Thanks.