Overview

InputNeed?Description
ReferenceRequiredThis is your starting point
RowsRequiredHow many rows you want to move away from your starting point. A positive number moves down and a negative number moves up
ColsRequiredHow many columns you want to move away from your starting point. A positive number moves to the right and a negative number moves to the left
HeightOptionalHow many rows (up or down) you want to expand your range (positive number expands downward and a negative number expands upward)
WidthOptionalHow many columns (right or left) you want to expand your range (positive number expands to the right and a negative number expands to the left)

Possible Errors That Can Occur

What Is Offset?

The OFFSET() function does just what its name says it does, it offsets!  It does this by getting a user-defined starting point and either moving or expanding from that starting point.  I find this most helpful in dealing with data that is organized by a timestamp (i.e. by month, by quarter, by year, etc…) where you may want to reference a new column every month.

Examples

We will make our starting point be Cell B2 (highlighted in yellow) in the below examples:

Example 1)   =OFFSET($B$2,4,0)

From our starting point (B2), we moved down 4 rows and 0 columns to return Cell B6 or “Widget D”.  Since we are just trying to target 1 cell value we do not want to use the optional Height and Width parameters as this will give us an error.

Example 2)   =OFFSET($B$2,0,2)

From our starting point (B2), we moved down 0 rows and to the right 2 columns to return Cell D2 or “Sales Price”.  Since we are just trying to target 1 cell value we do not want to use the optional Height and Width parameters as this will give us an error.

Example 3)  =OFFSET($B$2,4,3)

From our starting point (B2), we moved down 4 rows and to the right 3 columns to return Cell E6 or “$130”.  Since we are just trying to target 1 cell value we do not want to use the optional Height and Width parameters as this will give us an error.

 Example 4)  =SUM(OFFSET($E$3,0,0,5))

Now I have changed the starting point on you.  We are now starting on Cell E3.  This time I want to use the OFFSET function to be a part of a summation formula.  This means in order to adjust our range we are going to want to use those optional parameters.  We are not going to adjust the position of our starting point but we do want to add to it.  So we will set our Rows and Cols parameters to zero and adjust our Height parameter down to a total of 5 cells.  We can then wrap the SUM() function around our offset range to add up all the cells in our range.  Here are a few other ways we could highlight the same range in this example using the range function:

  • =SUM(OFFSET($E$7,0,0,-5))
  •  =SUM($E$3:OFFSET($E$3,4,0))
  •  =SUM($E$3:OFFSET($B$2,5,3))

All these formulas will return the value $405

Real World Examples

Hopefully now you are able to understand the functionality of the Offset function but you still might be wondering how to use it!  Well I have put together an Excel workbook that will show you a few different ways I have used Offset in my analytical work over the years. So go ahead, download the workbook and start thinking of ways that you can incorporate this function into your spreadsheets!