Data Tables Slowing Down Excel

[WindsorKnot]

I'm using a model which has 8 different Data Tables. (No I did not create). I currently had the calculation mode set to manual. However, when I go to save, the file takes over 5 mins to complete the saving process, while it is no more than 4MB in size. Is there any way I could speed up the data table process, either manually or through a macro?

Thanks.

 

[Dan DeHaven]

WidnsorKnot, I feel your pain!<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I had a similar problem with a commission calculation file that had at least that many tables and way too many SumProduct and Vlookup formulas with everything referring to practically each table. The file was slow in Excel 2003 but then a few years back when I switched it to Excel 2007 it became horrible beyond words. When I turned calculations to Automatic it would take over a half an hour to calculate and Saving took closer to an hour. I was running a very fast two chip processor with tons of RAM so it wasn't the machine.<o></o>
<o></o>
After some apprehension I re-built the entire file from scratch in Excel 2007, replaced the SumProduct formulas with SumIfS formulas and the calculation and save time was cut down about 90%. <o></o>
<o></o>
For quick fixes try these:<o></o>
1. If you're in a hurry and don't want to recalculate everything use "Shift + F9" (which only calculates the Active Sheet). Often there are only a couple sheets that require recalculation based on changes that you've made. <o></o>
<o> </o>
2. If you have sheets that aren't tables but summarize the table data, use the "Calculation Option" "Automatic except for Data Tables". <o></o>
<o> </o>
3. Oh yeah, in the Excel Options go to the Formulas section and under Calculation Options select Manual and then un-check Recalculate workbook before saving. That will also save you valuable time and allow you to save and move on much quicker.<o></o>
<o></o>
Best of Luck,<o></o>

Dan DeHaven<o></o>

 

[wigi]

There are no hard and fast rules for this kind of behavior, I'm afraid.

It might be that you have way too much data, or very inefficient calculations (that should perhaps be replaced with VBA-code), or ...

On each of the sheets, if you hit Ctrl-End, where do you arrive? Is this far away from the cell that you thought is the last one on the sheet? Delete rows and columns in between then.

Well, not much sensible things to say about it, I'm afraid. Can you post the file to some free upload site like Rapidshare?