Excel Tip: An introduction to dynamic ranges

A few months ago I wrote a post  introducing the use of ranges in Excel:

Excel Tip: A short introduction to named ranges

In that post I alluded to dynamic ranges but didn't really say much about them, so I thought I would expand a little on the subject now.

As the name suggests, dynamic ranges are ranges that are not fixed. The range referred to varies based upon either the cell in which the reference is used or a variable (or variables) referred to in a formula, or both.

If we go to name a new range using the Name Manager on the Formulas ribbon (if you need a refresher on this, please visit my earlier post), and in the "Refers to" box, we highlight a range, then by default this range is absolute (Excel automatically inserts the dollar signs to fix the row and column references).

So, if we type "Test" in the Name field and delete the contents of the "Refers to" box, then click in cell A1 of Sheet1 (while still in the "Refers to" box), Excel populates the "Refers to" box with:

=Sheet1!$A$1

This means that if we refer to the range "Test" anywhere else in the workbook, it will look at the contents of cell A1 of Sheet1. This is a normal (fixed) range.

Now let's create a dynamic range.

We will follow the same process to create second range, but this time, ensure that cell B1 is selected before we start. Again create a new range in the Name Manager but call it Test2 this time. Delete the contents of the "Refers to" box again, then, again, click in cell A1. This will populate the "Refers to" box, as before, with:

=Sheet1!$A$1

This time though, before clicking OK, use the F4 key to remove the dollar signs.

The "Refers to" box should now read:

=Sheet1!A1

This is now a relative reference, relative to the cell we had selected prior to defining the range (B1). The range "Test2", will now refer to the contents of the cell immediately to the left of the cell that you use it in (as A1 is the cell immediately to the left of B1).

Try typing the formula =Test in multiple cells. This will always return the contents of cell A1. But type =Test2 in any cell, and it will return the contents of the cell to its left. "Test2" is therefore a dynamic range - the cell(s) it refers to change depending on where it is used.

Note that this is not just the case with a single cell, we can refer to a number of cells.

Let's take an example. Let's say that we have a 12 month budget, with the 12 monthly values in columns B to M. Click in cell N1 and name a new range in the Name Manager called "FullYear" and type the following into the "Refers to" box:

=Sheet1!$B1:$M1

We can either type this in, or as before, highlight the cells B1 to M1, but then use the F4 key on each of the references to remove the dollar from the row reference.

Note that I have left the dollars on the column references. This means that the row will change, but the columns will stay fixed. This means that we can use the formula =SUM(FullYear) and it will always sum columns B to M of the current row.

Sticking with this same example, we can use the OFFSET function to extend this further and allow us to have dynamic ranges for the current month's balance, and the year to date balance.

If we store the number of the current month (1 to 12) in cell P1, then we can name a range called "MonthBalance" by clicing in cell N1, and creating the range as before, but typing the following formula in the "Refers to" box:

=OFFSET($B1,0,$P$1-1)

Note the use of the dollars, The column of B1 is fixed but the row is left relative, whereas P1 is entirely fixed.

This will return the contents of the cell P1-1 cells to the right of column B, on the same row. So if the month number entered in P1 is 3, P1-1 =2. As column B is the month 1 balance, 2 columns to the right in column D is the month 3 balance!

So we can enter =MonthBalance in any cell and it will return the current month's balance from that row.

We can define the range "YTD" as:

=OFFSET($B1,0,0,1,$P$1)

This will return a range P1 wide and 1 row high starting in column B of the current row, i.e. all of the months to date. As this will usually be more than one cell, you will need to use SUM to total the range.

We can now use =SUM(FullYear), =MonthBalance and =SUM(YTD), to give us these totals on any row of the sheet. In addition, MonthBalance and SUM(YTD) will change depending on the month number entered in P1.





If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".