When constructing formulas or using charts in Microsoft Excel, it’s usual to have to define a range of cells to use in your formula. A range is a contiguous set of cells that are usually named using two cell references. Looking at the small database below, you can see that we have data in cells “A2:A10”

Dynamic_Range2

Where you know which cells contain data, it is easy to reference them in a chart or formula, but what happens when you don’t know exactly which cells the data will be in? For example, sometimes you may have a list of data in column A, in our example the data ends in cell A10 at the moment, but each month the sales figures for that month are added to the bottom of the list. If you have a formula that references a fixed range, such as “A2:A10” then when new data is added, this range wont pick up the information in the new cells. This is where a dynamic named range is invaluable and we create it using an ‘OFFSET’ formula. Here’s how to create a range that expands down as many rows as there are numeric entries:

Select Formulas tab > Define Names group > Name Manager

Dynamic_Range

This opens up a box where you can edit or create ranges within your workbook

  • Click New.
  • In ‘name‘ give your range a name (note: the name needs to be all one word and can’t contains spaces)
  • In ‘refers to‘ box, type the following text, and then click OK:
  • =OFFSET($A$1,0,0,COUNT($A:$A),1)
  • Click close

Dynamic_Range3

 

The key points you need to know about this data (and the parts you need to change to use it for your own data) are:

  • The first argument for the OFFSET function is set to the first cell in your range containing date (in this case cell A1)
  • The argument for COUNTA refers to the column containing the data and should be the entire column (in this case is A:A)
  • If your data contains text as well as numbers then COUNT will not return a correct result, and you should replace COUNT with COUNTA e.g. =OFFSET($A$2,0,0,COUNTA($A:$A),1)

LEAVE A REPLY


*