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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.