Excel OFFSET Function: How It Works and When to Use It

Formulas & functions · Updated November 20, 2026

In Excel, use OFFSET to refer to a cell or range a chosen number of rows and columns away from a starting reference. With a value in A1 and a value of 42 in B3, enter =OFFSET(A1,2,1) in another cell; it returns 42, because B3 is two rows down and one column to the right of A1.

What OFFSET does

OFFSET builds a reference based on a starting point and a displacement. The starting point can be a single cell or a range. The row and column arguments tell Excel how far to move from that point; optional height and width arguments set the size of the resulting reference. OFFSET does not itself add, count, or look up data. It identifies a location or range, which you can use alone to return a cell’s contents or combine with another function such as SUM.

The direction is relative to the reference: positive row numbers move down, negative numbers move up, positive column numbers move right, and negative numbers move left. Zero means stay in the same row or column. For example, a reference starting at D5 with a row offset of 0 and a column offset of -1 points to C5. The resulting reference must stay within the worksheet; moving beyond its edges produces an error.

OFFSET syntax and arguments

The function signature is OFFSET(reference, rows, cols, [height], [width]). The square brackets indicate optional arguments; do not type them when entering a formula. The arguments mean:

  • reference: The starting cell or range. It is the point from which Excel measures the offset.
  • rows: How many rows to move from the reference. Use a negative number to move upward.
  • cols: How many columns to move. Use a negative number to move left.
  • height: The number of rows in the returned reference. If omitted, Excel uses the height of the starting reference.
  • width: The number of columns in the returned reference. If omitted, Excel uses the width of the starting reference.

For a single-cell reference such as A1, omitting height and width leaves a one-cell reference. When you supply those arguments, they must describe a positive-sized range. A formula using OFFSET can be entered in a worksheet cell like other formulas, or used inside functions that accept references.

A simple cell-reference example

Suppose A1 is the starting point and B3 contains 42. The formula in the opening example moves two rows down from row 1 and one column right from column A, so it refers to B3. In a blank destination cell, Excel displays the value found in that referenced cell. If B3 changes later, the displayed result changes too.

OFFSET can also return a range rather than just one cell. With a starting cell A1, a row offset of 2, a column offset of 1, a height of 3, and a width of 2, the resulting reference is B3:C5. The height and width describe the size of the destination range, not how far to move. This distinction is useful: rows and cols position the range, while height and width shape it.

Sum a moving range

A common use is to sum the latest few entries in a growing list. Assume monthly amounts are entered without gaps in B2:B13, with all 12 cells populated. The last three amounts are 80, 95, and 110. In a result cell, use this formula:

With monthly amounts in B2:B13, all cells filled, and the latest three values equal to 80, 95, and 110, enter =SUM(OFFSET(B2,COUNTA(B2:B13)-3,0,3,1)). The result is 285.

Here, COUNTA counts 12 nonempty cells. Subtracting 3 gives a row offset of 9 from B2, which lands at B11. OFFSET then refers to the three-cell, one-column range B11:B13, and SUM adds those values. This example relies on a continuous list of populated cells. If blanks appear inside the list, or if the count includes labels or unrelated content, the calculated starting point may not be the intended one.

Build and check an OFFSET formula

When constructing a formula, decide first whether the desired result is one cell or a range. Then identify a stable starting reference and work out the displacement and final dimensions. For a range, it can help to mark the intended top-left and bottom-right cells on the sheet before translating them into row and column offsets.

  1. Select the cell where the result should appear and type an equals sign followed by OFFSET and an opening parenthesis.
  2. Enter the starting reference, row movement, and column movement in that order.
  3. Add height and width only when you need a reference larger than the starting reference.
  4. Close the parenthesis. If the result should be calculated, wrap OFFSET in an appropriate function, such as SUM.
  5. Compare the formula’s intended destination with the highlighted reference or the actual worksheet cells. Check that the reference does not run off the worksheet and that any counted list has the structure the formula expects.

In Microsoft 365 for Windows, OFFSET is available from the Formulas tab in the Lookup & Reference function category, and Excel’s formula suggestions can help insert the function name. You can also type the formula directly in the formula bar or a cell. The function works in Excel for Mac and Excel for the web as well; ribbon layout and some interface details can differ, but the formula syntax is the same.

Dynamic ranges, charts, and other uses

Because OFFSET can change the size or position of a reference as worksheet data changes, it has been used for dynamic named ranges, rolling summaries, and charts that should include a changing number of entries. For example, a named range could be defined to cover the populated part of a column, then used as a chart’s data source. Such setups need careful handling of headers, blank cells, and the first or last item in the list. A formula that counts the wrong cells can make a chart omit data or include an unintended area.

OFFSET can also support reports that summarize a recent period, such as the latest three or six rows of a log, provided the data is kept in a predictable layout. Its flexibility is most useful when the position or dimensions of a reference genuinely need to change. For a fixed range, a direct reference is usually easier to read. For a list that grows over time, an Excel Table may be simpler: tables expand as rows are added and offer structured references that are easier for many readers to understand.

OFFSET compared with alternatives

OFFSET is not the only way to create a changing reference. INDEX can be used to identify a position without using a volatile function, and Excel Tables often remove the need to calculate a range’s size at all. The best choice depends on whether the formula needs a reference, a returned value, or an automatically expanding data source.

  • Use a direct cell or range reference when the data area is fixed. It is straightforward and avoids extra calculation logic.
  • Consider an Excel Table when rows are regularly added to a dataset. Table columns generally adjust as data is appended, which can make formulas and charts easier to maintain.
  • Consider INDEX when you need a position-based reference but want to avoid OFFSET’s volatile recalculation behavior. The exact formula depends on the layout and the intended range.
  • Use XLOOKUP or another lookup function when the task is to find a matching value, not to construct a range by moving from a starting cell. XLOOKUP is available in Microsoft 365 and Excel 2021 or later.

OFFSET is classified as volatile, meaning Excel recalculates it when a calculation occurs, even when a change may not affect its direct inputs. In a small workbook this may not be noticeable. In a large workbook with many OFFSET formulas, recalculation can contribute to slower performance. This is a reason to prefer a nonvolatile approach when it meets the same need, not a reason to avoid OFFSET in every workbook.

Common errors and troubleshooting

A #REF! error commonly means that the requested reference extends beyond the worksheet’s row or column limits. Check the starting cell and the direction and size of the offset. A #VALUE! error can occur when an argument is not a usable number or when a supplied height or width is less than 1. Review the commas and parentheses as well, especially when nesting OFFSET inside another function.

A formula can also return a plausible but incorrect result without showing an error. This often happens when the source range contains blanks, headers, or extra values that a COUNTA-based calculation includes. Confirm the exact cells the function is referring to rather than relying only on the displayed total. In the rolling-sum example, a blank among the monthly values breaks the assumption that the last three occupied entries are the final three rows.

Another frequent misunderstanding is to treat OFFSET as a lookup function. It does not search for a label such as a month name; it moves a fixed number of rows and columns from a reference. If the location of a matching label can vary, use a lookup approach instead. Also remember that formulas returning references may behave differently depending on the surrounding function: SUM can aggregate the referenced range, while a standalone one-cell OFFSET typically displays the referenced cell’s contents.

Frequently Asked Questions

Does OFFSET return a value or a reference?
OFFSET returns a reference. When the reference is one cell and the formula is entered by itself, Excel displays the value in that cell. When the reference covers multiple cells, use a function such as SUM if you want a single calculated result.

Why does my OFFSET formula show #REF!?
The requested destination may extend beyond the worksheet, or the starting reference may not be valid. Check the direction of the row and column movements, then verify the height and width against the remaining space on the sheet.

Can OFFSET move to the left or up?
Yes. Use a negative number for rows to move up and a negative number for columns to move left. The resulting reference still has to remain within worksheet boundaries.

Is OFFSET volatile in Excel?
Yes. Excel recalculates volatile functions during recalculation, even when their inputs have not directly changed. A large number of OFFSET formulas can therefore affect workbook calculation speed.

Can I use OFFSET to create a dynamic chart range?
Yes, a changing OFFSET reference can be used in a named range that supplies chart data. Set up the range carefully so it handles headers, blank rows, and new entries as intended. An Excel Table is often a more straightforward option for charts that should expand with added rows.

Is OFFSET available in Excel for the web and Mac?
OFFSET is available in both Excel for the web and Excel for Mac, as well as Microsoft 365 for Windows. The ribbon and interface can look different across platforms, but the function’s argument order and formula syntax are the same.

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.