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.
 

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

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.
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,605
Messages
5,512,379
Members
408,889
Latest member
2vbr

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...
Top