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,

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..:-)

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.)

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!