Excel OFFSET Function
last modified October 5, 2026
The OFFSET function is a powerful lookup and reference function in
Excel. It returns a reference to a range that is offset from a starting cell or
range. This tutorial provides a comprehensive guide to using the
OFFSET function with detailed examples. You'll learn basic syntax,
practical applications, and advanced techniques to master this versatile Excel
function.
OFFSET Function Basics
The OFFSET function returns a reference to a range that is a
specified number of rows and columns from a starting reference. It can return
a single cell or a multi-cell range. The syntax has required and optional
arguments.
| Component | Description |
|---|---|
| Function Name | OFFSET |
| Syntax | =OFFSET(reference, rows, cols, [height], [width]) |
| Arguments | reference (required), rows (required), cols (required), height (optional), width (optional) |
| Return Value | Reference to a cell or range |
This table breaks down the essential components of the OFFSET
function. It shows the function name, complete syntax format, argument
requirements, and return value characteristics.
| Argument | Description |
|---|---|
| reference | Required. The cell or contiguous range that the offset is measured
from. It must be a cell or a range of adjacent cells; anything else
returns #VALUE!. |
| rows | Required. How many rows to move from reference. Positive
values move down, negative values move up, and 0 stays in the same row.
The result is the upper-left cell of the returned range. |
| cols | Required. How many columns to move from reference.
Positive values move right, negative values move left, and 0 stays in the
same column. The result is the upper-left cell of the returned range. |
| height | Optional. The number of rows in the returned range. It must be a
positive number. When it is omitted, the height of reference
is used. |
| width | Optional. The number of columns in the returned range. It must be a
positive number. When it is omitted, the width of reference
is used. |
Only the first three arguments are required. OFFSET does not move
any cells or change the selection; it only computes a new address. The address
is rebuilt from the arguments every time the formula recalculates, which is
what allows the result to follow the data instead of pointing at a fixed
location.
Basic OFFSET Example
This example demonstrates the simplest use of the OFFSET function to reference a cell offset from a starting point.
| A | B |
|---|---|
| Apple | |
| Banana | |
| Cherry | |
| =OFFSET(A1, 2, 0) |
The table shows a simple spreadsheet with fruit names in column A. The OFFSET formula in cell B4 references a cell 2 rows below A1 (which would be A3).
=OFFSET(A1, 2, 0)
This formula starts at cell A1, moves down 2 rows (to A3), and stays in the same column (0 column offset). The result will be "Cherry". This shows how OFFSET can dynamically reference different cells.
OFFSET Rows and Columns Direction
The rows and cols arguments describe a displacement,
not a destination address. The starting cell stays where it is and the result
is placed that many rows and columns away from it. Positive numbers move down
and to the right, negative numbers move up and to the left, and zero means no
movement in that direction.
| Formula | Movement | Result |
|---|---|---|
| =OFFSET(E4, 2, 0) | 2 rows down, same column | E6 |
| =OFFSET(E4, -2, 0) | 2 rows up, same column | E2 |
| =OFFSET(E4, 0, 3) | Same row, 3 columns right | H4 |
| =OFFSET(E4, 0, -3) | Same row, 3 columns left | B4 |
| =OFFSET(E4, 0, 0) | No movement at all | E4 |
The displacement is applied to the upper-left cell of reference.
When the reference is a range, the whole block shifts with it, so
=OFFSET(B2:D4, 1, 1) points to the three-by-three block
C3:E5. Displacements that would place the result above row 1 or to
the left of column A fall outside the worksheet, and OFFSET
returns #REF!, as in =OFFSET(A1, -1, 0) or
=OFFSET(A1, 0, -1).
OFFSET Height and Width
The optional height and width arguments set the size
of the returned range. Both are counted from the position found by
rows and cols: height grows downward and
width grows to the right. When either argument is omitted, it
defaults to the size of reference, so
=OFFSET(B2, 1, 0) returns a single cell while
=OFFSET(B2, 1, 0, 3, 1) returns a three-row range.
=OFFSET(A1,0,0,5,1) ' A1:A5, five rows tall =OFFSET(A1,0,0,3,2) ' A1:B3, three rows by two columns =OFFSET(A1,0,0) ' A1, size inherited from the reference
Microsoft documents both size arguments as positive numbers, and a
height or width of 0 returns #REF!. That
case is easy to reach when the size comes from a count: an empty column makes
COUNTA return 0 and the whole formula fails. Excel has also
accepted negative height and width values for a long
time, in which case the range simply grows in the opposite direction, up or to
the left. The behavior is undocumented and other spreadsheet applications
reject it, so positive values are the safe choice.
OFFSET Returns a Reference, Not Only a Value
OFFSET produces a reference. You can confirm this with
ISREF: the formula =ISREF(OFFSET(A1,0,0,3,1)) returns
TRUE. What the cell finally displays depends on how that
reference is used.
=OFFSET(A1,2,0) ' a single cell, shows that cell's value =SUM(OFFSET(A1,0,0,3,1)) ' a range, handed to a range function =ISREF(OFFSET(A1,0,0,3,1)) ' TRUE, a reference was returned
Standing alone, a single-cell OFFSET displays the value of the
cell it points to, which is why the basic example above returns "Cherry".
Wrapped in a function that expects a range, it hands the range over intact,
which is how SUM, AVERAGE, COUNT, and
chart series consume it. A multi-cell OFFSET left on its own
cannot fit into one cell: Excel 365 and Excel 2021 spill the values into the
neighbouring cells, while Excel 2019 and earlier return #VALUE!.
The same error appears when the range is used directly in arithmetic, so wrap
the reference in a function that reduces a range to a single number.
OFFSET with Dynamic Range
OFFSET can create dynamic ranges that adjust automatically when data changes. This example shows how to sum a variable number of values.
| A | B |
|---|---|
| 100 | |
| 200 | |
| 300 | |
| =SUM(OFFSET(A1,0,0,3,1)) |
The table demonstrates using OFFSET to create a range of 3 rows starting at A1. The SUM function then adds all values in this dynamic range.
=SUM(OFFSET(A1,0,0,3,1))
This formula creates a range starting at A1 (0 row and column offset), with a
height of 3 rows and width of 1 column. SUM then adds these values (100+200+300)
for a result of 600. Because the height is a number rather than a fixed address,
the same pattern can follow the data:
=SUM(OFFSET(A1,0,0,COUNTA(A1:A100),1)) totals only the populated
cells of the list and grows as soon as a value is added.
OFFSET for Moving Averages
OFFSET is commonly used in financial analysis to calculate moving averages. This example shows a 3-month moving average calculation with the column headers in row 1 and the monthly sales figures in B2:B5.
| A | B | C |
|---|---|---|
| Month | Sales | 3-Month Avg |
| Jan | 1200 | |
| Feb | 1500 | |
| Mar | 1800 | =AVERAGE(OFFSET(B2,0,0,3,1)) |
| Apr | 1600 | =AVERAGE(OFFSET(B3,0,0,3,1)) |
The table shows monthly sales data with a column for 3-month moving averages. Each formula builds a three-row window with OFFSET and hands it to AVERAGE. The window ends at the current month and reaches two rows back, so it always covers the current month together with the two months before it.
=AVERAGE(OFFSET(B2,0,0,3,1))
In C4 the window is B2:B4, the months January through March, so the result is (1200+1500+1800)/3 = 1500. When the formula is copied down to C5, the relative reference moves to B3, the window becomes B3:B5, and the result is (1500+1800+1600)/3 = 1633.33. The range slides with the formula, which is exactly what a rolling average needs.
OFFSET with MATCH for Dynamic Lookup
Combining OFFSET with MATCH creates powerful dynamic lookup formulas. This example finds a value based on user input.
| A | B | C | D |
|---|---|---|---|
| Product | Price | Search: | Banana |
| Apple | 1.20 | Result: | =OFFSET(A1,MATCH(D1,A2:A4,0),1) |
| Banana | 0.80 | ||
| Cherry | 2.50 |
The table demonstrates a lookup system where the user can enter a product name in D1, and the formula returns the corresponding price from column B.
=OFFSET(A1,MATCH(D1,A2:A4,0),1)
This formula matches "Banana" in D1 with the products in A2:A4, returning
position 2. OFFSET then starts at A1, moves down 2 rows, and right 1 column to
return 0.80. This creates a flexible lookup system. If D1 holds a name that is
not in A2:A4, MATCH returns #N/A and the formula passes the error
through, so wrap the result in IFERROR when the search text comes
from the user.
OFFSET for Dynamic Chart Ranges
OFFSET can define dynamic chart ranges that automatically adjust as data grows. This example shows how to create a named range for charts.
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)
This formula creates a range starting at A1, with height equal to the count of non-empty cells in column A. When used as a named range in a chart, the chart automatically updates as new data is added to column A.
COUNTA counts every populated cell, header included. If A1 holds a
header and the data begins in A2, anchor the range at A2 and take the header
out of the count with
=OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A)-1,1). The count must
also equal the real height of the list, so keep the data free of blank cells
and remove any stray values that sit below it.
OFFSET Volatility and Performance
OFFSET is a volatile function. Volatile functions are recalculated
every time any cell in the workbook changes, even when the change has nothing
to do with them. The address is not stored; it is rebuilt on every
recalculation, which is what keeps dynamic ranges current, but also what costs
time.
A few OFFSET formulas in a small workbook are never noticeable. The cost grows
with the number of formulas and the size of the sheet, so hundreds of OFFSET
formulas, or volatile functions used down an entire column, can make typing and
manual recalculation sluggish. The usual precautions are to limit the number of
volatile formulas, to keep each referenced range as small as possible, and to
reach for a non-volatile function such as INDEX whenever the base
reference never moves. INDIRECT is volatile as well, so swapping
OFFSET for INDIRECT does not improve performance.
Alternatives to OFFSET
While powerful, OFFSET has some limitations. It is volatile, and its computed references are harder to read and debug than a plain address. Modern Excel offers several alternatives.
| Function | Advantage |
|---|---|
| OFFSET | Flexible, returns ranges of any computed size |
| INDEX | Non-volatile, often more efficient |
| INDIRECT | Builds references from text, but is also volatile |
| Dynamic Arrays | Newer, spill functionality |
| Excel Tables | Structured references grow with the data |
This table compares OFFSET with alternative functions. The reference form of
INDEX can return a range in the same way without recalculating on
every change, and an Excel Table expands by itself whenever a row is added,
which removes the need for a moving reference altogether. While OFFSET remains
the natural choice for ranges whose size is calculated, understanding these
alternatives helps pick the best tool for each situation.
OFFSET Errors and Troubleshooting
| Cause | Symptom | Fix |
|---|---|---|
| The offset runs past the edge of the worksheet | #REF! error | Reduce rows or cols, or start from a cell further from the edge; =OFFSET(A1,-1,0) tries to move above row 1 |
| height or width is 0, often a COUNTA over an empty list | #REF! error | Use a positive size, or omit the argument to inherit the size of reference |
| reference is not a cell or one contiguous range | #VALUE! error | Pass a single cell or one adjacent range; build multi-area references with INDEX or CHOOSE |
| A multi-cell OFFSET is left standing alone (Excel 2019 and earlier) | #VALUE! error | Wrap it in SUM, AVERAGE, or another function that accepts a reference |
| The offset points at an empty cell | 0 | Empty cells evaluate to 0; test with ISBLANK if you need to tell them apart |
| MATCH inside OFFSET does not find the lookup value | #N/A error | Check the lookup value and range, and use exact match with 0 as the third argument |
| height or width is negative | Accepted by Excel, rejected by other spreadsheet applications | Use positive sizes and anchor the range at its first cell |
Most OFFSET problems are boundary or size problems: the offset
must leave the result inside the worksheet, and the size must be a positive
number of rows and columns. When either value is calculated, guard it before it
reaches the function, for example
=SUM(OFFSET(A1,0,0,MAX(1,COUNTA(A1:A100)),1)), so an empty or
broken count can never turn a working total into #REF!.
The OFFSET function turns a fixed starting point into an address
that moves and grows with the data. Positive and negative row and column
offsets point down, up, right, and left, while the optional height and width
build ranges of any size, all anchored at the upper-left cell of the result.
Because OFFSET returns a reference rather than only a value, it
feeds SUM, AVERAGE, and chart series directly, and
combined with MATCH or COUNTA it produces lookups and
ranges that need no editing when rows are added. Two limits are worth
remembering: the result must stay on the worksheet with a positive size, and
every recalculation costs time because OFFSET is volatile. Used within those
limits, OFFSET remains one of the most direct ways to build
formulas that adapt instead of breaking.
Author
List all Excel Formulas.