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.
Excel and UDF Performance Stuff 
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.
This could be confirmed if using Trace Dependents from within a table that is referred to from, say, another sheet does not work correctly. . Indeed, this is the case, as outlined in a post I did at http://dailydoseofexcel.com/archives/2014/09/14/formula-auditing-woes-and-arrows/
Furthermore, it appears that changing the number of columns in a Table causes any UDFs in the workbook to recalculate, as per a question I posted at http://stackoverflow.com/questions/29815150/need-to-stop-udfs-recalculating-when-unrelated-cells-deleted
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
.
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
– …
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.
@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..:-)
Anyone find out a work around to this table updating issue? I have loved the switch to tables, but this issue is killing me.
Just break one of the prerequisite conditions:
◾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.
Thank you for sharing your prerequisite conditions!
This solved my vba performance problem until:
1. I did some debugging in VBA
2. Somehow, even though application.screenupdating = false the Worksheet with the Table does get updated on screen (probably a feature of the debugger).
3. And this activates the Worksheet with the Table.
The most strange behavior is that once I delete the entire worksheet with the table and have it been recreated by running my vba code again, it remains slow, even when I do not activate the debugger.
The only way around this is to quit Excel, open the Workbook again, and then rerun the vba.
With a fresh Excel restart my vba runs quickly and smoothly again.
It seems that even Excel has a ‘setting’ that once activated it will keep recalculating everything.
Hopefully this is helpful to somebody to make the perfect workaround. (Or even MS may pick this up to further improve a wonderful product.)
Thank you so much for figuring this out! I had a large table (50k rows) and was parsing/updating over several hours (running VBA). Now it only takes a few minutes and I only had to activate the next sheet.
This was incredibly helpful!
I was banging my head on my desk on why writing 500 cells cost 120sec, until I found this post. Just activating another sheet in my book saved the day.
please accept my gratitude!
I added a line to the vba to select a cell outside the table before executing the rest of the code. Thanks for the post