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.
Who is Participating?
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.

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


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.
Roy CoxGroup Finance ManagerCommented:
Try saving the file as xlsb., this format is designed to be used with larger amounts of data.
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.
agwalshAuthor Commented:
It's always the simple things isn't it? Great advice as always..
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
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.