Excel Tutorial Series – Range Operations

In the NETWORKDAYS example in one of my previous tutorial posts, 'HolidaysList' refers to a static range: A6:A15.

Many ranges are static, that is, they consist of a finite amount of data that isn't likely to change.

Ranges that are subject to change are called dynamic ranges. A popular example is data with a time element, such as sales figures, which are aggregated on worksheets or in database tables.

OFFSET Formula

An important feature of Excel is named ranges which expand or contract automatically based on the amount of items present. These are called dynamic named ranges. This allows your formulas to reference those names and adjust on their own, without the need to update your range references every time you add new data to the range.

A typical dynamic named range follows:

=OFFSET($A$1,0,0,COUNTA($A:$A),1)

Let's pick through the OFFSET formula and see what is so dynamic about it.

OFFSET(reference,rows,cols,height,width)
Returns a reference to a range that is a specified number of rows and columns from a cell or range of cells.

Arguments:

  • Reference is the reference from which you want to base the offset. Reference must refer to a cell or range of adjacent cells; otherwise, OFFSET returns the #VALUE! error value.
  • Rows is the number of rows, up or down, that you want the upper-left cell to refer to. Using 5 as the rows argument specifies that the upper-left cell in the reference is five rows below reference. Rows can be positive (which means below the starting reference) or negative (which means above the starting reference).
  • Cols is the number of columns, to the left or right, that you want the upper-left cell of the result to refer to. Using 5 as the cols argument specifies that the upper-left cell in the reference is five columns to the right of reference. Cols can be positive (which means to the right of the starting reference) or negative (which means to the left of the starting reference).
  • Height is the height, in number of rows, that you want the returned reference to be. Height must be a positive number.
  • Width is the width, in number of columns, that you want the returned reference to be. Width must be a positive number.

offset

The first thing to note is that OFFSET returns a range reference, which we need; in fact, any function which returns a range reference can be used in a named range.

The first argument is a range reference from which to base the offset; typically it is the upper left-most cell in a range. The second argument is the number of rows to move the range reference, while the third argument is the number of columns to move the range reference. The last two arguments are the number of rows tall and number of columns wide you want the resulting range to be.

When we define a named range on a worksheet using the above OFFSET formula, we are telling Excel that our range starts in cell A1 (because we started in A1 and moved 0 rows and 0 columns), is one column wide and is as many rows as there are cell entries (using COUNTA to count the number of non-blank cells). Important: There should not be any blank rows in the middle of your data in column A.

The reason the name is dynamic is because, as we add new data to column A, the COUNTA function returns the count of values in that column. If we use the name in a formula, it will automatically update any calculation in which it is used!

Now suppose our range contains a header, and we don't want to include it in our calculations. To account for the header row, we'll want the range to start one row down from cell A1, and we'll need to subtract one from the count of column A.

=OFFSET($A$1,1,0,COUNTA($A:$A)-1,1)

If your data also contains a dynamic number of columns, you might want the formula to take that into account as well. The last argument is the width of the resulting range, so just use COUNTA there as well:

=OFFSET($A$1,0,0,COUNTA($A:$A),COUNTA($1:$1))

One excellent use of this formula is in Pivot Tables. If you define a range named "Database" with the above formula, the Pivot Table will automatically update itself to include new data as it is appended to the table (when you click Refresh).

When creating the Pivot Table, simply refer to the named range as the data source:

pt

Recap:

Range (including header): =OFFSET($A$1,0,0,COUNTA($A:$A),1)
Range w/columns (incl header): =OFFSET($A$1,0,0,COUNTA($A:$A),COUNTA($1:$1))
Range (without header): =OFFSET($A$1,1,0,COUNTA($A:$A)-1,1)
Range w/columns (incl header): =OFFSET($A$1,1,0,COUNTA($A:$A)-1,COUNTA($1:$1))

In my opinion, the last one is the best, since it returns a flexible number of columns and rows, without the header row.

Incidentally, this is a great way to do lookups without forcing your data to be formatted a certain way (as VLOOKUP requires your lookup column to be in the leftmost column of a table). For example if I had a column of numbers in B1:B10 and wanted to return the value in the column to the left of the number 4, the formula would be

=OFFSET(B1,MATCH(4,B1:B10),-1,1,1)


Array formulas

In my last tutorial post, I went into detail about how array formulas calculate. So I thought I'd share a few more useful array formulas that might make someone's life easier. Remember these are array formulas, so use Ctrl+Shift+Enter when committing them to the cell.

Count blanks in a range: =SUM(IF(ISBLANK(range), 1))

Count nontext cells in a range: =SUM(IF(ISNONTEXT(range),1))

Count text (non-number) cells in a range: =SUM(IF(ISTEXT(range),1))

Count errors in a range: =SUM(IF(ISERROR(range), 1, 0))

Report errors in a range: =IF(SUM(IF(ISERROR(A2:E70), 1, 0))>0,"errors","all OK")

I also wanted to mention that array formulas are memory hogs (especially if you are referencing large ranges), so even a few of them will start slowing down your workbook considerably. Try to avoid them by using a Pivot Table or SUMPRODUCT where possible.

About JP

I'm just an average guy who writes VBA code for a living. This is my personal blog. Excel and Outlook are my thing, with a sprinkle of Access and Word here and there. Follow this space to learn more about VBA. Keep Reading »


Related Articles:


Share This Article:

Share and bookmark this articledelicious buttonfacebook buttonlinkedin buttonstumbleupon buttontwitter button

comment bubble 2 Comment(s) on Excel Tutorial Series – Range Operations:

  1. Cynthia Bruce writes:

    Hi JP,

    I've been trying to get the offset to work on my workbook; I'm using the =OFFSET($A$1,0,0,COUNTA($A:$A),COUNTA($1:$1)) formula in cell A1. My table range is A4 to AD59488, and I have 20 pivot tables behind it. My formula in cell A1 is =OFFSET($A$4,0,0,COUNTA($A:$A),COUNTA($4:$4)) and my result is the ever-hated #VALUE!

    Can you please tell me what I am doing wrong???

    Thanks so much,

    Cynthia

    • Cynthia,

      You would use the OFFSET formula in the range name dialog box: press Ctrl+F3 to bring up the dialog box. That's where you would specify the range and give it a name.

This article is closed to any future comments.
Excel School