Formula heavy, slow opening file

cappleby

New Member
Joined
Feb 5, 2010
Messages
14
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.
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
After deleting the unnecessary columns you would need to save the workbook to reset the used range. Have you tried deleting one column at a time? Also have you tried saving as an xls and deleting the columns?
 

cappleby

New Member
Joined
Feb 5, 2010
Messages
14
Thanks for the response. Though I tried saving as an xls last week to see if the situation improved, I was unaware at that point of the cell range problem. Good suggestion. I tried deleting as few as 6 cells just a few minutes ago, and Excel just locked up completely. i don't have a clue what's going on here. It's as if every action I take in this file creates some sort of residual mess that makes each subsequent action take longer and longer until it eventually hangs and I have to close out completely, and sometimes even reboot.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,492
Messages
5,832,010
Members
430,103
Latest member
BIGGAZ

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top