Excel 2010 Tables/Listobject: slow update and how to bypass

There was an interesting post on Stack Overflow recently about a performance problem when updating Excel 2007/2010 Tables/Listobjects. Certainly something strange is going on!

Duplicating the Problem

1. Create a table by selecting a few cells (I used A1:A3) on a sheet (Sheet1) and using Format as Table on the Home tab

2. On a different sheet (Sheet2) create a few thousand (I used 10000) of the simplest formula you can think of (I used =”A”)

3. Create a VBA Sub that updates a different cell in the same sheet as the table:

Sub Updater()
Dim j As Long
Dim dTime As Double
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
dTime = Timer
For j = 1 To 10000
Worksheets("Sheet1").Range("d5") = j
Next j
MsgBox Timer - dTime
End Sub

4. Select a cell within the Table

5. Run the VBA Sub

On my system that takes 8.8 seconds.

Bypassing the Problem

For this slowdown to occur each of the following conditions must be true:

  • A cell within the Table must be selected
  • The sheet containing the Table must be the Active Sheet
  • The cell being updated must be on the same sheet as the table, but does not have to be within the table
  • There must be a reasonable number of formulas in the workbook.

So change any or all of these conditions or convert the Table to a Range or delete all the formulas and the update will only take 0.5 seconds on my system.

Whats actually happening?

The time taken is proportional mostly to the number and slightly to the size of the formulas in the workbook, but none of the formulas are actually being calculated.

So it seems to me that each time the cell is updated Excel is scanning all the formulas in the workbook as though they might need to be changed.

Maybe this has something to do with the automatic extension of formulas within a table when you add a new row or the fact that the Table definition and its associated Name has to change if you add a new row.

But the fact that the slowdown only occurs if the Table is on the active sheet means that I think this is a bug.

Can you think of a better explanation?

About these ads
This entry was posted in Calculation, VBA. Bookmark the permalink.

5 Responses to Excel 2010 Tables/Listobject: slow update and how to bypass

  1. Bill Bekenn says:

    I suspect that the new Table structures, with the different syntax for formulas referring to columns and the current row within the tables, are not yet fully integrated into the Dependents ( and possibly Precedents) maps that Excel uses to optimise the calculation algorithm. This will mean that the calculation algorithm optimisation aimed at not calculating cells involved in a change will fail and the whole workbook ends up being scanned for Dependents.

    This could be confirmed if using Trace Dependents from within a table that is referred to from, say, another sheet does not work correctly. I may have a play as I am not sure whether to recommend the use of tables as I think the design has not been fully thought out.

  2. jeff Weir says:

    Hi Charles. I’ve got a question regarding caculation in general. Hope you don’t mind me answering it here.

    First, some info on my system. I’m running Excel 2010 on a Samsung laptop with an intel i5 chip running 64 bit Windows 7 with 8 GB RAM, and I’ve got Calculation Options set to Automatic.

    I’m using around 130 SUMPRODUCT on a 9 Column by 140560 Row excel database (Excel 2010 table), and find that typing anything anywhere into the spreadsheet seems to trigger a recalculation. (By that, I mean I’m getting the message “Calculating : (4 Processors)” and a very long delay even if I try to enter say “1” in cell A1 of an entirely new sheet or in an entirely new workbook).

    I don’t have any INDIRECTS or UDFs or OFFSETS or anything else that I can think of that would make this workbook recalculate on data entry.

    I would have expected this if I was say putting new data into the Excel Table or some other place referenced by the SUMPRODUCT formula. But I didn’t think Excel would care if I was just chucking some constants in some unreferenced cells.

    This spreadsheet has 9 columns of half hourly electricity meter data in it for 8 different sites’ consumption over a year. That’s 8 sites * 48 half-hourly readings * 365 days = 140560 rows.

    Like I say above, there are a large number of SUMPRODUCT formulas that reference this database. 32 of them check the date column to find out whether it is Summer or Winter, then check the Time column to find out if it is Day or Night, then sum up the amount of Electricity used (in kWh). 96 more calculate the average of the top 10 peak capacity times for each month. All use Boolean operators.

    As well as the SUMPRODUCTS there are around 80 INDEX/MATCH formula that fetch tariff rates from other tables etc and apply them to the results of the SUMPRODUCT cells.

    Ultimately this will be moved into SQL Server, but I like to use Excel to build a model that I can then use to check what I come up with using TSQL.

    I know I can turn calculation to Manual, and I know that recalculating these formulas or precedent cells will take time given I’m asking a lot of excel. But I really thought that any new cells wouldn’t be affected.

    As an experiment, I copied the database to a new sheet, closed any other sheets open, and then pointed 154 SUM functions at the entire 9 by 140560 table. Again, when I entered data into somewhere totally unrelated I still saw noticeable delays while Excel did some calculation.
    Is this as you would expect?

    Regards

    Jeff

    .

  3. Hi Jeff,
    Here are some of the possible reasons:

    – somewhere there is a volatile function.
    – you have some VBA code somewhere thats causing the problem
    – some addin is causing it
    – forcefullcalculation is set to true, or your workbook has exceeded excel’s recalc limits
    – named ranges?
    – the workbook has corrupted dependency trees
    – …

  4. jeff Weir says:

    THanks for that Charles. I’m not sure what was going on, but a total restart of my computer seems to have fixed the issue.

  5. sam says:

    @Jeff,

    You can try one or more of the below things to speed up the calcs further.

    1 )Replaces the SP’s with SUMIF’s (provided you have AND criteria (*) in all the SP’s)
    2) Replace the Index/Match combo with a Single Match in a column and Multiple Indexes, i.e Search once pick many times
    3)Replace the 0 option of match with the 1/-1 option i.e sort the column that you are searching on in Asc or Desc, use the IF Formula along with Index, Index to replicate an Exact MATCH behavior with 1 and -1
    4) Send me a PM with a sample data and I can have a look..:-)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s