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

 

[Alex Blakenburg]

Me too.

 

[Jeffrey Mahoney]

Below are all the formulas and named ranges I use for the 5 major tables. This morning I again changed the named ranges that the Master Data formulas use from Structured Tables to Standard, Non-Dynamic, named ranges. My calculation time with the structured table references was about 12 seconds on average. My calculation time with standard named range references was 3 to 4 seconds.

As another frame of reference, I also changed those same standard named ranges to dynamic named ranges. Look at the very bottom for that named range formula. My calculation time dropped to 1 second or less. I attribute that to the number of rows in the dynamic named ranges went from about 1,000 to actual number of rows in the tables.

---------------------------------
Here is the main formula that I use on the MasterData sheet. This keeps it clean and I don't have to create special formulas for each table I want to pull from. Row 3 is where I add the values Header Data, Cashflow, ACWP, SAP, and Depreciation so it knows which table to get data. It is calling each of the Named Range formulas below

=IFERROR(IFS(B$3="Blank","",B$3="Header Data",GetHeaderData,B$3="Cashflow",GetCashFlow,B$3="ACWP",GetACWP,B$3="SAP",GetSAP,B$3="Depreciation",GetDepreciation),"")

Each of Named Range formulas to pull data based on the Structured Table

=LET(ACWP,VLOOKUP(MasterData!$A14,WDTbl,MasterData!B$5,FALSE),Div,SUM(MasterData!B$7),IFS(ACWP=0,"",Div>0,ACWP/Div,TRUE,ACWP))
=LET(GCF,VLOOKUP(MasterData!$A14,CashFlowTbl,MasterData!B$5,FALSE),Div,SUM(MasterData!B$7),IFS(GCF=0,"",Div>0,GCF/Div,TRUE,GCF))
=LET(Dep,VLOOKUP(MasterData!$A14,DepTbl,MasterData!B$5,FALSE),IFS(LEN(Dep)=0,"",SUM(MasterData!B$7)>0,Dep/MasterData!B$7,TRUE,Dep))
=LET(GHD,VLOOKUP(MasterData!$A14,HeaderDataTbl,MasterData!B$5,FALSE),Div,SUM(MasterData!B$7),IFS(GHD=0,"",Div>0,GHD/Div,TRUE,GHD))
=LET(SAP,VLOOKUP(MasterData!$A14,SAPDataTbl,MasterData!B$5,FALSE),Div,SUM(MasterData!B$7),IFS(SAP=0,"",Div>0,SAP/Div,TRUE,SAP))

Here are the named ranges for the structured tables that Excel creates when you turn a range into a table

HeaderDataTbl: ='Header Data'!$A$4:$AN$396
SAPDataTbl: ='SAP Data'!$B$8:$X$435
DepTbl: =Depreciation!$A$5:$P$458
WDTbl: =WD!$A$6:$I$444
CashFlowTbl: ='Cash Flow'!$A$7:$CD$445

All I did was change the above formulas to look at new Named Ranges that ARE NOT Structured Table references

HeaderDataTable: ='Header Data'!$A$4:$AN$1000
SAPDataTable: ='SAP Data'!$B$8:$X$1000
DepreciationTable: =Depreciation!$A$5:$P$1000
WorkDayTable: =WD!$A$6:$I$1000
CashFlowTable: ='Cash Flow'!$A$7:$CD$1000

Basically the same formula but with the standard reference

=LET(ACWP,VLOOKUP(MasterData!$A14,WorkDayTable,MasterData!B$5,FALSE),Div,SUM(MasterData!B$7),IFS(ACWP=0,"",Div>0,ACWP/Div,TRUE,ACWP))
=LET(GCF,VLOOKUP(MasterData!$A14,CashFlowTable,MasterData!B$5,FALSE),Div,SUM(MasterData!B$7),IFS(GCF=0,"",Div>0,GCF/Div,TRUE,GCF))
=LET(Dep,VLOOKUP(MasterData!$A14,DepreciationTable,MasterData!B$5,FALSE),IFS(LEN(Dep)=0,"",SUM(MasterData!B$7)>0,Dep/MasterData!B$7,TRUE,Dep))
=LET(GHD,VLOOKUP(MasterData!$A14,HeaderDataTable,MasterData!B$5,FALSE),Div,SUM(MasterData!B$7),IFS(GHD=0,"",Div>0,GHD/Div,TRUE,GHD))
=LET(SAP,VLOOKUP(MasterData!$A14,SAPDataTable,MasterData!B$5,FALSE),Div,SUM(MasterData!B$7),IFS(SAP=0,"",Div>0,SAP/Div,TRUE,SAP))

Dynamic named range

HeaderDataTable: =OFFSET('Header Data'!$A$3,1,0,MATCH(1E+99,'Header Data'!$A:$A,1)-ROW('Header Data'!$A$3),40)

 

[Alex Blakenburg]

I thought if you had lots of Dynamic named ranges and replaced them with using Tables the performance might actually improve but you seem to be indicating that is not the case.
There are a few things you can do to improve performance but I wouldn't have thought that they would slow using structured references more than non-structured references.
Without being able to play with your file I am afraid I don't have any other suggestions.

Performance Improvement options:
• VLookups are convering very large number of columns. Xlookup or Index-Match would be more efficient.
• IFS - this calculates all the options even if the first one is TRUE. Your calculations for the most part don't look overly onerous but switching to nested IF statement might help.

 

[Jeffrey Mahoney]

I've been working with the dynamic ranges for several days and they seem to be performing well. So, I guess this me putting this out to those that have the ability to tell the MS Excel team that they need to work on making their structured tables more efficient.

 

[Jeffrey Mahoney]

This is a continuation of the story:

The workbook that I mentioned above still had Excel Tables in them even though I referenced the ranges via dynamic named ranges. Last week it went corrupt to the point of not being able to save it by locking up Excel. And in some cases prevented me from saving other workbook while it was open.

I spent three days rebuilding the workbook from the ground up. I tried cutting corners by copying formulas from the old to the new, but it caused the same problem again. I copied values and formatting over to the new WB and recreated the named ranges and formulas from scratch. There were hundreds of unique formulas and named ranges to create. The speed difference is amazing, it's like I put more RAM and a Solid State drive in an old computer!

I had another corrupted workbook and converted it also, along with increasing the number of rows and columns with formulas. With Excel Tables I wasn't able to enter items into cells without a few seconds of calculating. Now, it calculates in a fraction of a second.

From now on, every chance I get, I will be recommending to everybody that they NOT use Excel Tables. It kills me to say that because I really like the features of Tables.

 

[Joe4]

When I hear things like "big tables", "lots of data", "lots of lookup formulas", that is usually a red flag that you have a relational database on your hands.
While you can brute-force Excel to behave like a relational database, it is not really what it was designed for, so tends to be "clunky" and performance can take a hit as data increases.

All things equal, it would be far better to use a tool designed for relational databases, like Microsoft Access or SQL. They handle those things much more gracefully (since that is what they were defined for). By linking tables and creating queries, none of the lookup formulas are necessary, and it is dynamic in nature.

In recent years, Excel came out with "Power Query", which allows you to better do database-type operations in Excel. So if you are unwilling/unable to use Access or SQL, perhaps you want to look at using Power Query in Excel. There is a forum for it here ("Power Tools").