Skip to content

Excel function · Lookup & reference

Excel OFFSET function

OFFSET starts from a cell, moves down and across by the amounts you give and returns a new range. Use it for calculations whose size changes, such as a year-to-date total or the average of the last three months.

In Turkish Excel: KAYDIR

What it does

OFFSET works with references, not values. You give a starting cell and say how many rows down and columns across to move; you can also set the height and width of the range that starts there. The result usually goes into a function such as SUM or AVERAGE.

Tie the height to a cell and the range grows or shrinks by itself: =SUM(OFFSET(B2,0,0,D2,1)) adds as many months as D2 says. If the number of rows to move comes from COUNT, the latest rows added to the list are picked up.

OFFSET is volatile: it recalculates on every change in the workbook and can slow large files down. INDEX often does the same job more lightly (=SUM(B2:INDEX(B2:B13,D2))); in Microsoft 365, TAKE returns the last rows of a list.

Syntax

English Excel=OFFSET(reference, rows, cols, [height], [width])
Turkish Excel=KAYDIR(başvuru; satırlar; sütunlar; [yükseklik]; [genişlik])

Arguments in square brackets are optional. English Excel separates arguments with commas (,) and uses a dot for decimals; Turkish Excel uses semicolons (;) and a decimal comma. Excel shows function names in its own language when it opens the file.

Arguments

ArgumentRequiredDescription
referenceRequiredThe cell or range to start from, e.g. B2.
rowsRequiredHow many rows to move down; a negative number moves up and 0 stays on the same row.
colsRequiredHow many columns to move right; a negative number moves left and 0 stays in the same column.
heightOptionalThe number of rows in the returned range. If omitted, the height of reference is used.
widthOptionalThe number of columns in the returned range. If omitted, the width of reference is used.

Examples

1Add up to a chosen number of months

OFFSET starts at B2, moves no rows or columns (0, 0) and returns a range as many rows high as the number in D2; SUM adds that range.

E2fx=SUM(OFFSET(B2,0,0,D2,1))
Add up to a chosen number of months: example table
ABCDE
1MonthSalesMonthsFirst N Months Total
2Jan182,0004745,600
3Feb167,500
4Mar201,300
5Apr194,800
6May210,250
7Jun188,900

D2 says 4, so the range is B2:B5 and you get the January–April total: 745,600. Set D2 to 6 and the total runs to June.

We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.

2Average the last three months

Five months are filled in column B so far, and the range is reserved to the end of the year, B13. COUNT finds the number of filled months; OFFSET moves down three fewer rows than that and returns a three-row range.

D2fx=AVERAGE(OFFSET(B2,COUNT(B2:B13)-3,0,3,1))
Average the last three months: example table
ABCD
1MonthSalesLast 3 Months Average
2Jan182,000202,117
3Feb167,500
4Mar201,300
5Apr194,800
6May210,250

With five months filled, the move is 2 rows and the range covers March–May: the average is 202,117. Once June is entered in B7, the formula moves to April–June by itself.

We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.

Common errors and fixes

#REF!

Cause: The move goes off the sheet (e.g. five rows up from row 2), or height or width is 0.

Fix: Check the rows and cols values; height and width must be at least 1.

#VALUE!

Cause: In older versions, a multi-cell result was put in a single cell without wrapping it in a function.

Fix: Pass the result to a function such as SUM or AVERAGE; in Microsoft 365 the result spills.

Slow workbook

Cause: The file has many OFFSET formulas and all of them recalculate on every change.

Fix: Build the range with INDEX where you can.

Good to know

  • The first cell of the returned range is reference moved by rows and cols; to start at reference itself, use 0 rows, not 1.
  • When you first build an OFFSET, check it with =ROWS(OFFSET(…)) to confirm the range has as many rows as you expect.

In Nexell

Get Nexell to do it from a sentence

Type this in Nexell

Add a column to the monthly sales table with the 3-month moving average for each month

Convert to table

Bring your monthly table into the tool and type this. Nexell builds the moving average with real Excel formulas and shows it in the preview; when you correct a month in the downloaded file, the averages change too.

Nexell uses AI to understand the request and builds the result with real Excel formulas; the formula may differ from the example on this page. You see a preview first and nothing changes until you press Apply; formulas stay live in the downloaded .xlsx file.

Free account · no card needed · 30 credits a month

Frequently asked questions

How do I sum the last 3 months with OFFSET?

Work out how far to move from the number of filled cells: =SUM(OFFSET(B2,COUNT(B2:B13)-3,0,3,1)). When a new month is entered in column B, the total moves to the latest three months.

Related functions

All lookup & reference functions →

Stop memorising formulas. Describe them.

Bring your table into the tool and say what you need; Nexell builds the formula and shows it in a preview.

Try it free