Excel OFFSET Function (Shift a Reference) – Examples & Practice
Practice the Excel OFFSET function online with an interactive grid, instant feedback, and clear formula help.
Instruction
From reference B2, move 2 rows down and 0 columns to return the target cell reference.
Formula Syntax
=OFFSET(reference, rows, cols, [height], [width])
- reference: Starting anchor cell.
- rows: Rows to move down (negative moves up).
- cols: Columns to move right (negative moves left).
- [height]: Optional height of the returned range.
- [width]: Optional width of the returned range.
What it does
OFFSET returns a reference shifted by a number of rows and columns from a starting cell, optionally sized as a rectangle. It is commonly used for rolling windows and dynamic named ranges.
Excel OFFSET Function Examples
Move down two rows
=OFFSET(B2, 2, 0)
Returns the cell two rows below B2 in the same column.
Sum a sliding window
=SUM(OFFSET(B2, 0, 0, 3, 1))
Sums three cells down starting at B2 (height 3, width 1).
Last row pattern (careful)
=OFFSET(A1, COUNTA(A:A)-1, 0) can target the last filled cell in a column—validate blanks and headers.
dynamic-array-data.xlsx
| A | B | C | |
|---|---|---|---|
| 1 | Category | Amount | Region |
| 2 | A | 120 | North |
| 3 | B | 340 | South |
| 4 | A | 560 | North |
| 5 | C | 225 | West |
| 6 | B | 90 | South |
| 7 | Output |
Input Formula
Need Help?
Tips
- Prefer **SEQUENCE**/**FILTER** on modern Excel when they simplify intent.
- Keep OFFSET ranges bounded—avoid whole-column anchors inside OFFSET in big files.
- Comment why each argument is computed.
OFFSET Function Use Cases
- Rolling N-day averages
- Dynamic print areas in legacy models
- Charts that should follow the last data row
- Teaching coordinate-based references
- Interim solution before SEQUENCE/FILTER
Common mistakes - OFFSET function not working
- Off-by-one row or column arguments
- Forgetting height/width defaults to 1
- Volatile chains slowing large files
- COUNT-based “last row” with blank cells in the column
- Hard-to-audit magic numbers in OFFSET arguments
FAQ
Is OFFSET volatile?
Yes. It recalculates with the workbook and can impact performance when overused on large ranges.
Can OFFSET return a range?
Yes. HEIGHT and WIDTH arguments return rectangular ranges for SUM or other aggregations.
OFFSET vs INDEX?
INDEX can be non-volatile in some patterns; OFFSET always shifts from a starting anchor.
Why #REF!?
The shifted range leaves the sheet bounds or arguments are inconsistent.
Can OFFSET replace tables?
Sometimes, but Excel Tables and structured references are often clearer for growing data.
Comparison
| Function | Mechanism |
|---|---|
| OFFSET | Shift from anchor |
| INDEX | Pick by index |
| INDIRECT | Parse text |
Example
=OFFSET(B2, 2, 0)=SUM(OFFSET(B2, 0, 0, 5, 1))
Advanced examples
OFFSET + MATCH for dynamic start
Combine MATCH to compute the starting row, then OFFSET to size the range—classic pre-dynamic-array pattern.
Named OFFSET for charts
Define a named formula using OFFSET so charts track the last filled row (document assumptions clearly).