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
reference: The cell or range to start from.rows: How many rows down (negative for up).cols: How many columns right (negative for left).height(optional): How many rows the result has (default: as many as the starting range).width(optional): How many columns the result has (default: as many as the starting range).
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
Two rows down, one column across
A B C D 1 Month Sales 2 Jan 100 150 3 Feb 150 4 Mar 90 5 Apr 120 In D2:
=OFFSET(A1, 2, 1)→ 150Add up the last three months, however long the list gets
A B C D 1 Month Sales 2 Jan 100 360 3 Feb 150 4 Mar 90 5 Apr 120 In D2:
=SUM(OFFSET(B1, COUNTA(B:B) - 3, 0, 3, 1))→ 360COUNTA 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.
A block of cells
A B C D E 1 Month Sales 2 Jan 100 Jan 100 3 Feb 150 Feb 150 4 Mar 90 5 Apr 120 In D2:
=OFFSET(A1, 1, 0, 2, 2)→ Jan, 100; Feb, 150
Common mistakes
- Counting from 1. The row and column arguments are how far to move, not which row to land on. To stay on the starting row, use 0.
- Moving off the sheet. A negative move from A1, or any move past the edge, gives #REF!.
- Slow workbooks. Like INDIRECT, OFFSET recalculates on every change. For one cell,
=INDEX(B2:B100, n)does the same job and only recalculates when its inputs change.
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.
- Czech: POSUN
- Danish: FORSKYDNING
- Dutch: VERSCHUIVING
- Finnish: SIIRTYMÄ
- French: DECALER
- German: BEREICH.VERSCHIEBEN
- Hungarian: ELTOLÁS
- Italian: SCARTO
- Norwegian: FORSKYVNING
- Polish: PRZESUNIĘCIE
- Portuguese (Brazil): DESLOC
- Portuguese (Portugal): DESLOCAMENTO
- Russian: СМЕЩ
- Spanish: DESREF
- Swedish: FÖRSKJUTNING
- Turkish: KAYDIR