DataWright Information Services

Consulting and Resources for Excel and Access

Using dynamic ranges in Excel

This page was updated on 4 March 2011, adding information on OFFSET/MATCH and INDEX/MATCH options for defining dynamic ranges.

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 a formula in the range definition, to calculate the height and width of the range.

There are a few ways to build a dynamic range with a formula, the best-known of which is 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

Note:

COUNTA will only expand for the number of items in the selected column. If you have blank cells, the range will be too short by the number of blank cells in the column. If you are building a range that includes blank cells, you can use the MATCH function instead. One use of this function is to define ranges for pivot tables, or for filters, on worksheets that contain blank rows. The syntax differs for numbers and text:

Using MATCH on a range containing numbers

=OFFSET(DataSeries!$C$2,0,0,MATCH(9.99E+307,DataSeries!$C:$C,1)-1,1)

Using MATCH on a range containing text

=OFFSET(DataSeries!$C$2,0,0,MATCH(REPT("Z",255),DataSeries!$C:$C,1)-1,1)

MATCH works by searching for the first item in a list that meets your criteria. By searching for a huge number or text string, you will go to the end of the list. The third parameter in MATCH indicates whether to perform an exact match (0), return the item before the exact match (1), or higher than the exact match (-1). Because our search term is intentionally too high to be found in the list, using 1 as the third parameter will return the position of the last item in the list. As for the COUNTA example, we then need to subtract 1 for the heading.

Another option is to use INDEX and MATCH together, to define a range. The syntax is a bit more complex, and out of long habit I tend to use the OFFSET function, but for completeness, here's the INDEX alternative.

The syntax for INDEX is 

INDEX(Range, Row, Column)

and it returns the value of the cell located in a particular row and column of a range.

INDEX and MATCH are most commonly used together when you want to look up a value to the left of your search column. They are generally used as an alternative to VLOOKUP; MATCH searches for the position adn INDEX returns the value. With VLOOKUP you must look to the right of the search column; INDEX and MATCH let you return a value from any column you like. Generally you will need an exact match, so here is an example of how to look up to the left:

=INDEX($G:$G,MATCH(F2,$D:$D,0),1)

When you are defining a range, however, we take advantage of another feature of INDEX; it can return an array of values. The next four formulas show some of the ways that you can define ranges with INDEX. 

=INDEX(DataSeries!$C:$C,2,1):INDEX(DataSeries!$C:$C,MATCH(9.99E+307,DataSeries!$C:$C,1))

You can hard-code the top of the range:

=DataSeries!$C$2:INDEX(DataSeries!$C:$C,MATCH(9.99E+307,DataSeries!$C:$C,1))

To define a multi-column range, do this:

=INDEX(DataSeries!$C:$C,2,1):INDEX(DataSeries!$D:$D,MATCH(9.99E+307,DataSeries!$D:$D,1),1)

To include the headers, use this instead:

=INDEX(DataSeries!$C:$C,1,1):INDEX(DataSeries!$D:$D,MATCH(9.99E+307,DataSeries!$D:$D,1),1)

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.