OFFSET Generator
Return a reference shifted a set number of rows and columns from a starting cell, optionally resized.
OFFSET
Return a reference shifted a set number of rows and columns from a starting cell, optionally resized.
The anchor cell or range the offset is measured from.
Positive moves down, negative moves up, 0 stays on the same row.
Positive moves right, negative moves left, 0 stays in the same column.
How many rows tall the returned reference should be. 1 returns a single row. Optional in Excel and Sheets - delete it and the width from the copied formula to inherit the anchor's own size.
How many columns wide the returned reference should be. 1 returns a single column.
=OFFSET($A$1, 2, 3, 1, 1)Worked Example
You want to read the cell three columns right and two rows down from the top-left of your data.
=OFFSET($A$1, 2, 3, 1, 1)Returns: Returns the value in D3: two rows down and three columns right of A1, sized to a single cell.
Checks Before You Paste
- •OFFSET is volatile: it recalculates on every change anywhere in the workbook. A rolling-window model built from hundreds of OFFSET calls is the usual reason a file takes seconds to respond to a keystroke.
- •A height or width above 1 makes OFFSET return a multi-cell range. That is fine inside SUM, AVERAGE, or COUNT - =SUM(OFFSET($A$1, 0, 0, 12, 1)) - and it spills in Microsoft 365 and Excel 2021. In older Excel, entered on its own it falls back to implicit intersection, returning the single value on the formula's own row or #VALUE! when the range does not line up with it.
- •The offset is measured from the top-left cell of the reference, and any move that lands outside the worksheet gives #REF! - so negative rows from row 1, or negative cols from column A, will break the formula.