OFFSET Function in Excel: Move and Resize a Range

The OFFSET function in Excel starts at one cell, moves a set number of rows and columns, and returns the cell or range it lands on, at whatever size you ask for. That makes it useful for "the last 3 months", moving averages and ranges that grow as you add data. This guide explains the syntax with small examples, the volatility catch, and when INDEX does the same job better.

What does the OFFSET function do?#

OFFSET returns a reference: an address of a cell or range, not a value. Think of it as giving directions: "start at B1, go 4 rows down and 0 columns across, then take a block 3 rows tall and 1 column wide."

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

If you leave out height and width, the result is the same size as the starting reference. OFFSET works in every version of Excel.

All the examples use this monthly sales list:

OFFSET examples: move, then resize#

Return one cell#

=OFFSET(B1,3,0)

Start at B1, move 3 rows down, stay in the same column. That lands on B4, so the result is 5,100 (March). Moving 0 rows returns B1 itself.

Return a range and add it up#

Because OFFSET returns a range, wrap it in a function such as SUM:

=SUM(OFFSET(B2,0,0,3,1))

Start at B2, don't move, and take a block 3 rows tall and 1 column wide: B2:B4. The result is 4,200 + 3,900 + 5,100 = 13,200, the first quarter. The SUM function guide covers the basics of adding ranges.

In Microsoft 365, typing =OFFSET(B2,0,0,3,1) on its own spills the three values into the cells below. In older versions it shows #VALUE!, which is why you'll often see OFFSET inside SUM, AVERAGE or COUNT.

How to sum the last N months with OFFSET#

The classic OFFSET job is a total that always covers the latest months, even as new rows are added. Use COUNT to find how many months there are:

=SUM(OFFSET(B1,COUNT(B2:B7)-2,0,3,1))

• COUNT(B2:B7) is 6 months.
• 6-2 = 4, so the start moves from B1 down to B5.
• Height 3 makes the range B5:B7.

The result is 4,800 + 5,600 + 6,100 = 16,500. When you type July into row 8 (and widen the COUNT range), the window slides to May–July by itself.

A moving average works the same way. In C4, =AVERAGE(OFFSET(B4,-2,0,3,1)) averages B2:B4 (4,400), and filling it down makes each row average itself and the two rows above. The guide to calculating a moving average shows simpler ways too.

Why OFFSET can slow a workbook: volatility#

OFFSET is a volatile function. A volatile function recalculates every time anything in the workbook changes, even a cell it doesn't use. TODAY, NOW and INDIRECT behave the same way.

A handful of OFFSET formulas is fine. Thousands of them, or OFFSET inside a big lookup, can make a file lag after every keystroke. OFFSET formulas are also harder to audit, because you can't click the range they point to; you have to work out the directions in your head.

That doesn't make OFFSET wrong. It just means that for large or shared models, a non-volatile alternative is worth knowing.

How to do the same thing with INDEX#

INDEX returns the cell at a position inside a range, and it is not volatile. Most OFFSET formulas have an INDEX version:

The last row uses a lesser-known trick: two INDEX results joined by a colon form a range, here B5:B7. Both versions return 16,500. The INDEX function guide explains the arguments, and the full guide to the INDEX function shows more ways to pick values by position.

In Microsoft 365 or Excel 2024 there is an even shorter option: =SUM(TAKE(B2:B7,-3)), where TAKE with a negative number returns the last 3 rows.

Common uses: running totals and dynamic ranges#

You'll meet OFFSET in older files in two other places.

Running totals. Some files use =SUM(OFFSET($B$2,0,0,ROW()-1,1)). A plain expanding range, =SUM($B$2:B2) filled down, does the same thing and is easier to read. The guide to running totals walks through it.

Dynamic named ranges. Before Excel tables and spill ranges existed, a named range like this grew with a list in column A:

=OFFSET($A$2,0,0,COUNTA($A:$A)-1,1)

It starts at A2 and is as tall as the number of filled cells minus the header. People used it as the source of a drop-down list. Today, an Excel table or a spill reference is usually simpler; see how to make a dynamic drop-down list.

Why OFFSET returns #REF! or the wrong range#

Check these when the result looks wrong:

• #REF!: the range runs off the sheet, such as =OFFSET(A1,-1,0), or the height or width is 0.
• Off by one. The starting cell counts as 0, so OFFSET(B1,1,0) is B2, one row below the start. Many mistakes come from starting at the header row or the first data row without adjusting.
• Blank cells in the COUNT range. A gap makes COUNT too small, so the window ends early.
• #VALUE! in older Excel: a multi-cell OFFSET on its own needs a wrapper like SUM.

Practise it#

OFFSET is mostly about counting positions, and INDEX uses the same skill without the volatility. Practise INDEX and MATCH in a live spreadsheet: you'll pick values by position and build lookups, with each answer checked instantly. If ranges and totals are new to you, start with the SUM and AutoSum lesson. The INDEX MATCH guide is a good next read.