Excel functions / Lookup

OFFSET function

A range a set number of rows and columns away from a starting cell, of any size.

Syntax

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

Arguments

When to use it

OFFSET starts at a cell and moves a number of rows and columns from it. It can also give back a range of any height and width from that point, which makes it the classic way to total "the last three months" or a list that keeps getting longer.

Rows and columns count from 0: =OFFSET(A1, 0, 0) is A1 itself, and =OFFSET(A1, 2, 1) is two rows down and one column to the right, B3.

Examples

  1. Two rows down, one column across

    ABCD
    1MonthSales
    2Jan100150
    3Feb150
    4Mar90
    5Apr120

    In D2: =OFFSET(A1, 2, 1) → 150

    Try this example

  2. Add up the last three months, however long the list gets

    ABCD
    1MonthSales
    2Jan100360
    3Feb150
    4Mar90
    5Apr120

    In D2: =SUM(OFFSET(B1, COUNTA(B:B) - 3, 0, 3, 1)) → 360

    COUNTA counts the filled cells in column B (5, with the heading), so the range starts 2 rows below B1 and is 3 rows tall. Add May and it moves down by itself.

    Try this example

  3. A block of cells

    ABCDE
    1MonthSales
    2Jan100Jan100
    3Feb150Feb150
    4Mar90
    5Apr120

    In D2: =OFFSET(A1, 1, 0, 2, 2) → Jan, 100; Feb, 150

    Try this example

Common mistakes

Common questions

How do I sum the last N rows in Excel?

Use OFFSET with COUNTA: =SUM(OFFSET(B1, COUNTA(B:B) - 3, 0, 3, 1)) adds the last three filled cells of column B. Change both 3s to the number of rows you want.

OFFSET in other languages

Excel shows function names in its own language. In these languages it also puts a semicolon between arguments where English has a comma.

Translate a whole formula

Try OFFSET in the playground

Related functions