Skip to main content

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?


The Best Office Productivity Tools

Kutools for Excel - Helps You To Stand Out From Crowd

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions
Popular Features: Find, Highlight or Identify Duplicates  |  Delete Blank Rows  |  Combine Columns or Cells without Losing Data  |  Round without Formula ...
Super VLookup: Multiple Criteria  |  Multiple Value  |  Across Multi-Sheets  |  Fuzzy Lookup...
Adv. Drop-down List: Easy Drop Down List  |  Dependent Drop Down List  |  Multi-select Drop Down List...
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  Compare Columns to Select Same & Different Cells ...
Featured Features: Grid Focus  |  Design View  |  Big Formula Bar  |  Workbook & Sheet Manager | Resource Library (Auto Text)  |  Date Picker  |  Combine Worksheets  |  Encrypt/Decrypt Cells  |  Send Emails by List  |  Super Filter  |  Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters ...)  |  50+ Chart Types (Gantt Chart ...)  |  40+ Practical Formulas (Calculate age based on birthday ...)  |  19 Insertion Tools (Insert QR Code, Insert Picture from Path ...)  |  12 Conversion Tools (Numbers to Words, Currency Conversion ...)  |  7 Merge & Split Tools (Advanced Combine Rows, Split Excel Cells ...)  |  ... and more

Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need is Just A Click Away...

Description


Office Tab - Enable Tabbed Reading and Editing in Microsoft Office (include Excel)

  • One second to switch between dozens of open documents!
  • Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
  • Increases your productivity by 50% when viewing and editing multiple documents.
  • Brings Efficient Tabs to Office (include Excel), Just Like Chrome, Edge and Firefox.
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations