Dynamic Range Wizard Step 2

In this step you select the anchor cell, the expansion method, and any additional fixed rows or columns you want to include in the Dynamic Range.

This is the form you will see if you chose to expand by rows only in Step 1. The words will change appropriately if you select a different option in Step 1.

Step 2A: Choose Dynamic Expansion Method

The Dynamic Range Wizard creates formulas stored in Excel Defined Names that return automatically resizing ranges. These formulas can use either of the Excel INDEX or OFFSET functions.

INDEX is not a volatile function but consumes more memory and slows down opening the workbook.

OFFSET is a volatile function, and so will be recalculated at every calculation, which slows down the recalculation, but uses less memory than INDEX.

Step2B: Select the Anchor Cell

When expanding by rows you identify the anchor cell and fixed columns by clicking on the anchor cell and selecting any additional columns by dragging the mouse across the cells in the same row as the anchor cell.

When expanding by columns you identify the anchor cell and fixed rows by clicking on the anchor cell and selecting any additional rows by dragging the mouse down the cells in the same column as the anchor cell.

When expanding by both rows and columns you must only select a single cell for the anchor cell.

If you click on the grey button showing … on the right of the input box the form will disappear and you will see:

In this example I have chosen to build a dynamic range that:

Starts in row 16.

Expands dynamically downwards by rows.

Includes three columns: B, C and D.

When you have selected the Anchor Range press Next to go to Step 3, or press Back to return to Step 1.

Copyright © 2020 Decision Models Ltd