Is there any reason not to use named ranges that run from row 1 to row 1048576?
Will this affect performance?
Or is it better to have a range that is only as big as my data?
We have some optimizations, that allow to speed up in very many places, where the range includes the whole column (or a whole row). In those cases, we could usually detect the used range, and your formula would be fast.
- It means, that you better use
A:A, thanA1:A1000000, becauseA1:A1000000is not the whole column - there are 48576 rows more; and for this case, we won’t be able to conclude that the user meant “just the data in that range”, not “I prepared the range carefully, do what I say”. Also,A2:A1048576is not a whole column, ether. - The optimizations are not everywhere. If some calculation with whole columns (rows) takes longer than with manually created range, then that should be considered a bug.
4 Likes
Thanks. Is this also true for formats?
Better to format 1:1 as a heading, not just A1:AZ1?
This applies also for formats. Especially for rows. If you format only part of a row, that would mean creating all the columns for the formatted part, even if you don’t use all of it; and with the current 16K column sheets, it may bite; but formatting a whole row would just set a flag “the unused rest of the row has this format”. E.g., here is a ~recent change, that optimized application of autoformat to ranges including whole rows (and keeps the pre-existing optimizations for whole columns).
Note, that if you format 1:1, but in its last cell, you make a change, it will hit you. The change of formatting means, that the whole range is used.
But again: there is no 100% coverage; bug reports are welcome.
3 Likes