Excel File Takes Long Time to Save

Hi, I have been combing through this forum looking for an answer and came across a similar thread.
http://www.excelforum.com/excel-gene...ponding-2.html


If I have overlooked a solution within this forum that was already posted, please excuse me. I honestly have spent about 4-5 hours on google and on this thread looking for the solution.

Problem: Excel takes about 10 minutes in the saving process. When I say 10 minutes, I mean, the excel screen freezes (says not responding) for about 10 minutes, then it actually saves at the very end in the normal time any other file would take as you watch the progress bar go forward.

I know many of the common answers and have tried. But many of the common suggestions have to do with reducing the calculation time (which in turn reduces the saving time).

But in my circumstance, the calculation takes a very reasonable amount of time, and you see the progress % going forward.

- I would say I have about 2000 rows, and 15 columns.
- They have sumifs formulas.
- They link to a different workbook.
- The workbook I am working on saves to the network
- the source of my sumifs are also in the same folder on the network
- the recalculation takes about 10 seconds at most
- i have turned off recalculate before saving, it is all on manual calcs

- when i hit save, there are no calcs being performed
- there are no macros in the workbook
- there are only about 2 names in the name manager
- then it freezes for about 10 minutes.
- then the progress bar starts moving then it saves.

What is it doing in those 10 minutes?

1 more item to note, when I break the links to the workbook and thereby removing the sumifs formulas, its a snap.

Why does the existence of the sumifs extend saving time? I would completely understand if it elongated calculation time, but if calculation is off, then why does it even worry about it when saving?

Hi mikbsirhc ,

Strange indeed! It looks like you have all the bases covered.

Do you have the linked file open or closed when saving?

I had a similar problem a while ago where I noted that one file with a lot of links closed 10 times faster if the both files were open? (I never found out why?)

David


When you reply please make it clear WHO you are responding to by mentioning their name.
If this has been of assistance, please advise. A little thanks goes a long way.
- Please click on the *Add Reputation button at the bottom of helpful responses.
Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

Originally Posted by David A Coop

Hi mikbsirhc ,

Strange indeed! It looks like you have all the bases covered.

Do you have the linked file open or closed when saving?

I had a similar problem a while ago where I noted that one file with a lot of links closed 10 times faster if the both files were open? (I never found out why?)

David


When you reply please make it clear WHO you are responding to by mentioning their name.
If this has been of assistance, please advise. A little thanks goes a long way.
- Please click on the *Add Reputation button at the bottom of helpful responses.
Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

Hi David,
Thanks for your response. Yes, I have tried saving it with the linked file open and closed. Ironically enough, it saves a bit faster (by a few minutes) when the linked file is closed.

Thanks,
Chris Kim (mikbsirhc backwards)

Hi Chris,

Sorry that didn't help. Ironically I had a similar problem last evening. I had a file with 5000+ rows with SUMPRODUCT formulas.

While it took a few seconds to calculate, it took a looooong time to delete the formulas. (I am not on a network).

I don't know a lot about array formulas like SUMIFS and SUMPRODUCT. Perhaps they have to "do their thing" regardless of the AutoCalc settings. We'll have to leave that to the gurus!

Meanwhile, I did find this!

http://social.technet.microsoft.com/...re?forum=excel

This suggests that there can be some conflicts between Excel 2010 and Network servers. Get your IT guys to have a look at this.

That's my best shot!

David


When you reply please make it clear WHO you are responding to by mentioning their name.
If this has been of assistance, please advise. A little thanks goes a long way.
- Please click on the *Add Reputation button at the bottom of helpful responses.
Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

'
hi
I am Mac user,
go to preferences > Calculations > Switch off the - Save external link values
and it works

should be there in windows version also .

Gerry !!

Hi gaurav,
i was having same problem of excel consuming more time while saving.. And your suggestion helped me ... Now it is takes less time then before..
Thank you

regards,
nj