Use the OFFSET function to refer to a range, based on a starting cell. For example, get the total sales for April-June (start in column 4, include 3 columns).

The OFFSET function can return a reference to a range. Enter the starting point, number of rows and columns to move from starting point, and how big a range to include (number of rows and columns)

In this video, you'll see four examples of how to use the OFFSET function. The written instructions are below the video.

The OFFSET function is volatile, so it could slow down a workbook if used in too many cells.

Instead, you could use a non-volatile function, like INDEX, to return a reference.

The OFFSET function has five arguments: OFFSET(reference,rows,cols,height,width)

**reference**: a cell or range of adjacent cells.**rows**: Rows to move from starting point; can be positive (below starting reference) or negative (above starting reference).**cols**: Columns to move from starting point; can be positive (right of starting reference) or negative (left of starting reference)**height**: If entered, number must be positive, and is number of rows in returned reference**width**: If entered, number must be positive, and is number of columns in returned reference.

With the OFFSET function, you can return a reference to a range, based on a starting reference. In this example, we want the sales amount in cell G2:

- starting reference is cell
**C1** - number of rows to offset is entered in cell
**F2** - sales amount is in column C, so the number of columns to offset is zero
- height is 1 row
- width is 1 column

**=OFFSET(C1,F2,0,1,1)**

In cell H2, there is a similar OFFSET formula, to return the month name. The only difference is the column offset -- 1 instead of zero.

**=OFFSET(C1,F2,1,1,1)**

Note: The height and width arguments could be omitted, because we want a reference that is the same size as the starting reference. I used them in this example, to show how all the arguments work.

In this example, the OFFSET function returns a reference to the sales amounts for the selected month, and the SUM function returns the total for that range. In cell B10, the selected month number is 3, so the result is the total of the March sales.

- starting reference is A3:A6
- rows to offset is zero (you could omit the zero, for the same result)
- columns to offset is entered in cell B10
- height and width are omitted, because the reference should be the same size as the starting reference

**=SUM(OFFSET(A3:A6,0,B10))**

You can also use the OFFSET function to create a dynamic range. In this example, I've created a name, MonthsList, with this formula:

**=OFFSET('Ex03'!$C$1,0,0,COUNTA('Ex03'!$C:$C),1)**

If another month is added to the list in column C, it will automatically appear in the data validation drop down list in cell F2, which uses MonthsList as its source.

In this final example, OFFSET is combined with SUM and COUNT, to show the total for the last * n* months. As new quantities are added, the formula result will automatically adjust to include the latest months. In cell E2, the number of months is 2, so the August and September amounts are summed.

- starting reference is cell
**C2** - number of rows to offset is calculated by counting numbers in column C, subtracting number in cell
**E3**, adding 1 - quantity is in column C, so the number of columns to offset is zero
- height is entered in cell
**E3** - width is 1 column

**=SUM(OFFSET(C2,COUNT(C:C)-E3+1,0,E3,1))**

Download the sample OFFSET workbook

Don't miss my latest Excel tips and videos! Click OK, to get my weekly newsletter with Excel tips, and links to other Excel news and resources.

Last updated: October 31, 2018 10:51 AM

Contextures RSS Feed