250MB File, 30 million cells, freezing, crashing, etc

[buho6867]

Hello,

I have a very large excel file with around 30 million cells, with approximately 2 - 3 million of those cells being formulas. The file is about 250mb. When doing a sort, adding or deleting columns, or pasting formulas, Excel either can take around 5 - 10 minutes to complete the task, or will freeze / crash. I am not getting any particular error message, instead Excel just turns to not responding.

I have:
Windows 7 Home Premium Service Pack 1 with all Windows Updates Installed
AMD FX 6100 Six-Core Processor 3.3 GHz
8 GB RAM
64-Bit Operating System
Excel 2013 64 bit version

I have tried

• Uninstalling and Reinstalling Microsoft Office
• Switching between Office 2007 and Office 2013
• Switching between 32 and 64bit versions of Excel
• Disabling automatic calculations
• Disabling all other running programs
• Disabling my virus scanner
• Changing process priority to High / Real Time
• Removing formatting and conditional formatting
• Limiting cell references to exact ranges instead of entire columns / rows
• Booting in safe mode and opening excel without macros and in safe mode
• Buying a new I5 400 series computer with Windows 8 (no help - I could open the file and use it but it would still lock up, freeze, crash, etc or take a very long time to do things)
• Buying a new $3,000 Mac Pro with Office 2011 for Mac (It couldn't even open the file, but I think that may have been a mac issue)

Types of Formulas / Examples (each formulas is pasted down 200,000 cells)

• =ROUNDUP( ( (AS9-ROUNDDOWN(AS9,0))* 16),0 )
• =VLOOKUP(AB9,'References'!$A$1:$C$60,3,FALSE)
• =CONCATENATE("SKU",$A9)
• =CONCATENATE(IF(D17>1,"(SKU" & D17 & ") ",""), E17)
• =IF(AND(AE17,NOT(AG17),J17>1,AA17<>"DO NOT LIST"),"Y","N")

Mostly I am just using rounding, VLookups, concatenations, if statements, and basic math. I'm not sure why this would be an issue though if automatic calculations are turned off. Please let me know if you need any more information from me. Thank you in advance for any help I can get.

 

[RoryA]

Have you tried changing your default printer or updating the printer drivers for it?

Also, can I have your Mac Pro if it's no good to you?

 

[buho6867]

Have you tried changing your default printer or updating the printer drivers for it?

Also, can I have your Mac Pro if it's no good to you?

Thanks for the reply. What does the printer have to do with the excel file?

 

[juankalsa]

Hi, I have the same problem with any file with more than 2 millions rows in my work PC

Core i5, 8gb Ram, Win 7, Office 2k13

At my house the files run just fine....

2 core i7 3960x
64gb ram
Windows 8.1
Office 2k13
2 500gb SSD
2 TB Sata drive
4gb Video Card :D

 

[buho6867]

Hi, I have the same problem with any file with more than 2 millions rows in my work PC

Core i5, 8gb Ram, Win 7, Office 2k13

At my house the files run just fine....

2 core i7 3960x
64gb ram
Windows 8.1
Office 2k13
2 500gb SSD
2 TB Sata drive
4gb Video Card :D

Thanks for the info. Could part of the issue be a lack of a SSD?

 

[juankalsa]

Thanks for the info. Could part of the issue be a lack of a SSD?

I Don't think that the SSD is doing this performance Issue.

The SSD will only provide better access to the information but is not going to perform a slow down access.

I guess that maybe the Physical Memory+Virtual Memory could be affecting this!

 

[RoryA]

There's a close link between what Excel renders on the screen and your printer drivers. It seems to have started with 2007 and got more pronounced.