August 6, 2022
I've been creating new workbooks after being out of excel for many years. These new workbooks will grow so I'm curious about my dynamic named ranges. I've always used offset, but have learned index may be a better solution due to volatility. Can someone explain why one function is better than the other in this situation?
July 16, 2010
Welcome to our forum!
Evaluation for volatile functions is triggered more frequently than non-volatile functions. As a result, your workbook can become unnecessarily bogged down in calcs, making it slow.
See this post for a more thorough explanation.
INDEX is not a volatile function, so it's deemed preferable for dynamic ranges over OFFSET. However, if you only have a few OFFSET functions, you won't notice any impact on calculation times.
November 1, 2018