Usually, when you specify a range in a formula, you specify it through its address: for example, `$B$5:$C$10`

. However, you can specify ranges through formulae, effectively making them dynamic.

There are a number of formulae you can use to specify dynamic ranges, including `OFFSET`

and `IF`

.

## Using the OFFSET formula to define a dynamic range

The formula that is most often used to define a dynamic range is `OFFSET`

. The result of an `OFFSET`

formula is a range. You have to specify the top left hand corner and the dimensions.

Specifically, there are five arguments:

**Reference**: A range that forms the basis of the new range. This can be a single cell, or a range of adjacent cells.**Rows**: The number of rows up (negative) or down (positive) from the top of the**reference**range that you want the new range to start at.**Cols**: The number of columns left (negative) or right (positive) from the left of the**reference**range that you want the new range to start at.**Height**: The height (number of rows) of the new range. This argument is optional. If you omit it, the new range will have the same number of rows as the**reference**range.**Width**: The width (number of colums) of the new range. This argument is optional. If you omit it, the new range will have the same number of columns as the**reference**range.

So, for example, the result of the formula `OFFSET($B$5:$C$10, 3, -1)`

would be the range `A8:B13`

: it starts 3 rows down and one column to the left of `B5`

, and has 6 rows and 2 columns, just like the range `$B$5:$C$10`

.

The real utility of this formula comes when you use formulae for some of the arguments, and define a name to refer to it.

For example, suppose you have monthly sales data in a spreadsheet; you want to plot a chart, but don’t want to edit the chart each time you update the sales data.

You can define a name `SalesData`

to refer to the following formula:

`=OFFSET(chartData!$B$5,0,0,COUNT(chartData!C:C)-COUNT(chartData!$B$1:$B$4),1)`

and then set the data series in the chart to refer to `SalesData`

.

You can then update the chart simply by adding a new line of data.

Note that we have to be a bit careful how we specify the number of rows in the dynamic range, so as not to count any values that are not sales data.

## Using IF to define a dynamic range

There is an even simpler way to define a dynamic range, which is especially useful for data validation. Suppose you have related sets of options: if you choose a service level of **premier**, for example, you can choose one of three delivery methods, but if the service level is **standard**, you only have a choice of two.

For the cell `B5`

we can set the data validation type to `list`

and enter the following formula as the range:

`=IF($B$4="Premier", $E$3:$E$5,$E$4:$E$5)`

The drop down box for cell `B5`

will then show the valid delivery options for the service level chosen in cell `B4`

.