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

buho6867

New Member
Joined
Sep 30, 2013
Messages
13
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.
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
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? :)
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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!
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,108
Members
452,302
Latest member
TaMere

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
Back
Top