The OFFSET function in Excel allows you to return a value or range that is a specific number of rows and columns away from a starting point. Think of it as telling Excel to “move over” from one cell and return whatever is there.
OFFSET is especially useful when working with changing datasets, pulling values from nearby cells, or building formulas that adjust automatically as data changes. Even though it may sound advanced, the basic use of OFFSET is beginner-friendly once you understand how it works.
In This Guide, You’ll Learn
- What the OFFSET function does
- The syntax and arguments of OFFSET
- How to use OFFSET with beginner-friendly examples
- Common mistakes to avoid
- Real-world business uses for OFFSET
- When OFFSET is useful in everyday spreadsheets
What is the OFFSET Function?
The OFFSET function returns a reference to a cell or range that is a certain number of rows and columns away from a starting reference.
OFFSET Function Syntax
=OFFSET(reference, rows, cols, [height], [width])Argument Definitions
- reference – The starting cell or range.
- rows – How many rows to move. Positive numbers move down, negative numbers move up.
- cols – How many columns to move. Positive numbers move right, negative numbers move left.
- height (optional) – Number of rows to return in the result.
- width (optional) – Number of columns to return in the result.
If you only want a single value, you can ignore height and width.
Example Dataset Used in This Tutorial
You can download the sample data we’ll be using in this tutorial by clicking on this link—> OFFSET Function Data
Basic OFFSET Formula
In this example, we’ll start at cell A2 and move down 2 rows.
Formulas Used
=OFFSET(A2,2,0)=OFFSET(A3,2,0)=OFFSET(A4,2,0)
How This Formula Works
Excel starts at A2, moves:
- 2 rows down
- 0 columns over
It returns the value in the new location.
Move Across Columns
You can also move horizontally.
Formulas Used
=OFFSET(B2,0,2)=OFFSET(B3,0,2)=OFFSET(B4,0,2)
How This Formula Works
Excel starts at B2, moves:
- 0 rows down
- 2 columns over
Move Rows and Columns Together
OFFSET can move both vertically and horizontally at the same time.
Formulas Used
=OFFSET(B2,1,2)=OFFSET(B3,1,2)=OFFSET(B4,1,2)
How This Formula Works
Excel starts at B2, moves:
- 1 rows down
- 2 columns over
This helps navigate structured datasets.
Using OFFSET with SUM
OFFSET can also work inside other formulas.
Formula Used
=SUM(OFFSET(B2,0,0,3,1))
How This Formula Works
Excel starts at B2, moves:
- 0 rows down
- 0 columns over
- Return a range 3 rows tall
- Return 1 column wide
Excel sums:
- January
- February
- March
Common OFFSET Mistakes
Moving Outside the Worksheet
If OFFSET references cells beyond the worksheet boundaries, Excel returns an error.
Using Incorrect Row or Column Numbers
Positive values move:
- Down
- Right
Negative values move:
- Up
- Left
Forgetting Height and Width
If returning a range, height and width matter.
Without them, OFFSET only returns one cell.
OFFSET Can Slow Large Workbooks
OFFSET is a volatile function, meaning Excel recalculates it frequently. In very large spreadsheets, too many OFFSET formulas may slow performance.
Example Dataset Used in This Tutorial
You can download the sample data we’ll be using in this tutorial by clicking on this link—> OFFSET Function Data
