How to define a range in Excel dynamically

From my previous blog – How to define a range in Excel, I have illustrated how to name a range. We can also define a range in Excel dynamically using the offset excel function.

To create a dynamic range, we will use the “offset” function” in Excel

offset excel function

The Offset excel function returns a reference to a range that is a specified number of rows and columns from a cell or range of cells. The reference that is returned can be a single cell or a range of cells. You can specify the number of rows and the number of columns to be returned.

Following is the Syntax

OFFSET(reference, rows, cols, [height], [width])

[vtftable ]
Parameter;;;Required/Optional;;;Description;nn;
Reference ;;;Required;;;The cell from which you want to base the offset.;nn;
Rows;;;Required;;; The number of rows, up or down, that you want the upper-left cell to refer to. ;nn;
Col;;;Required;;;The number of columns, to the left or right, that you want the upper-left cell of the result to refer to;nn;
Heights;;;Optionals;;;The height, in number of rows, that you want the returned reference to be. Height must be a positive number.;nn;
Width ;;;Optionals;;;The width, in number of columns, that you want the returned reference to be. Width must be a positive number.;nn;
[/vtftable]

How to define a range in Excel dynamically

Following example illustrated how to define a range in Excel dynamically using the Offset function in the name manager. However, must be reminded this function is NOT only design for Name manager, but also for other functions that requiring a range as an input parameter.

Suppose we have the following data

Sales table

Without using the offset function. We can still name a range (area) using the name manager.

Static range

However, when we added extra data to the spreadsheet, the range will not be automatically extended. This is because the range is fixed or static.

Add February data
Range didn’t include February data

This is the problem with static range.

Now let’s have a look at how to us offset function to define a range in Excel dynamically.

Suppose we have the same group of data,

Sales table

Following is the steps:

  1. Go back to the name manager and enter the following formula

    “=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),COUNTA(Sheet1!$1:$1))”

    Name manager using dynamic range (Offset function)
    Name manager using dynamic range (Offset function)

    [vtftable ]
    Parameter;;;Value;;;Meaning;nn;
    Reference;;;Sheet1!$A$1;;;Take “Sheet1!$A$1” as a reference;nn;
    Rows;;;0;;;Start from the same row as A1 (i.e. Row 1);nn;
    Col;;;0;;;Start from the same column as A1 (i.e. Column 1);nn;
    Heights;;;COUNTA(Sheet1!$A:$A);;;Specify the number of rows that the range include by counting all items in column A. The counta function is to count all non-blank cell in the input range. In this example, it is the entire column A (Sheet1!$A:$A);nn;
    Width;;;COUNTA(Sheet1!$1:$1);;;Specify the number of columns that the range include by counting all items in column A. The counta function is to count all non-blank cell in the input range. In this example, it is the entire row 1 (Sheet1!$1:$1);nn;
    [/vtftable]

Now we have successfully created a dynamic range. Let’s try to add some additional data to see if the dynamic range works successfully.

Appending extra rows

Add February data
Add February data
Range updated automatically

Add additional columns

Add Cost column
The range is automatically updated!

You can see that no matter adding columns or rows, the range will be updated automatically. This is because the boundary is updated automatically.

Remember the offset function is NOT simply design for name manager, it can be used by all function that requires a range as input.

Leave a Comment