Tables have a bad reputation for performance.
Zack (Firefytr) Barresse (who wrote the definitive guide to Excel Tables with Kevin (Zorvek) Jones) recommends a limit of around 10K rows for tables if you want to keep performance reasonable.
Prompted by a thread on the Excel-L forum I thought I should spend some time researching this.
Example Problem
Eric Lacroix kindly posted a test problem on Dropbox. I have simplified it further to make it clearer what is going on.
The workbook has a Table with 15000 rows and 2 calculated columns.
Columns C and D contain COUNTIFS formulas referencing columns :
XL 2013 Table Calculation Speed
A full calculation on this workbook with Excel 2013 takes 3.6 seconds on my 4.5GHZ I7 6700K.
XL 2013 Table Editing Speed
In Manual Calculation mode if you copy Column B2:B15000 (which do NOT contain any formulas) then doing a Past Special Values back onto column B takes 1.9 seconds!
There is no calculation time involved in this operation and none of the formulas in columns C and D are recalculated or re-evaluated. It is just the paste values operation that takes the time.
XL 2013 Range Calculation Speed and Editing Speed.
If you convert the table to a normal range, which converts the structured references to normal range references, then
- Full Calculation still takes 3.6 seconds
- But the Paste operation takes about 2 milliseconds! About 1000 times faster.
So the slowdown is :
- Not caused by Calculation
- Caused by Structured References
Bypassing the problem in Excel 2013
After doing some more research I discovered that the problem is caused by Excel 2013 being extremely slow to flag all the formulas containing structured references to the data in Column B for recalculation (make them dirty).
And its only slow if the formulas are not already dirty (but note that doing a recalculation automatically “cleans” all the formulas)
If you set the ForceFullCalculation property of the workbook to true then Excel does not bother to dirty formulas. The downside is that Excel then always does a full calculation of all the formulas in the workbook rather than a smart recalculation of only the dirtied formulas.
So it’s a trade-off: faster editing but slower calculation.
Excel 2016 fixes the Problem
I was surprised to find that when I tried to duplicate the problem with Excel 2016 I could not!
The Excel team have fixed the slowdown! (But don’t seem to have told anyone).
Excel and UDF Performance Stuff 
@Charles,
How would the calculation behave if we replace the [@Region] with A2 and retain the [Region] and [Score]
@sam,
speed looks very similar: it still has to dirty the formulas containing structured refs.
Yep that appies to Excel 2010 too. I have a small VBA Sub to print every column in a workbook and log the Calc time of that column. On excel 2010 one of my slowest columns is always in a Table. Funny thing this column doesn’t contain any Formula. On Excel 2016 the overall calculation is about 20% Faster and Table columns dont seem to be a problem any more.
Yes it feels very strange that doing a Range.Calculate on a table column that does not contain any formulas is so slow: thats because using Range.Calculate dirties any structured ref formulas that refer to the data column.
It’s really kinda strange. This particular table also causes the workbook to calculate extremely slow on save. It’s about 10secs in Office 2010 for a FullRecalc. But at save it’t more than 20secs.
Without that table the calculation is much faster.
The Table is around 100rows and 200 Columns in size. And about 150 columns do have really simple +- Calculations in them.