Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

Excel OFFSET Function

The Excel OFFSET function returns a reference to a cell or a range of cells that is offset from a specific cell by a given number of rows and columns.


Syntax

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


Arguments

Reference (required): A cell or a range of adjacent cells you will set as the starting point.
Rows (required): The number of rows to move up (negative number) or down (positive number) from the starting point.
Cols (required): The number of columns to move left (negative number) or right (positive number) from the starting point.
Height (optional): The number of rows you want to return. The height must be a positive number.
Width (optional): The number of columns you want to return. The width must be a positive number.


Return value

The OFFSET function will return a cell reference offset from a given starting point.


Function notes

1. #VALUE! error value will return when the reference refer to a range of discontinuous cells.
2. #REF! error value will return when the rows and columns offset reference over the edge of the worksheet.


Examples

Example 1: Basic usage for the OFFSET function

Return a reference to a cell with the below formula:
=OFFSET(B2,3,1)

In this case, B2 is the starting point, number 3 and 1 mean that to move 3 rows down and 1 column right from cell B2, and return the value in C5 finally. See screenshot:

Return a reference to a range of cells with the below formula:
=OFFSET(B2,3,1,2,2)

In this case, you will get the results of 2 x 2 range that is 3 rows below and 1 column right of cell B2.

Note: #VALUE! Error will occur when you only selecting one cell to apply the OFFSET function for returning a range of cells. You need to select a 2 x 2 range (says 4 blank cells), enter the formula and press the Ctrl + Shift + Enter keys to get the results.

Example 2: Use the OFFSET function to sum a range of values

As we mentioned above, if you try to use the OFFSET function such as =OFFSET(B2,3,1,2,2) on its own in a single cell, it will return a #VALUE! Error. However, if you combine the SUM and OFFSET function as below screenshot shown, it will return the sum of values in range C5:D6 directly.

1. Select a blank cell, copy the below formula into it and press the Enter key to get the result.
=SUM(OFFSET(B2,3,1,2,2)))

Example 3: Sum columns based on certain criteria

As below screenshot shown, how to get the total sales of Mango from Sun to Tue? Please try as below.

1. Select a blank cell, copy the below formula into it and press the Enter key to get the result.
=SUM(OFFSET(C2:E2,MATCH(G4,B3:B8,),))

Notes:

1. In the above formula, MATCH(G4,B3:B8) is looking for Mango and returning its position in range B3:B8. Let’s see, Mango locates 5 rows below cell B2, so it returns the number 5;
2. As the MATCH result is 5, and the last comma here represents 0, the OFFSET function now is displaying as OFFSET(C2:E2,5,0), which means that the offset starts from range C2:E2, move 5 rows down and 0 column right to get the reference to range C7:E7;
3. Then the SUM function displays as SUM(C7:E7), and finally return the sum of values in range C7:E7.


More Examples

How to sum every n rows down in Excel?

How to create dynamic named range in Excel?

How to return cell value every five or nth row in Excel?


Excel Productivity Tools

300 Advanced Features Help You To Stand Out From Crowd

Would you like to complete your daily work quickly and perfectly? Kutools for Excel brings 300 powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) and save 70% time.

  • Designed for 1500 work scenarios, helps you solve 80% Excel problems.
  • Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands.
  • Become an Excel expert in 3 minutes. No longer need to remember any painful formulas and VBA codes.
  • 60-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years.

Brings Efficient Tabs to Office (include Excel), Just Like Chrome, Firefox, And New IE

  • Office Tab Increases your productivity by 50% when viewing and editing multiple documents.
  • Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
  • One second to switch between dozens of open documents!
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Be the first to comment.