Inside formulas, should i use Excel table structural references or Dynamic Named ranges?

[flora1]

I have a big file with many formulas.

currently, it is too slow. because my predecessor has used whole column reference inside formulas.

i want to change the whole column references to either Excel tables or dynamic named ranges.

so my question is, which one should i use to make my excel work faster? I mean for excel formulas to calculate faster

 

[arthurbr]

I don't know if there is a straightforward answer to this.
Excel tables are easy to set up while dynamic named ranges can be somewhat trickier.
I'd suggest you try both on a copy of your data

 

[mole999]

are the formulas volatile ?

 

[thisoldman]

These four MSDN articles by Charles Williams, with David Chestnut, appear to give a slight edge to using structured tables over dynamic ranges, particularly with Excel 2016:

• https://msdn.microsoft.com/en-us/vb...performance-obstructions#allow-for-extra-data
• https://msdn.microsoft.com/en-us/vba/excel-vba/articles/excel-performance-and-limit-improvements
• https://msdn.microsoft.com/en-us/vba/excel-vba/articles/excel-improving-calcuation-performance
• https://msdn.microsoft.com/en-us/library/office/mt709003.aspx

The first three articles were updated in October, 2017, the fourth in October, 2016.

 

[flora1]

These four MSDN articles by Charles Williams, with David Chestnut, appear to give a slight edge to using structured tables over dynamic ranges, particularly with Excel 2016:

• https://msdn.microsoft.com/en-us/vb...performance-obstructions#allow-for-extra-data
• https://msdn.microsoft.com/en-us/vba/excel-vba/articles/excel-performance-and-limit-improvements
• https://msdn.microsoft.com/en-us/vba/excel-vba/articles/excel-improving-calcuation-performance
• https://msdn.microsoft.com/en-us/library/office/mt709003.aspx

The first three articles were updated in October, 2017, the fourth in October, 2016.

thank you very much

 

[thisoldman]

You're welcome. I'm happy to have helped.

 

[arthurbr]

Thanks for the links. Interesting stuff

 

[Aladin Akyurek]

I'd go for dynamic named ranges. These definitions should not use OFFSET or INDIRECT and COUNTA at all.

 

[thisoldman]

I'd go for dynamic named ranges. These definitions should not use OFFSET or INDIRECT and COUNTA at all.

Do you mean "Do not use OFFSET, do not use INDIRECT, do not use COUNTA" or do you mean "do not use OFFSET with COUNTA, and do not use INDIRECT with COUNTA?"

The formula I most often use for a dynamic range in a column is INDEX with COUNTA, for example,

=$A$1:INDEX($A:$A,COUNTA($A:$A))

 

[Aladin Akyurek]

Do you mean "Do not use OFFSET, do not use INDIRECT, do not use COUNTA" or do you mean "do not use OFFSET with COUNTA, and do not use INDIRECT with COUNTA?"

The formula I most often use for a dynamic range in a column is INDEX with COUNTA, for example,

=$A$1:INDEX($A:$A,COUNTA($A:$A))

No COUNTA, please. All those years I have been forwarding dynamic named ranges which do not invoke COUNTA at all. Mind you, COUNTA looks at every cell one by one.