Excel OFFSET Formula Explained: Syntax and Examples

Formulas & functions · Updated November 19, 2026

Excel’s OFFSET function returns a reference to a cell or range located a specified number of rows and columns from a starting reference. For example, =OFFSET($A$1,3,1) refers to cell B4; use OFFSET inside a function such as SUM when you want to calculate with the returned range.

What OFFSET does

Think of OFFSET as a way to describe a location relative to a starting point. It does not move, copy, or change any worksheet cells. Instead, it tells Excel which cell or range to refer to. That reference can then be used by another function, or a single-cell reference can appear in a formula by itself and display the value from that cell.

For example, starting at A1, moving three rows down and one column to the right lands at B4. The row and column movements are counted from the starting reference: zero means stay in the same row or column, a positive number moves down or right, and a negative number moves up or left. The resulting location must still be inside the worksheet, or OFFSET returns an error.

OFFSET is useful when a formula needs to refer to a range whose position or size changes, such as the most recent entries in a list. It is also used in named ranges and some chart setups. For straightforward calculations, however, a normal cell reference or another function may be easier to understand and maintain.

OFFSET syntax and its arguments

For the syntax template below, assume the reference argument is any valid cell or range on the worksheet; the template shows the function’s argument order rather than a particular dataset.

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

The arguments mean:

  • reference: The starting cell or range. OFFSET measures from this reference.
  • rows: How many rows to move. Use a positive number to move down, a negative number to move up, or zero to remain in the same row.
  • cols: How many columns to move. Positive moves right, negative moves left, and zero stays in the same column.
  • height: Optional number of rows in the returned reference. It must be greater than zero.
  • width: Optional number of columns in the returned reference. It must be greater than zero.

If height and width are omitted, the returned reference has the same dimensions as the starting reference. If you provide only height, the width keeps the starting reference’s width. The returned reference can be one cell or a larger range, depending on the starting reference and the optional size arguments.

In Excel formulas, arguments are separated by commas under the standard Microsoft 365 settings shown here. Some regional settings use semicolons instead; if Excel rejects a formula with commas, the list separator configured on that computer may differ.

A worked cell-reference example

Assume a worksheet has a small table with headers in A1:C1: Month in column A, Sales in column B, and Units in column C. The rows run from January in row 2 through June in row 7. In this layout, A1 is the starting cell, and the formula below moves three rows down and one column right.

=OFFSET($A$1,3,1)

This refers to B4, the Sales value for March. With the example data, B4 contains 128, so entering the formula in an ordinary single cell displays 128. The dollar signs lock the starting reference if you copy the formula elsewhere; they do not alter the movement described by the row and column arguments.

OFFSET can also return a range. With the same headers and data layout, the next formula starts at A1, moves one row down and one column right to B2, then specifies a range three rows high and two columns wide.

=SUM(OFFSET($A$1,1,1,3,2))

The OFFSET portion refers to B2:C4, covering January through March for both Sales and Units. If Sales are 120, 135, and 128 and Units are 12, 14, and 13, SUM adds all six cells and returns 422. SUM is doing the addition; OFFSET supplies the range to add.

Using OFFSET with SUM and other functions

A common reason to use OFFSET is to create a range for another function. SUM, AVERAGE, COUNT, and similar functions can work with the reference it returns. This is more useful than asking OFFSET alone to represent several cells: a normal formula cell is designed to show one result, while a function such as SUM can process a multi-cell range and return one value.

With Sales in B2:B7, using the same monthly values above, this formula adds the first three months:

=SUM(OFFSET($B$2,0,0,3,1))

The reference starts at B2, does not move, and is three rows tall and one column wide, so it covers B2:B4. The result is 120 + 135 + 128 = 383. Changing the height from 3 to 4 would extend the range through B5, assuming the starting point remains B2.

OFFSET can also be combined with AVERAGE or COUNT, but consider whether a conventional range is clearer. For a fixed three-month period, =AVERAGE(B2:B4) is easier to read than a function that constructs the same range. OFFSET is most compelling when the reference genuinely needs to shift or resize based on other worksheet values.

Building a range that grows with a list

One traditional pattern is to sum from the first data cell through the last populated cell. Assume B1 contains the Sales header and B2:B7 contains the six monthly values, with no blank cells among the data. The formula below counts nonempty cells in column B, adjusts for the header and starting cell, and uses OFFSET to identify the last entry.

=SUM($B$2:OFFSET($B$2,COUNTA($B:$B)-2,0))

COUNTA($B:$B) returns 7 because B1:B7 are populated. Subtracting 2 gives 5, so OFFSET moves five rows down from B2 to B7. The colon joins B2 and that endpoint into the range B2:B7, and SUM returns 835 for the example values. If a new monthly value is entered in B8, the count rises to 8 and the formula extends through B8.

This pattern depends on its layout assumptions. A blank within the data can cause COUNTA to underestimate the final row, and other content elsewhere in column B can make it count too much. A bounded range or an Excel Table may be safer in a workbook with notes, gaps, or unrelated values. Whole-column references are convenient, but they also make the formula’s dependency broader than the data list itself.

How range size and direction work

Rows and columns specify where the reference begins; height and width specify how large it is. These are separate decisions. For instance, a movement of two rows and one column does not itself create a two-by-one range. Without size arguments, the returned reference inherits the dimensions of the starting reference.

Negative movement is allowed when the destination remains on the sheet. If the starting point is D5, moving up two rows and left one column points to C3. But a result that would require a row above row 1 or a column left of column A is outside the worksheet and produces #REF!. Height and width describe the size of the range, not a direction to extend it backwards; use a suitable top-left starting cell for the range you want.

A zero height or zero width is invalid and produces #REF!. When building a formula from cell inputs, check that calculations used for height and width cannot become zero or negative. Also check the final destination: a formula can work for one list length and fail when the list grows close to the worksheet edge.

Common OFFSET problems and how to fix them

When OFFSET does not return the expected result, first verify the starting reference, then check the direction and size arguments. A one-row error often comes from counting the starting cell as the first move: an offset of 1 means the next row, while 0 means the current row.

  • #REF!: The destination is outside the worksheet, or height or width is zero or invalid. Check the computed address and range dimensions.
  • An unexpected value: OFFSET may be pointing to the wrong cell because rows and columns were reversed or counted from the wrong starting reference. Trace the destination on the sheet.
  • A range that is too short or too long: Review the height and width arguments and any COUNTA or other calculations used to generate them.
  • A dynamic range stops early: Check for blank cells when the formula relies on COUNTA, and check whether the counting range includes a header or unrelated entries.
  • A formula appears not to update: Check that Excel calculation is set to Automatic under Formulas > Calculation Options, and confirm that the formula’s source cells are included in the calculation.

OFFSET returns a reference, not a cleaned-up or filtered dataset. If the source range contains blanks, text, or errors, the function using that reference handles them according to its own rules. For example, SUM generally ignores text in referenced cells but an error in the summed range can make the result an error. Diagnose the consuming function as well as OFFSET.

Performance and volatility

OFFSET is a volatile function in Excel. Volatile formulas recalculate whenever Excel recalculates, even when a change is not directly related to their referenced cells. A handful of OFFSET formulas is usually manageable, but thousands of volatile formulas in a large workbook can add recalculation work and make the file feel slower.

That does not mean OFFSET is inherently wrong or should never be used. It means the function has a performance trade-off worth considering in a large or calculation-heavy workbook. Avoid copying volatile formulas across a very large grid without need, and prefer a direct range or nonvolatile approach when it provides the same result clearly. A full-column COUNTA reference can add another broad dependency, so a carefully chosen data range or structured Table reference may be more maintainable.

If a workbook’s calculations seem slow, identify how many formulas use volatile functions and whether they are duplicated across large areas. Replacing OFFSET indiscriminately is not necessary; first confirm that it is a meaningful source of recalculation cost and that an alternative preserves the workbook’s behavior.

OFFSET compared with INDEX and other options

INDEX is a common alternative when the goal is to select a cell within a known range. It is generally nonvolatile and can be easier to audit when the row or column number is explicit. Assume Sales values are in B2:B7, with 120, 135, 128, 150, 142, and 160 respectively. To return the fourth item in that range, use:

=INDEX($B$2:$B$7,4)

The formula returns 150, the value in B5. Unlike the example OFFSET formula, the INDEX formula describes the target by its position within a specified range rather than by moving from a starting cell.

For a growing sum with contiguous entries in B2:B7 and no blanks, this INDEX-based version returns the same total of 835 while using a bounded range:

=SUM($B$2:INDEX($B$2:$B$100,COUNTA($B$2:$B$100)))

Here COUNTA counts the six values, INDEX identifies the sixth cell in B2:B100 (B7), and SUM covers B2:B7. This still relies on contiguous nonblank data, but it avoids OFFSET’s volatility. In Microsoft 365 or Excel 2021 and later, functions such as XLOOKUP or FILTER can be more suitable when the real task is finding a matching record or returning rows that meet conditions. Those functions solve different problems; they are not automatic substitutes for every reference OFFSET can construct.

Use OFFSET when relative movement or a dynamically sized reference is genuinely useful. Use a direct range for a fixed calculation, INDEX when a position within a known range is clearer, and a lookup or dynamic-array function when the task is searching or returning a changing set of results.

Frequently Asked Questions

Does OFFSET return a value or a reference?
OFFSET returns a reference to a cell or range. A formula using a single-cell reference can display that cell’s value, while a function such as SUM can calculate over a multi-cell reference.

Why does Excel OFFSET return #REF!?
Usually the calculated destination is outside the worksheet, or the requested height or width is zero or invalid. Check both the movement from the starting reference and the range dimensions.

Is OFFSET volatile in Excel?
Yes. Excel recalculates OFFSET when it recalculates the workbook, even if its inputs have not directly changed. This can matter when many OFFSET formulas are used.

Can OFFSET move left or up?
Yes. Negative values for rows move up, and negative values for columns move left. The resulting cell or range must remain within the worksheet boundaries.

What is a good alternative to OFFSET?
For a fixed reference, use ordinary cell addresses. INDEX can select a position within a range and is generally nonvolatile; for searches or filtered results, choose a lookup or dynamic-array function that matches the task.

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.