OFFSET in Excel: directions, range dimensions and boundaries

OFFSET returns a reference to a cell or rectangular range positioned relative to a starting reference. I read it as a sequence of questions: where do I start, how many rows and columns do I move, and what size should the resulting reference have? No data is physically moved around the worksheet. The Polish function name is PRZESUNIĘCIE.

Positive row offsets go down and negative ones go up. Positive column offsets go right and negative ones go left. Microsoft’s OFFSET documentation specifies these directions. The examples below use a small, synthetic table.

Five arguments and a starting reference

=OFFSET(reference,rows,cols,[height],[width])

The initial reference identifies a cell or contiguous range. Rows and cols locate the result’s upper-left corner. Height and width specify its size; the documentation requires positive dimensions. Omitting a dimension inherits that dimension from the starting reference. A two-row reference does not automatically collapse into one cell just because the optional arguments are absent.

These formulas use English names and commas. Regional settings may require semicolons. OFFSET is available in Microsoft 365 and versions including Excel 2016, 2019, 2021 and 2024. Start by drawing the destination address on paper, then combine the reference with a calculation. Separating position from aggregation makes the formula substantially easier to inspect.

Use a table small enough to inspect manually

Enter the following data in A1:C5. The Polish headings preserve identical input across both language versions: Okres means Period, Plan means Plan and Wykonanie means Actual. The four records use one arbitrary unit and are entirely synthetic.

=OFFSET(B2,1,1)

Starting at B2, move one row down to B3 and one column right to C3. The reference is C3, whose value is 18. The starting cell’s value of 10 is irrelevant to that movement: B2 supplies a position, rather than an amount to add. This distinction helps when a formula seems to produce a number unrelated to its starting cell.

=OFFSET(C3,-1,-1) points back to B2 and its value of 10. Meanwhile, =OFFSET(C3,0,0) keeps C3 and gives 18. Zero means no displacement along that axis. Working through these three cases makes reversed signs visible before they become buried inside a more complicated model with several nested functions.

Pass a returned range to SUM

=SUM(OFFSET($C$2,2,0,2,1))

The start moves two rows from C2 to C4. A height of two and width of one create C4:C5. The values are 33 and 44, so the sum is 77. The outer function accepts the reference and performs the aggregation. For the same two values, the average is 38.5. Position and size determine the input; the outer function determines the calculation.

Returning a range and displaying it directly in the grid are separate issues. Array behaviour depends on the Excel version and formula location. I put multi-cell results inside SUM here to keep the intended calculation clear and avoid depending on free spill space. Microsoft explicitly describes using OFFSET as an argument for functions expecting references.

Omitted dimensions retain the original shape

=SUM(OFFSET(B2:C3,1,0))

The starting reference is two rows by two columns. Moving it down by one row retains that shape, producing B3:C4. Its sum is 20 + 18 + 30 + 33, or 101. This corrects the misleading assumption that omitted height and width always mean a single-cell result. The size of the original reference remains part of the calculation.

When reviewing a formula, write down the full starting range. Changing B2 to B2:C3 can change the result’s dimensions without altering the explicit movement arguments. Inspecting just the row and column offsets therefore misses part of the behaviour. The starting position and shape together establish what the formula will reference after the movement.

Last N periods with a known record count

Put an integer from 1 to 4 in F1. With four consecutive, complete records in C2:C5, a window over the final records can be written as:

=SUM(OFFSET($C$2,4-$F$1,0,$F$1,1))

For N equal to 2, the start moves down two rows and the height is two: C4:C5, giving 77 again. For N equal to 1, the result is C5 and 44. For N equal to 4, the range is C2:C5 and the sum is 107. The literal 4 explicitly assumes four records. It does not discover additional entries automatically.

In an actual input form, restrict N to valid integers and establish the record ordering. “Last” here means last by row position, rather than automatically latest by date. Different sorting or a missing period requires attention to the source and window definition first. OFFSET moves a reference; it has no independent understanding of calendar completeness or which records the report should include.

Do not blindly replace the record count with COUNTA over a whole column. A header or note is nonempty too, and counting occupied cells does not locate the final occupied row when gaps exist. Zeros, empty cells and formulas returning empty text also need defined treatment. For regularly appended data, an Excel table is often a clearer source structure.

Worksheet boundaries and recalculation

=OFFSET(A1,-1,0) attempts to reference a row above the worksheet and returns #REF!. A result extending beyond a sheet edge through its height or width has the same boundary problem. Referencing an existing empty cell is not, by itself, a movement outside the worksheet. Determine the destination before hiding an error with another function.

OFFSET is volatile: it participates in each recalculation even when its immediate inputs have not changed. That does not mean a single formula must be slow, but many volatile references can increase the work required. Microsoft discusses OFFSET and INDIRECT volatility. I would not present INDIRECT as an automatic performance improvement because it is volatile too.

To retrieve C3 from this table, =INDEX(A1:C5,3,3) supplies an equivalent single-cell selection. The range has three columns, so the column index is valid. INDEX(A:A,3,3) would not be a suitable replacement: A:A contains only one column. Choose INDEX, a table or a direct reference according to the task; do not promise a measured speed improvement without examining the actual workbook.

Exercise: establish the address first

Derive the range for OFFSET(B2,1,0,2,2): B3:C4, whose sum is 101. Then set N in F1 to 3. The final three actual values occupy C3:C5 and sum to 95. For each answer, write the destination address before its value. This helps distinguish a positioning mistake from incorrect data in an otherwise correctly selected range.