CODEWITHSUNDEEP
excelexcel-formula
Benjamin
Benjamin

Reputation: 561

Whole Column Formula Efficiency for MS Excel Built in Functions

I've been trying to find a list of the built in MS Excel functions that calculate "Whole Column" formula efficiently but haven't been successful, any ideas where I can go for this information? What i mean by this is exemplified below:

This documentation suggests that SUM and SUMIF formula automatically pick up on the last row of data, thus meaning that there is no efficiency reason why using a more restricted or dynamic range is preferable.

https://msdn.microsoft.com/en-us/library/office/ff726673(v=office.14).aspx#xlAllowExtraData

Answers for Excel 2003/7/10 are all welcome.

Upvotes: 1

Views: 244

Answers (1)

HarveyFrench
HarveyFrench

Reputation: 4568

I think it would be fair to assume that ALL excel functions behave as teh article describes (ie the same as SUM and SUMIF). (I wasn't aware of this article, but it makes sense when you think about it...)

Behind the scenes the data in cells is stored in OO data structures such that only cells with non-default values and formatting will have been created.

It's highly probable that the value data and formatting data are help in separate containers.

So when Excel is using a formula on a range it is working on the data structures and consequently only works with the cells that have values.

I hope that a whole column with cells having different formatting (but very values) does not cause the SUM and SUMIF formulas to scan through every cell.

If in doubt you could do an experiment with the formulas you want to use.

The link you gave talked about formulas that behave differently and explicitly named VBA user created functions and array formulas - which makes sense.

Also, note that the article says using "Structured Table References" is the best approach. (ie not only storing your data in ranges but storing your data in excel tables. Created from a range using Ribbon:INSERT>Table).

These tables will allow any function to be used more efficiently as the range used is limited to the number of rows the table has.

I hope this helps.

Harvey

Upvotes: 1

Related Questions