OFFSET

What it does

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

OFFSET is one of the few worksheet functions whose result is a reference, not a value. It performs arithmetic in the address space of the grid.

Given a base reference B whose top-left cell is at row r₀ and column c₀, and whose extent is h₀ × w₀, together with displacements Δr and Δc and optional dimensions h and w, OFFSET returns the rectangular area

• whose top-left cell is at row r₀ + Δr and column c₀ + Δc,
• with height h (default h₀) and width w (default w₀).

Note precisely what the defaults mean: the returned area inherits the base reference's shape, not a single cell. OFFSET(A1:C3, 1, 1) is B2:D4, a 3×3 area — not B2. This is the detail that most surprises readers who think of OFFSET as "the cell n down from here".

Microsoft states the other half explicitly: OFFSET does not move any cells or change the selection; it computes an address. What happens to that address afterwards is the caller's business — a consuming function resolves it, an aggregate scans it, a cell publishes its contents (or spills them).

The value of OFFSET is that its displacement and its size are computed. OFFSET(A1, 0, 0, COUNTA(A:A), 1) is a range whose length tracks the data — the classic dynamic-range idiom, and the reason OFFSET outlived several attempts to replace it.

Arguments

reference — the base. Must be a single cell or a contiguous range; a multi-area union is rejected. Its size supplies the defaults for height and width.

rows — vertical displacement, positive downward, negative upward.

cols — horizontal displacement, positive rightward, negative leftward.

height — optional row count of the result, defaulting to the base reference's height.

width — optional column count of the result, defaulting to the base reference's width.

Try it

Quirks

Result and edge cases

The result kind is ReferenceLike — an A1 shape when the computed extent is a single cell, an Area shape otherwise. This matters because a reference can be consumed in ways a value cannot: it can be handed to SUM, to ROWS, to CELL("address", …), or to the : range operator, and it can be resized again.

• Published into a cell, an area-shaped result is materialized: on a modern Excel it spills; on the legacy grid model an unrequested area produced a #VALUE! unless entered as an array formula. Which of those a reader sees is a property of the host and the workbook's calculation surface, not of OFFSET.
• Displacement off the grid is an error, not a clamp. Moving before row 1 or column A, or past the last row or column, yields #REF!.
• A base reference on another sheet displaces within that sheet; OFFSET carries the sheet identity of its base.
• Reference resolution and the values-versus-references distinction are described once in the call pipeline, stage 1; OFFSET is one of the named RefsVisibleInAdapter functions there.

Volatility

OFFSET is volatile. It is recomputed on every recalculation of the workbook, whether or not any cell it depends on has changed, and everything downstream of it is recomputed too.

This is not an implementation choice that could be optimized away. OFFSET's arguments name a displacement, so the engine cannot know which cells the result will designate until it has evaluated the function — the dependency graph cannot be built statically from the formula text. Volatility is the price of that late binding.

The practical consequence is that OFFSET used at scale is a recalculation cost multiplier: a few thousand volatile cells, each with a dependent subtree, is a workbook that pauses on every edit. The usual modern replacements are:

• structured table references (Table1[Amount]), which grow with the table and are not volatile;
• INDEX, which returns a reference from an explicit range and is not volatile — the A1:INDEX(A:A, n) idiom builds a dynamic range without volatility;
• spill anchors (B1#), which reference a dynamic array's current extent directly;
• TRIMRANGE and the trim-reference operators, for the specific case of "the used part of this column".

Errors

Documented by Microsoft:

How to implement

Notes for implementers

• The first argument must arrive reference-bearing. If argument preparation dereferences it, the function cannot be written: there is no address left to displace.
• The result must be a reference, not the values at that address. A tempting shortcut is to materialize the area and return an array; it produces the right numbers in SUM(OFFSET(...)) and the wrong answer everywhere the reference identity is observed — CELL("address", OFFSET(...)) is the cheap test.
• Default height and width come from the base reference's extent, which means the function needs the base's shape, not just its top-left cell.
• Volatility must be declared, not inferred. An engine that constant-folds OFFSET because all its arguments are literals will produce a stale reference after a row insertion.

Rust code

CatalogueEntry {
    id: OFFSET,
    spelling: "OFFSET",
    notation: Notation::Call,
    arity: CallableArityShape::range(3, 5),
    parameters: &[
        Parameter::reference("reference"),
        Parameter::value("rows"),
        Parameter::value("cols"),
        Parameter::value("height"),
        Parameter::value("width"),
    ],
    demand: ArgumentDemand::Eager,
    kernel: Kernel::Eager(offset),
}

fn offset(
    arguments: &[CalcValue],
    services: &mut EvalServices<'_>,
) -> Result<CalcValue, FunctionFault> {
    // dc-ylr: Excel gives OFFSET of a reference to a closed workbook #VALUE!.
    let external = arguments[0].as_reference().is_some_and(|reference| {
        reference.areas().iter().any(|area| {
            services
                .host()
                .is_some_and(|host| host.is_external_sheet(area.sheet()))
        })
    });
    let site = Site::new(OFFSET, services);
    if external {
        return Site::finish(Err(Exit::Error(WorksheetErrorCode::Value)));
    }
    Site::finish(shifted(&site, arguments))
}

fn shifted(site: &Site<'_>, arguments: &[CalcValue]) -> Step<CalcValue> {
    site.scalar_operands(arguments)?;
    let Some(reference) = arguments[0].as_reference() else {
        return Err(Exit::Error(WorksheetErrorCode::Value));
    };
    let [base] = reference.areas() else {
        return Err(Exit::Error(WorksheetErrorCode::Value));
    };
    let rows = whole(site, 1, &arguments[1])?;
    let cols = whole(site, 2, &arguments[2])?;
    let height = extent(site, 3, arguments.get(3), base.row_count())?;
    let width = extent(site, 4, arguments.get(4), base.column_count())?;
    let top = i64::from(base.first().row()) + rows;
    let left = i64::from(base.first().column()) + cols;
    let (top, bottom) = span(top, height);
    let (left, right) = span(left, width);
    let on_grid = |low: i64, high: i64, max: u32| low >= 1 && high <= i64::from(max);
    if !on_grid(top, bottom, MAX_ROWS) || !on_grid(left, right, MAX_COLUMNS) {
        return Err(Exit::Error(WorksheetErrorCode::Ref));
    }
    let sheet = base.sheet();
    let corner = |row: i64, column: i64| {
        CellKey::new(sheet, row as u32, column as u32).expect("checked on the grid")
    };
    let area = GridArea::new(corner(top, left), corner(bottom, right))
        .expect("both corners lie on one sheet");
    Ok(CalcValue::reference(ReferenceLike::area(area)))
}

/// Argument `index` read as a number and truncated toward zero; a value beyond any grid offset
/// is kept large enough to leave the grid.
fn whole(site: &Site<'_>, index: usize, value: &CalcValue) -> Step<i64> {
    let n = site.number_argument(index, value)?.trunc();
    Ok(n.clamp(-4_000_000.0, 4_000_000.0) as i64)
}

/// The height or width argument `index`, truncated: the reference's own `own` when it is not
/// given, #REF! when it is 0.
fn extent(site: &Site<'_>, index: usize, value: Option<&CalcValue>, own: u32) -> Step<i64> {
    match value {
        None => Ok(i64::from(own)),
        Some(value) if value.is_missing() => Ok(i64::from(own)),
        Some(value) => match whole(site, index, value)? {
            0 => Err(Exit::Error(WorksheetErrorCode::Ref)),
            n => Ok(n),
        },
    }
}

No implementation has been published with a Handbook test suite for this function.

Version differences

No comparison between specific Excel builds is recorded here yet. This does not establish that all builds behave alike.

Alternatives

Relationships

• INDEX is the closest sibling and the recommended replacement for most dynamic-range work: it also returns a reference, but from an explicit range and without volatility.
• INDIRECT is the other reference-producing function, and is also volatile; it builds a reference from text, which is strictly more dangerous and strictly less analyzable.
• ROW, ROWS, COLUMN, COLUMNS, CELL, ADDRESS are the rest of the reference-inspection family; OFFSET is the reference-producing member.
• TRIMRANGE and the trim-reference operators occupy some of the ground OFFSET was used for — trimming a whole-column reference down to its populated extent — without volatility.
• Readers confuse OFFSET with INDEX's reference form (which selects within a range rather than displacing from it) and with INDIRECT.

Named variants

No named variants are recorded yet.