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

microsoft excel

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.

Right now I am working on splitting the worksheet into four instances; where I will update four different Excel sheets in sequence. But it requires manually copying index columns to next stage. It takes 3-4 hours to update the links. Yesterday I started getting error messages that:

Excel does not enough resources to complete the operation.

I was left with no choice, but to split the entire operation into four small steps requiring manual intervention.

I looked at INDEX. Not really applicable on my case. Any ideas on how I can get it done quickly?

Best Answer

5000 rows by 50 columns is not a large worksheet. 12MB is a perfectly reasonable small-to medium file size and you should have no need to do anything so drastic as reworking to a database or something similar.

From your description, the problem appears to be the links. How large are the referenced workbooks? Are they recalculating when you link to them? I like the pre-opening idea: you can see what's happening then.

Is your memory usage (look at Task Manager) exceeding your available physical RAM? Once memory has to be swapped out to disk, things start to slow down a lot.

Beyond the pre-opening and sorting help, I'd suggest taking a look at the non-lookup formulae: can any be converted into array formulae and called once per calculation instead of once per row?

On the VLOOKUP thing: have you tried using INDEX(value_range, MATCH(lookup_key, key_range))? There are situations where it's faster.

You don't say if you have any VBA. If you do, look at how many times VBA functions are called and how much time they take. VBA code that makes a lot of reference to Excel objects can be expensive.

Related Solutions

Excel – What can slow down Excel in copying worksheets, but doesn’t slow down general responsiveness

Find the last row, select down (Shift-down) and delete rows. Same for columns. It could be that someone applied a formatting on 1M lines.

Excel – How to update automatically and continuously cell values in the master workbook, while the source workbooks are edited from different computers

Nice formatting of your question!

There is no way to turn that on, but there are ways to get it to do it automatically. Enable the developer ribbon if you haven't already (if you have full Excel) and in that ribbon click "Visual Basic"

You want to stick ActiveWorkbook.RefreshAll somewhere. Maybe by editing 'ThisWorkbook' and adding the following code:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    ActiveWorkbook.RefreshAll
End Sub

so that it happens every time you switch tabs?

Or maybe by editing the master sheet's VBA code and adding ActiveWorkbook.RefreshAll somewhere in there, like in the Worksheet's Worksheet_Activate or Worksheet_SelectionChange events?

Once you get that working you could also try going to https://stackoverflow.com/questions/211715/how-to-make-a-macro-which-executes-periodically-in-excel to have it periodically update every n seconds instead.

Best Answer

Related Solutions

Excel – What can slow down Excel in copying worksheets, but doesn’t slow down general responsiveness

Excel – How to update automatically and continuously cell values in the master workbook, while the source workbooks are edited from different computers

Related Question