## 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 Toolsets:  12 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...

#### 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.