DataWright Information Services

Consulting and Resources for Excel and Access

Using dynamic ranges in Excel

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

Creating a dynamic range

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.

Open the Names dialog box

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

Define the name -- Excel 2007

The dialog looks like this: the Define Name dialog in Excel 2007
  • 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

Define the name -- earlier Excel versions

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

Define the second name

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)

Unpacking the OFFSET function

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

Viewing the range

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.

Practical Application: A chart with an expanding data range

  • 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 the original chart
  • 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 the expanded chart

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

Another option: letting the user control the size and start of the the chart range

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

Other ways to create dynamic range names

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.