Excel OFFSET Function: Syntax, Examples and How to Use It

By Dr. Zubair Khalid, DVM, MS, PhD · 2026-10-03

The Excel OFFSET function returns a cell or a range that sits a given number of rows and columns away from a starting reference. You use it when a formula needs to move its target as data grows, so a single offset calculation can replace a hard-coded range. It does not return a value by itself. It returns a reference, and that reference is then read by another function or by the formula around it.

Quick Answer

OFFSET returns a reference, not a value, so it is usually wrapped inside SUM, AVERAGE, COUNT or a lookup.
The syntax is OFFSET(reference, rows, cols, [height], [width]).
Positive rows move down, negative rows move up. Positive cols move right, negative cols move left.
Height and width are optional. Leave them out and OFFSET returns a single cell.
OFFSET is volatile. It recalculates on every sheet change, which can slow large workbooks.

Syntax

Argument	Required?	Meaning
reference	Yes	The starting cell or range. OFFSET counts from its top-left corner.
rows	Yes	How many rows to move from the reference. Positive is down, negative is up, 0 stays on the same row.
cols	Yes	How many columns to move from the reference. Positive is right, negative is left, 0 stays in the same column.
height	No	How many rows the returned range should span. Must be a positive number. Defaults to the height of reference.
width	No	How many columns the returned range should span. Must be a positive number. Defaults to the width of reference.

The formula pattern is:

$$ \text{OFFSET}(reference,\ rows,\ cols,\ [height],\ [width]) $$

Parentheses group the arguments, and Excel requires a closing bracket for every opening one [1].

How It Works

OFFSET treats the reference as an anchor. It moves the anchor by the rows and cols counts, then optionally expands the result into a block of cells using height and width.

Say the anchor is A1. The formula =OFFSET(A1, 2, 3) moves 2 rows down and 3 columns right, landing on D3. Because height and width are omitted, the result is that single cell.

Now add a size. The formula =OFFSET(A1, 2, 3, 2, 1) still starts at D3, but it returns a 2-row by 1-column range, which is D3:D4. Wrapped in a function, =SUM(OFFSET(A1, 2, 3, 2, 1)) adds the values in D3 and D4.

Two behaviors matter in practice.

First, the reference argument can be a range, not only a cell. If the reference is B2:D10, OFFSET counts from B2, the top-left corner, and the default height and width come from that range.

Second, the returned reference is live. If the cells it points to change, the formula updates. If the rows or cols counts are themselves formulas, the target moves as those inputs change. That is what makes OFFSET useful for dynamic ranges.

OFFSET pairs naturally with functions that accept a range. SUM totals it, AVERAGE averages it, COUNT counts numbers in it, and INDEX can pull a specific item from it. For fixed-size totals you would normally use a plain range or a function like SUM, but OFFSET earns its place when the size or position must be computed.

Worked Example

Suppose a small sales sheet lists monthly figures in B2:B13, one value per month, and you want a running total of the last three months as new rows are added.

Put the count of months to include in E1, for example 3. The formula for a rolling total of the most recent three entries is:

=SUM(OFFSET(B2, COUNT(B2:B13)-E1, 0, E1, 1))

Read it in steps. COUNT(B2:B13) returns how many numbers are in the column. Subtracting E1 gives the number of rows to skip from the anchor B2, so the block starts at the first of the last three entries. The 0 keeps the same column. The height E1 sets the block to three rows, and the width 1 keeps it to a single column. SUM then adds those three cells.

If the column holds 12 numbers and E1 is 3, the offset is 9 rows down from B2, which is B11, and the range is B11:B13. Because COUNT(B2:B13) only looks at rows 2 to 13, a 13th month in B14 would not be counted. Write the count as COUNT(B2:B100) and the same formula shifts to B12:B14 with no editing when a 13th month is added. That is the offset calculation doing its job.

More Examples

Return a single cell by position. With A1 as the anchor, =OFFSET(A1, 2, 3) returns D3. Change the 2 or the 3 and the target moves.

Average a dynamic block. =AVERAGE(OFFSET(A1, 0, 0, 5, 1)) averages A1:A5. The height of 5 sets the block size.

Look up a value with a computed position. Combine OFFSET with MATCH to find a row, then step across. If MATCH returns the position of a label in column A, =OFFSET(A1, MATCH("North", A2:A100, 0), 2) returns the value two columns to the right of that label. This is an alternative to INDEX and MATCH, though INDEX is the more common pairing.

Build a range from a text address. OFFSET can take a reference produced by INDIRECT when the sheet or cell address is stored as text.

Total a filtered or grouped block. OFFSET works with SUBTOTAL when you want a total that respects hidden rows inside a dynamic range.

Pull the last item in a column. =OFFSET(B2, COUNT(B2:B100)-1, 0) returns the last numeric entry, because the count minus one gives the row offset to the final value.

Extract part of a text string. OFFSET returns references, not text, so for character work you would use RIGHT or another text function instead.

Errors and How to Fix Them

#REF! appears when the offset pushes the reference outside the worksheet. If A1 is the anchor and you ask for 5 rows up, the result is off the sheet. Check that rows and cols do not move past the edges.

#VALUE! appears when rows, cols, height or width are not numbers. A text value used as a count causes this. A blank cell is read as 0, which is valid for rows and cols but gives #REF! for height or width. Wrap the count in a function that returns a number.

#NAME? appears when the function name is misspelled. A missing bracket does not produce #NAME?. Excel refuses to enter the formula and offers a correction, because it needs a closing parenthesis for each opening one [1].

A wrong range size is not an error message but a wrong answer. If height or width is larger than the data, OFFSET includes empty cells, and AVERAGE or COUNT returns a misleading figure.

A negative height or width returns #REF!. Sizes must be positive. Use negative rows and cols to move backward, not negative sizes.

Common Mistakes

Forgetting that OFFSET returns a reference, not a value. Fix: wrap it in SUM, AVERAGE, COUNT or another function that reads a range.
Counting rows from the wrong anchor. Fix: remember that OFFSET counts from the top-left corner of the reference, so a range reference starts at its first cell.
Using 1-based thinking for the offset. Fix: an offset of 0 means the same row or column, and an offset of 1 means one step away, so the first item is at offset 0.
Leaving height or width out when a block is needed. Fix: add both sizes when you want more than one cell, and match them to the real data size.
Building a whole-column OFFSET. Fix: anchor to a bounded range so the returned reference stays small and fast.
Ignoring volatility in big models. Fix: use INDEX for fixed lookups and reserve OFFSET for cases where the position or size genuinely changes.

Limitations

OFFSET is volatile. Excel recalculates it whenever anything in the workbook changes, even if the cells it points to did not change. In a workbook with thousands of OFFSET formulas, that constant recalculation can make typing and scrolling feel slow. INDEX is not volatile and often does the same job faster.

OFFSET also cannot see what it is pointing at. It returns a reference based on counts you supply, so if your counts are wrong, it silently returns the wrong cells. It does not warn you that a range is too small or too large. It also cannot return a value from a closed workbook, and it cannot create a reference to a sheet that does not exist. For most fixed lookups, a direct range or INDEX is safer and easier to audit.

Frequently Asked Questions

What does the OFFSET function do in Excel?

OFFSET returns a cell or range that is shifted a set number of rows and columns from a starting reference. It does not return the value in that cell. It returns the reference, which another function then reads. You use it to build ranges whose position or size changes as your data changes.

What is the difference between OFFSET and INDEX?

Both can return a reference to a cell. INDEX takes a row and column number inside a fixed range, and it is not volatile. OFFSET takes a distance from an anchor and can resize the result with height and width. OFFSET is more flexible for dynamic ranges, while INDEX is faster and more predictable.

Why does my OFFSET formula return #REF!?

The most common cause is an offset that moves past the edge of the worksheet, such as asking for rows above row 1 or columns left of column A. A negative height or width also returns #REF!. Check the rows, cols, height and width values, and confirm the anchor is where you think it is.

Can OFFSET create a dynamic named range?

Yes. You can define a name whose formula uses OFFSET with COUNTA or COUNT to size the range automatically. The name then expands as rows are added. Keep the counts bounded so the range does not grow to include blank cells far down the sheet.

Is OFFSET slow?

It can be. OFFSET is a volatile function, so Excel recalculates every OFFSET formula on each change to the workbook. A few are fine. Thousands in a large model can cause noticeable lag. Where a fixed range or INDEX works, prefer those.

References

Altman DG, Bland JM (2011). Brackets (parentheses) in formulas. BMJ