Home » Questions » Computers [ Ask a new question ]

Huge Excel spreadsheet taking too long to update links or calculate formulae

Huge Excel spreadsheet taking too long to update links or calculate formulae

I have an Excel spreadsheet with 5000 rows and columns till AY (size 12MB). Except for the first six columns, the rest contain either vlookups or other formulae. All the vlookups are in a separate Excel worksheet. I have changed the Excel setting to manually update the links and calculate formulae. Now every time I try to update the links, either Excel hangs or it takes something like 15 minutes.

Asked by: Guest | Views: 265
Total answers/comments: 4
Guest [Entry]

"Unfortunately, that's the way it is with Excel spreadsheets... VBA isn't exactly the fastest option out there.

You might want to consider migrating the data to a Microsoft Access database."
Guest [Entry]

Maybe get a faster computer or upgrade your RAM
Guest [Entry]

"I agree with Mike Woodhouse. This is not a lot of data.

As a test I would make a new spreadsheet with the data from both spreadsheet merged into 1 new spreadsheet. This will tell you if the issue is happening because the data is in two separate spreadsheet. This should not be an issue but it will be a good test."
Guest [Entry]

"I don't know if this is any help for your situation since it involves database links, but I had the same problem except that it was taking way too long to update a spreadsheet running a database query.

The way I sped things up by a factor of 15 was by first making the query select the necessary data instead of making Excel filter it. Second, in the Connection Properties window of my DB connection, I checkmarked the option ""Remove data from the external data range before saving the workbook."" This helped tremendously so that my file was not continously growing in size with the larger amounts of data I may be querying up.

Now my entire refresh takes around 2-3 seconds instead of 30-45."