Dynamic Ranges

Dynamic Ranges

See FastExcel V2 Dynamic Range Wizard for easy creation of dynamic ranges.

Use Dynamic Ranges to minimise the number of cells that Excel has to calculate.

When you often have to add extra rows or columns of data to your spreadsheets, you need to find a way of having your Excel formulae automatically refer to the new data area.

You can do this by using a large range in your formulae that extends well beyond your current data boundaries, for instance by referring to a whole column. However this causes very inefficient calculation.

The solution is to use Dynamic Named Ranges. By using the OFFSET and COUNTA functions in the definition of a named range, the area that the named range refers to can be made to dynamically expand and contract. For example create a defined name as:

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)

When you use the dynamic range name in a formula it automatically expands to include new entries.

The benefit of this (apart from the fact that you don’t have to change all your formulae) is that Excel then only has to calculate using exactly the right number of rows and columns. However there is a performance hit if you use a lot of Dynamic Ranges.
The main performance hit is because OFFSET is a volatile function and therefore is always recalculated, and because the COUNTA function inside the OFFSET has to look at a large number of rows. You can minimise this performance hit by storing the COUNTA part of the formula in a separate cell, and then referring to the cell in the dynamic range:

Counts!z1=COUNTA(Sheet1!$A:$A)
DynamicRange=OFFSET(Sheet1!$A$1,0,0,Counts!$Z$1,1)

You can also use functions like INDIRECT to construct dynamic ranges.

The FastExcel sample problem makes extensive use of dynamic range names, and also shows how to optimise them. It also contains two versions of the optimised workbook, one with dynamic names, and one without.

FastExcel Version 2 has a Dynamic Range Wizard that makes it easy to create dynamic range names, and a several new counting functions that overcome the limitations of the COUNTA function.

For further explanation of Dynamic Ranges see:

© 2001-2014 Decision Models  Legal Disclaimer Privacy Statement E-Mail Webmaster