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
.