Using dynamic ranges in Excel
- 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
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: |
![]() |
- 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 |
![]() |
- 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.
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.