Excel Data Table is Very Slow to Refresh

Question

I have an Excel data table linked to a query to an Oracle database. This data table has both: (1) additional calculated columns (some involving array formulas & MATCHes) added to the data table (based on the queried values), and (2) many dependent formulas spread throughout other tabs

While the query itself is fast to refresh if added to a brand new Excel file exactly as is, the dependent formulas seem to slow down the refresh EVEN WITH CALCULATIONS SET TO MANUAL.

I've tried several things: 1) Set calculations to manual 2) Disabled screen updating 3) Disabled events 4) Removed calculated columns from data table (just normal formulas)

Nothing seems to help... any ideas? Thanks.

"additional calculated columns" might have heavy Excel formulas, such as array and/or VLOOKUPs? Add some examples. — zx8754
Hi @zx8754, thanks for the comment. Yes, there are some heavy formulas (array, vlookup, etc.), but do you know of a way to temporarily disable them so I can refresh all of the underlying data? Do you know why Calculation=Manual doesn't work? — user2587696
Complete shot in the dark (and only because I had this work with I wkbk I had)..on each sheet, select ALL the blanks rows and do a Clear All. Do the same for all the blank columns. Then save the workbook, close and reopen.. when I had a wkbk that had hundreds of ops across hundreds of thousands of rows, sometimes it slowed the workbook down to a crawl even though the cells appear blank — Jaycal

Scheballs Scheballs · Accepted Answer · 2013-07-30T14:13:20

This link may be of interest to you.

Excel Recalculation http://msdn.microsoft.com/en-us/library/office/bb687891(v=office.12).aspx

Thread Safe Functions http://msdn.microsoft.com/en-us/library/office/bb687899(v=office.12).aspx#xl2007xllsdk_threadsafe

There are many events that can trigger a recalculate event also read up on VOLATILE functions. A Volatile function is one whose value cannot be assumed to be the same from one moment to the next even if none of its arguments (if it takes any) have changed.

The following Excel functions are volatile.
NOW
TODAY
RAND
OFFSET
INDIRECT
INFO (depending on its arguments)
CELL (depending on its arguments)

Recalculation of data tables is handled slightly differently. Recalculation is handled asynchronously to regular workbook recalculation, so that large tables might take longer to recalculate than the rest of the workbook.

Try setting Automatic Except for Data Tables. Perhaps even when set to manual calculate that just means that whenever an event triggers the manual it will calculate the affected cells. Perhaps your refresh is triggering all these events which will calculate multiple times because as the events trigger it more events come in and trigger again.

The easiest thing you could do is look to eliminate as many VOLATILE and ARRAY formulas as possible. Personal exp tells me that RAND and ARRAY functions are the worst.

Excel Data Table is Very Slow to Refresh

1 Answers