I have an xlsx file that is roughly 11MB. It is formula heavy (about 500,000 formulas - vlookups and sumifs). However, there are no external data links - every reference is internal to the file. My problem is that the file takes about 6 minutes to open and the same to close, whereas other files i have that are even larger (but have NO formulas) open in mere seconds. Here are the things I've tried, or the things I've made sure of when trying to remedy this problem:
1. Calculate is set to manual
2. Calculate when close/save is OFF
3. Background error checking is OFF
4. All error checking rules are unchecked
5. Hyperthread calculation is ON (dual processor) FYI 2 GIG of RAM
6. Formulas do NOT reference entire columns or rows; they are limited to the appropriate ranges (which are no more than about 8,000 rows, and 20 columns)
7. Formulas are fairly simple (single Sumif statements or vlookups)
Interesting NOTE: I have also done the CRTL + END thing and found that I had some phantom bloat or crap cells way out in left field. To be specific, it went to cell WYK2783, and my report ends at cell GZ2783. However, when I attempt to delete all those "empty" cells, it tells me that it doesn't have enough resources. I select an option that says "continue without undo" (which I assume does something similar to Access when you need to delete more records than it can keep in memory). It then sits there for several minutes (15) locked up. After it finishes whatever it does, CTRL + END takes me back to the same cell as before (WYK2783). Seems to have done nothing. I tried selected a much smaller portion of this phantom range for deletion - same message about undo; same results - apparently does nothing.
Any ideas?! This thing is driving me nuts, and every time I need to work in it, I blow half my day "end tasking" Excel and opening and closing this file.
1. Calculate is set to manual
2. Calculate when close/save is OFF
3. Background error checking is OFF
4. All error checking rules are unchecked
5. Hyperthread calculation is ON (dual processor) FYI 2 GIG of RAM
6. Formulas do NOT reference entire columns or rows; they are limited to the appropriate ranges (which are no more than about 8,000 rows, and 20 columns)
7. Formulas are fairly simple (single Sumif statements or vlookups)
Interesting NOTE: I have also done the CRTL + END thing and found that I had some phantom bloat or crap cells way out in left field. To be specific, it went to cell WYK2783, and my report ends at cell GZ2783. However, when I attempt to delete all those "empty" cells, it tells me that it doesn't have enough resources. I select an option that says "continue without undo" (which I assume does something similar to Access when you need to delete more records than it can keep in memory). It then sits there for several minutes (15) locked up. After it finishes whatever it does, CTRL + END takes me back to the same cell as before (WYK2783). Seems to have done nothing. I tried selected a much smaller portion of this phantom range for deletion - same message about undo; same results - apparently does nothing.
Any ideas?! This thing is driving me nuts, and every time I need to work in it, I blow half my day "end tasking" Excel and opening and closing this file.