Using OFFSET in Excel: Syntax, Examples, Alternatives

Formulas & functions · Updated November 20, 2026

In Microsoft Excel for Microsoft 365, OFFSET returns a reference to a cell or range at a specified distance from a starting reference. With a label in A1 and the value 42 in A3, enter =OFFSET(A1,2,0) in another cell; the formula refers to A3 and displays 42.

What OFFSET does

OFFSET is a reference function: it starts from a cell or range, moves a chosen number of rows and columns, and can return a reference with a chosen height and width. It does not move cells or change the worksheet. It also does not, by itself, calculate a total or perform a lookup; another function can use the reference it returns.

The starting reference is the anchor. Positive row and column offsets move down and to the right, while negative offsets move up and to the left. A zero offset keeps that coordinate unchanged. For example, moving down two rows and across one column from C4 leads to D6. The reference remains within the worksheet grid; asking OFFSET to return a cell beyond the grid produces an error.

When the returned reference is used directly in a worksheet formula, Excel evaluates the referenced cell. When OFFSET supplies a range to a function such as SUM, that function works on the whole range. This distinction explains why OFFSET is commonly used to build flexible ranges rather than as a calculation on its own.

Arguments and how the function works

OFFSET takes a starting reference, a row displacement, and a column displacement. It can also take a height and width for the returned reference. If height and width are omitted, the returned reference has the same dimensions as the starting reference. A one-cell starting reference with height 5 and width 1 therefore produces a five-cell vertical range.

  • Reference: The starting cell or range. It must be a valid reference, not a text label that merely looks like one.
  • Rows: How far to move from the reference. Use a positive number to move down and a negative number to move up.
  • Cols: How far to move across. Positive numbers move right; negative numbers move left.
  • Height: The number of rows in the returned reference. If supplied, it must be positive.
  • Width: The number of columns in the returned reference. If supplied, it must be positive.

For a multi-cell starting reference, the row and column offsets are measured from its upper-left cell. Height and width define the size of the returned range, not the distance moved. In practice, it helps to check the destination and the returned range on the sheet before nesting OFFSET inside a larger formula.

A basic OFFSET example

Suppose A1 contains the word Start and A3 contains 42. With that layout, the formula =OFFSET(A1,2,0) returns the reference A3, so a cell containing the formula displays 42. The first argument identifies the anchor, the second moves two rows down, and the third keeps the column unchanged.

To move one column to the right and one row up from D5, use a row offset of -1 and a column offset of 1. Before entering a formula like this, make sure the destination cell exists within the worksheet; negative offsets are allowed, but they cannot take a reference above row 1 or to the left of column A.

OFFSET is often nested inside another function. With the five numbers 10, 12, 8, 15, and 5 in B2:B6, the formula =SUM(OFFSET(B2,0,0,5,1)) returns 50. The reference begins at B2, moves nowhere, and covers five rows by one column, so SUM adds B2:B6.

Creating ranges that change size

OFFSET can build a range whose height depends on how many entries are present. This is useful when a list grows over time and a calculation should include new entries without manually editing its end row. It works best when the list is continuous: blank cells in the middle can make a count-based range misleading.

For example, assume monthly sales are entered consecutively in B2:B13, with no blank cells, and the latest three months are 120, 135, and 145 in B11:B13. With that layout, =SUM(OFFSET(B2,COUNTA(B2:B13)-3,0,3,1)) returns 400. COUNTA finds 12 populated cells, so OFFSET starts nine rows below B2, at B11, and returns a three-cell range. SUM adds 120, 135, and 145.

This approach assumes the entries begin at B2 and remain contiguous. If a blank is left in the middle, COUNTA may count fewer entries than the last used row, causing OFFSET to start too early. If the list can contain gaps, use a method designed around the actual last occupied row, or clean the data so entries are consecutive. Also choose a sufficiently large source range: a formula cannot count entries that fall outside B2:B13.

Some people use OFFSET to create expanding chart or named ranges. That can be useful in workbooks built around traditional references, but a formula-based range is not automatically necessary for every modern chart or table. Excel Tables often expand their structured references when rows are added, which can be simpler to maintain.

Common errors and troubleshooting

The most frequent problem is #REF!. It means the requested reference is invalid, often because the row or column offset leads outside the worksheet or because the requested range extends beyond its edge. Check the anchor, direction, offset values, and returned height and width. Remember that a range may extend past the edge even when its upper-left cell is still on the sheet.

A #VALUE! error can occur when height or width is zero or negative. Use positive dimensions for the range size; use negative row or column offsets only when you intend to move upward or leftward. Check that the first argument is a real reference and that each argument is in the expected position. Accidentally swapping the row and column offsets is a common source of a plausible-looking but incorrect result.

If the formula returns an unexpected number rather than an error, trace the actual range it selects. For a five-row range beginning at B2, the intended cells are B2:B6. It is easy to mistake the offset values for absolute worksheet coordinates: a row offset of 5 from B2 points to row 7, not row 5. The range height is counted starting with the destination cell.

In formulas that count entries, verify that the count matches the data layout. COUNTA counts non-empty cells, including cells containing text and formulas that return an empty text value. A count-based OFFSET range can therefore include the wrong rows if the column contains headers, notes, gaps, or formulas outside the intended list.

Performance and recalculation

OFFSET is a volatile function. Excel recalculates volatile formulas whenever a calculation occurs, even if the cells they refer to have not changed. A few OFFSET formulas are usually manageable, but large numbers of them in a workbook with many calculations can add avoidable recalculation work.

This does not mean OFFSET is inherently wrong or that every workbook using it will be slow. The practical consideration is scale: if a workbook contains thousands of volatile formulas, unnecessary recalculation can affect responsiveness. If performance becomes a concern, consider whether the same task can use a nonvolatile reference method, a Table, or a simpler fixed range. Calculate the trade-off in the context of the workbook rather than replacing every OFFSET automatically.

When investigating a slow workbook, look at the overall formula design as well as OFFSET. Large ranges, repeated calculations, and other volatile functions can also contribute. A formula that is easy to understand and maintain may be preferable to a more complex alternative, as long as its recalculation cost is acceptable.

OFFSET compared with other options

INDEX is a common alternative when a formula needs a changing endpoint but does not need OFFSET’s movement in two directions or a dynamically sized reference. For example, suppose B2:B6 contains 10, 12, 8, 15, and 5, and B7:B13 are blank. With that layout, =SUM(B2:INDEX(B2:B13,COUNTA(B2:B13))) returns 50. COUNTA returns 5, INDEX identifies the fifth cell in B2:B13, and SUM adds B2:B6.

Unlike OFFSET, INDEX is nonvolatile in ordinary worksheet use, so it can be a better fit in a large calculation-heavy workbook. The example still depends on a continuous list: if there are gaps, COUNTA may not identify the last used row correctly. Choose the formula based on the shape of the data and the reference you actually need, not just on which function appears shorter.

Excel Tables are another practical alternative for growing lists. When a row is added directly beneath a Table, it generally becomes part of the Table, and formulas using structured references can include the new data without a constructed range. Tables also provide named columns, which can make formulas easier to interpret. If you need to return a reference a particular distance from an anchor, however, OFFSET may express that relationship more directly.

For many tasks, a fixed range is the clearest choice. If the data size is known and will not change, using B2:B6 avoids the extra logic and volatility. Before choosing OFFSET, ask whether the reference must move, whether its size must change, and whether a Table or INDEX can meet the requirement more simply.

Frequently Asked Questions

What does OFFSET do in Excel?
It returns a reference located a specified number of rows and columns from a starting reference. Optional height and width arguments set the size of the returned cell range.

Does OFFSET return a value or a reference?
OFFSET returns a reference. When used by itself in a worksheet formula, Excel displays the value from the referenced cell; when nested in functions such as SUM, the other function can use the returned range.

Why does OFFSET return #REF!?
The reference it is asked to create falls outside the worksheet grid, or its range extends beyond the grid. Check the starting cell, row and column offsets, and requested range dimensions.

Is OFFSET a volatile function?
Yes. Excel recalculates it whenever calculation occurs, even when its inputs have not changed. A small number of formulas is often fine, but many volatile formulas can add work in a large workbook.

Can I use OFFSET to make a dynamic range?
Yes. Its height can be calculated from a count of populated cells, provided the data layout matches the formula’s assumptions. Continuous lists are important; blanks or unrelated entries can make a simple count-based range select the wrong cells.

What can I use instead of OFFSET?
Consider INDEX for a changing endpoint, an Excel Table for a growing dataset, or a fixed range when the size will not change. INDEX is nonvolatile, while the best option depends on how the source data is organized.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.