Notes Old site

Dynamic Ranges

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.

sales data in column B

You can define a name SalesData to refer to the following formula:
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.

sales data in column B

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.

Notes Old site

Using Names in charts

There are some ways of using Excel names in charts that can help make your charts easier to set up and maintain. You can use names to define the data series; in conjunction with dynamic ranges, you can then build dynamic charts without the use of macros.

Using names in charts

You can use a name in most of the places that you can use a range, when setting up a chart. However, you must always prefix the name with the worksheet or workbook name and an exclamation mark.

In particular, you can use names when specifying data series.

Chart data series

If you are using a name with workbook visibility (ie not local to a worksheet), you can prefix the name with either the workbook name or the sheet name when you type it in. Excel will automatically change the prefix to the workbook name. If you are using a name that is local to a worksheet, you must prefix the name with the worksheet name.

The useful thing about using a name to specify a data series is that if you change the range the name refers to, the chart will automatically show the new data. You don’t have to edit the chart in any way. As editing charts can be fiddly, this can be very helpful.

You cannot use names when specifying a data range. If you type a name in, Excel will automatically change it to the absolute address.

Chart data range