Structured Excel Tables are Slow

[Jeffrey Mahoney]

So I really like Excel Tables; they give me a better way to reference data and read formulas. They also allow me to select columns and rows of data without having to scroll to the bottom or to the right.

I recently converted one of my larger workbooks from standard ranges to Structured Tables. It has about 7 tables of supporting data with a maximum of 500 rows and 20 to 150 columns each. My main table uses Vlookup formulas inside the name manager to pull data from the 7 tables. The Master Table has 130 columns by about 430 rows (~54,000 formulas)

After the conversion, my entire workbook takes up to 10 seconds to calculate if I change one value in a supporting table. Before, the calculation time was negligible.

Is anybody else experiencing this? Have you changed any methods to improve the speed?


Named Range Formula: This looks up the value from the table and divides the value by 1000 if the Div cell isn't zero. Cell A16 is the Row Reference to look for. Cell B5 is the column reference. B7 is the divisional value
=LET(ACWP,VLOOKUP(MasterData!$A16,WDTbl,MasterData!B$5,FALSE),Div,SUM(MasterData!B$7),IFS(ACWP=0,"",Div>0,ACWP/Div,TRUE,ACWP))

 

[awoohaw]

Have you considered moving them into the data model? I don't know if that would affect other processes.

 

[Alex Blakenburg]

In the early days I used to find that adding data to a table was slower but I think MS fixed because I seemed to stop having the issue.
What did that same formula look like before the conversion to a Table ?
How many columns does WDTbl include ?
Am I reading it right and you have that LET statement in the Name Manager ?

 

[Jeffrey Mahoney]

I have about 5 of those relative named range formulas to get data from multiple tables into one. Yup, LET is a boon to cleaner formulas.

 

[Jeffrey Mahoney]

The WDTbl is smaller, about 10 columns, while the others are much larger.

 

[Jeffrey Mahoney]

The other day, I converted 3 of the larger tables back into named ranges and changed my formulas to look at those. My calc time went from 10 seconds to 2 seconds. Same amount of data.

 

[Alex Blakenburg]

That seems rather damming. I still like wouldn't mind seeing the non-table version of the formula and how both versions actually looks in the name manager.
In the non-table version how are you making the data range dynamic ?

 

[MartyCollins]

Did you try Xlookup?- vlookups are semi volatile I believe. They recalculate if any data in the array changes, even if it’s not in the lookup or return column. However Xlookup only recalcs when lookup or return column have changes.
I do notice myself that tables can be a bit laggy.

 

[Alex Blakenburg]

@MartyCollins I figured XLookup wasn't option since I am assuming that Jeff needed to access the return value by column no.

VLOOKUP(MasterData!$A16, WDTbl, MasterData!B$5, FALSE),

Index Match would be more efficient in that scenario.
I think your confirmation that you are also finding it laggy will be very helpful.

I would still be interested in seeing the before formula set up, in case there is anything in the conversion process that would account for a slow down.

 

[MartyCollins]

Oh yeah that’s a very good point.
I wonder if the original formulas had smaller arrays, but the whole table is now the table/array in the vlookup and more likely to be recalculating.