A1:A5000 Vs A:A

[Russk68]

I have always put a limit on column ranges in formulas thinking that it will be quicker to calculate. I have also ran into problems when I would expand data on a sheet where other sheets become inaccurate because I did not update the formula. So, does a formula like Index Match with A1:A5000 calculate faster vs A:A?

 

[Fluff]

By & large, yes it will take longer, especially if you are using array formulae.

 

[RoryA]

Most functions (as opposed to operators like * or +) will only calculate based on the used range of the references, so I doubt you will see much difference generally. You could also use tables or dynamic named ranges.

 

[Russk68]

Most functions (as opposed to operators like * or +) will only calculate based on the used range of the references, so I doubt you will see much difference generally. You could also use tables or dynamic named ranges.

Thank you!

 

[Alex Blakenburg]

Most functions (as opposed to operators like * or +) will only calculate based on the used range of the references, so I doubt you will see much difference generally. You could also use tables or dynamic named ranges.

I have on several occasions tried to get definitive documentation on this but the only documentation I have seen refers to the SumIf & CountIf group of functions being aware of the Used Range. I have not been able to confirm that it applies to the newer SumIfs CountIfs etc.
We are generally under the impression that most lookup functions are not in fact aware of the used range (and I believe that @Fluff is also of that opinion).

I am not sure of a definitive test either but the below would indicate perhaps even CountIf is not limiting itself to the used range.

XLookup EntireColumn Test 20241220.xlsx
	A	B	C	D	E	F	G	H
1
2
3		Test Xlookup	=ROW(XLOOKUP(1,--ISBLANK(E:E),E:E,,,-1))		Dummy Values
4		Last to First	1048576		E4
5		First to Last	1		E5
6					E6
7		Test CountIf	=COUNTIF(E:E,"<>1")		E7
8			1048576		E8
9					E9
10					E10
11					E11
12
13
14							Last Cell (End of Used Range)
15
Test Sheet

Cell Formulas
Range	Formula
C3,C7	C3	=FORMULATEXT(C4)
C4	C4	=ROW(XLOOKUP(1,--ISBLANK(E:E),E:E,,,-1))
C5	C5	=ROW(XLOOKUP(1,--ISBLANK(E:E),E:E,,,1))
C8	C8	=COUNTIF(E:E,"<>1")

 

[RoryA]

I suspect that it behaves differently if you use a negated criterion like that, since it would actually be wrong otherwise.

It’s not something that bothers me enough to devote the time to so I leave that to nice people like Charles: Excel Full Column References and Used Range: Good Idea or Bad Idea?

 

[Alex Blakenburg]

Thanks @RoryA, I am aware of Charles work but that post dates back to Dec 2015 (based on the comments since he doesn't seem to date the article). If some of the comments around the performance of arrays hold true for the dynamic array formulas then we are definitely going to need the not yet released TrimRange function.
His testing does seem to indicate that Match is Used Range aware although the only documentation on a Microsoft web site only mentions the SumIf, CountIf group of functions.

Anyway lets leave it there. I will keep an eye out for clarifying documentation

 

[RoryA]

I know there were quite a few performance improvements made in 2016, as Charles mentions here, (including performance of full column refs) but I don't remember seeing a similar thing about later versions - not that that means a lot.

 

[Alex Blakenburg]

The latest I have seen is probably the below but it doesn't shed any light on which functions handle whole column referencing well and which don't (and hence which will benefit from using TrimRange and which won't)

Excel performance - Performance and limit improvements

Find out about performance improvements in Excel 2016 and Excel 2010.

learn.microsoft.com