Home > Formulas > Lookup > OFFSET Excel OFFSET Function Examples Use the Excel OFFSET function to refer to a range of cells, based on a starting cell. For example, to get the total sales for April, start in cell C1, and go down 4 rows. It's a bit like fishing, but you catch data, instead of fish!

1) OFFSET Function

The OFFSET function can return a reference to a range. Enter the starting point, the 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.

Tip: To follow along, download the OFFSET FunctionExamples workbook.

1.1) OFFSET Warning

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.

2) OFFSET Function Syntax

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

1. reference: a cell or range of adjacent cells.
2. rows: Rows to move from starting point; can be positive (below starting reference) or negative numbers (above starting reference).
3. cols: Columns to move from starting point; can be positive (right of starting reference) or negative (left of starting reference)
4. [height]: (optional) If entered, number must be positive, and is number of rows in returned reference
• If omitted, default is same height as reference
5. [width]: (optional) If entered, number must be positive, and is number of columns in returned reference.
   • If omitted, default is same width as reference

3) Sales for Selected Month

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
• for the cols argument, the sales amount is in column C, so the number of columns to offset is zero
• height is 1 row
• width is 1 column

Here is the formula in cell G2:

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

In the screen shot below, OFFSET returns 215, which is the April sales amount

3.1) Get the Month Name

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: For this example, 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.

4) Sum a Specific Month

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.

• for the reference argument, starting range 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

Here is the formula in cell C10:

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

 

5) Create Range Based on Count

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 months are added or removed from the list in column C, those changes will automatically appear in the data validation drop down list in cell F2.

The drop down list uses the dynamic named range, MonthsList, as its source.

6) Sum Specific Number of Months

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

Here is the formula in cell F3:

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

7) OFFSET - Like Fishing For Data

A few years ago, someone asked me to explain the Excel OFFSET function, saying "Please teach me to fish." That's when I realized that using OFFSET is like fishing.

• Tip: Read my full comparison of fishing vs OFFSET, on my Contextures Blog

Fishing: There are lots of different ways to fish. For example:

• dip into a pond with a bamboo pole and a small hook
• head out to sea, and cast a large net
• drop a line and hook through a small hole in the ice

OFFSET: With OFFSET, you can "fish" for data, with adjustable settings, to:

• pull data from a single cell nearby,
• or a large range of cells, off in the distance.

Fishing or OFFSET: In the photo below, from a long-ago camping trip, my dad is holding the catch of the day. So, would you rather catch fish, or use OFFSET, to catch data?

8) Get the Sample File

• OFFSET Examples: To see the OFFSET examples from this page, download the OFFSET FunctionExamples workbook. The zipped file is in xlsx format, and does not contain any macros.

Get Monthly Excel Tips!

Don't miss my monthly Excel newsletter! You'll get quick tips, article links, and a bit of fun. Add your email, and click Sign Up.

Next, when you get my reply, click the Confirm button. I add this step to protect you from spam!

More Excel Functions

SUM Function

Dependent Drop Downs

INDEX / MATCH Functions

COUNT Function

Split Winnings for Tied Rank