Large Excel file - takes ages to open, or do anything with.

I've got an Excel file (which has data set up from Power Query) which has 5 sheets, and one of the sheets with 300,000 plus rows. The data in the sheets has been formatted as a table.  It's driving me nuts because it takes ages to open, everything takes ages and if I update any formula, it throws a tantrum and then I have a hissy fit :-)  I'm currently storing the file in OneDrive.  Any suggestions on optimising what I am doing? It's 32 bit Excel Thank you as always.
LVL 1
agwalshAsked:
Who is Participating?
 
AlanConsultantCommented:
Some ideas:

Try opening it from your local hard disk.

Try using 64 bit Excel.

Try using a machine with more RAM - 16 or 32 Gb maybe.

Turn off auto calculation.

Alan.
1
 
Neil FlemingConsultant and developerCommented:
In addition to Alan's list, check if you have any VBA code for so-called user-defined functions in the workbook that contain the line

Application.Volatile

Open in new window


"Volatile" functions run every time you change anything in the workbook. If you have a large number of cells containing the same user-defined function, it will run for each of them, one at a time. This can massively slow down the workbook.
0
 
Roy CoxGroup Finance ManagerCommented:
Try saving the file as xlsb., this format is designed to be used with larger amounts of data.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
agwalshAuthor Commented:
Brilliant! Am going to take all these on board...I do *love* Excel except when it starts doing this sort of crap :-)  @Roy Cox - any issues/limitations I should be aware of around saving as an xlsb? @Neil Fleming, have no UDFs in the file - just a dump from Power Query...@Alan - am going to move entire file to a different machine. Thanks for all the advice.
0
 
agwalshAuthor Commented:
It's always the simple things isn't it? Great advice as always..
0
 
Roy CoxGroup Finance ManagerCommented:
There are no issues with .xlsb, it would be interesting to know if it performs better before moving. Pleased to help
1
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.