- Download the sample file (4746 bytes)

Dynamic ranges are incredibly useful where the amount of data in your worksheets keeps changing, and you need ways to analyse all or part of that data in charts or pivot tables. Here are a few possible applications for dynamic ranges:

- Plotting series of financial data that are updated daily, weekly or monthly
- Plotting a moving window, for example, just the last 12 months of data in a growing series
- Building pivot tables based on a growing data set

To create a dynamic range you need to use the OFFSET function, which lets you define a cell or cells relative to a starting point. This lets us define the start point, height (rows) and width (columns) of the range. OFFSET has the following syntax:

=OFFSET(Top of range, Row offset, Column offset, Height in rows, Width in columns)

The last two parameters are optional, but we will use them here.
In the sample file, go to the **DataSeries** sheet. In
columns A:C are about 40 rows of equity trading data. We need to
plot Equity (Column C) against Date (Column A), and allow the data
series and the linked chart to adjust as more data is added.

In Excel 2007 go to the Formulas tab and select Define Name; in earlier versions go to Insert > Name > Define.

The dialog looks like this: |

- In the Name row of the dialog enter Dates
- Leave the scope as Workbook -- we want to be able to use this range anywhere in the workbook, not just this sheet.
- In the Refers To row put this formula:

=OFFSET(DataSeries!$A$2,0,0,COUNTA(DataSeries!$A:$A)-1,1)

- Click OK

The dialog looks like this: |

- In the Name row of the dialog enter Dates
- In the Refers To row put the same formula as above. Click Add, then OK to dismiss the dialog

The second range is based on column C, and starts in C2. The name of the range is Equity, and the reference formula is

=OFFSET(DataSeries!$C$2,0,0,COUNTA(DataSeries!$C:$C)-1,1)

The OFFSET formula above defines the following features of the dynamic range called Dates:

Parameter | Formula part | Description |
---|---|---|

Top of range | DataSeries!$A$2,0,0 | The top of the range is the first data point, in this case A2. The two zeros indicate that we don't want to change the top of the range (ie, the row and column offsets from this start point are 0). |

Height of range | COUNTA(DataSeries!$A:$A)-1 | COUNTA counts all items in a range of cells, text or not. Here, we count all items in column A, less 1 for the heading row |

Width of range | 1 | 1 column wide |

For normal ranges, to jump to that range you can select it in the Names box. For any names based on formulas, you can't do this because the Names box will not display them. Instead you need to do the following:

- Formulas > Name Manager (Excel 2007) or Insert > Name > Define (Earlier versions)
- Click anywhere in the Reference line and the range selector will highlight the range for you
- Now, copy some data from columns F and G directly below the range. Fill the formula in column C down, and check the range again. It will have expanded to accommodate the new data.

- Using the data from the open workbook, select A1:A9 and, holding down the Ctrl key, select C1:C9.
- Create a line chart using the default settings. At this stage it's pretty boring...

Click the thumbnail to see a full-sized image |

- Select the data series and you will see the series formula displayed:

=SERIES(DataSeries!$C$1,DataSeries!$A$2:$A$9,DataSeries!$C$2:$C$9,1)

The syntax for this formula is =SERIES(Series Label, X values, Y values, Series Number). To change this into a dynamic series we need to use the range names in the series formula. Leaving the sheet references untouched, but replacing the ranges in columns A and C with the two range names, results in this series formula:

=SERIES(DataSeries!$C$1,DataSeries!Dates,DataSeries!Equity,1)

- Press Enter or Tab to stop editing the formula, and two things will happen:
- The series formula will update to a different syntax, using the workbook name and not the worksheet name. Although you could do this yourself, it's simpler and less error-prone to just do the range name substitution and let Excel automatically update the formula for you.
- The chart will update to take in the new data range

The new syntax is

=SERIES(DataSeries!$C$1,'Dynamic range 1.xls'!Dates,'Dynamic range 1.xls'!Equity,1)

The updated chart now looks like this

Click the thumbnail to see a full-sized image |

Try adding and removing data from the range, and see how the chart adapts.

You can take this a step further, by letting your users select both the start point and the amount of data to plot.

- Download this sample file (13,200 bytes) to see how it's done

If you are creating a lot of dynamic ranges, manually editing the formulas becomes tedious. You can save yourself a lot of time by using VBA to create dynamic ranges.