OFFSET in Excel: Syntax, Examples, and Uses

Formulas & functions · Updated November 18, 2026

In Excel, OFFSET returns a reference to a cell or range located a specified number of rows and columns from a starting reference. For example, if B2 contains 10 and B4 contains 30, =OFFSET(B2,2,0) refers to B4 and returns 30 when entered as a worksheet formula.

What OFFSET Does in Excel

OFFSET is a reference function: it identifies a destination relative to a starting cell or range. It does not move, copy, or change any worksheet data. Think of it as describing a position: start here, move a number of rows and columns, then optionally specify how tall and wide the resulting reference should be.

The function is useful when a formula needs to refer to a range whose position or size may change. For instance, you can use it to total the latest several values in a list, or to define a range that grows as new records are added. It can also be used inside other functions, such as SUM, that accept a reference.

A key distinction is that OFFSET itself returns a reference, not a calculation such as a total or average. In a worksheet cell, a one-cell reference will usually appear as that cell’s value. To calculate something from a multi-cell reference, wrap it in a suitable function such as SUM or AVERAGE.

OFFSET Syntax and Arguments

The syntax is OFFSET(reference, rows, cols, [height], [width]). The first three arguments are required; height and width are optional. The arguments mean:

  • reference: The starting cell or range. It must be a reference to a cell or a contiguous range.
  • rows: How many rows to move from the starting reference. A positive number moves down; a negative number moves up; zero stays on the same row.
  • cols: How many columns to move. A positive number moves right; a negative number moves left; zero stays in the same column.
  • height: The number of rows in the returned reference. It must be positive. If omitted, the returned reference has the same height as the starting reference.
  • width: The number of columns in the returned reference. It must be positive. If omitted, the returned reference has the same width as the starting reference.

Rows and columns describe movement from the reference, not an absolute worksheet address. If the starting reference is B2, moving down two rows and right one column lands at C4. If you request a destination or range that extends beyond the worksheet boundary, Excel returns a #REF! error.

A Basic OFFSET Example

Suppose B2:B5 contains 10, 20, 30, and 40, respectively. With those values in place, this formula uses B2 as its starting point, moves down two rows, and stays in the same column:

With the values in B2:B5 as described above, enter: =OFFSET(B2,2,0). It refers to B4, so the displayed result is 30.

Negative movements work the same way in the opposite direction. With a value of 25 in D3, this formula starts at C5, moves up two rows, and moves one column right:

With C5 as the starting cell and D3 containing 25, enter: =OFFSET(C5,-2,1). The destination is D3, so the displayed result is 25.

You can also make the returned reference larger than one cell by setting height and width. For example, if B2:B6 contains 10, 20, 30, 40, and 50, the formula below refers to five cells starting at B2. SUM then adds the values in that reference.

With the five example values in B2:B6, enter: =SUM(OFFSET(B2,0,0,5,1)). It adds 10 + 20 + 30 + 40 + 50 and returns 150.

Creating a Range That Grows with Your Data

OFFSET can help define a range whose height depends on how many entries are present. This can be useful for a running total when new numeric values are added to a continuous list. Consider a worksheet with a heading in B1 and numeric entries in B2:B5: 10, 20, 30, and 40. Place the total formula somewhere outside column B, and keep the list contiguous with no blank rows.

With the heading in B1, the four numeric entries in B2:B5, and no other nonblank cells in column B, enter: =SUM(OFFSET(B2,0,0,COUNTA(B:B)-1,1)). COUNTA(B:B) counts the heading and four entries, giving 5; subtracting 1 gives a range height of 4. The result is 100. When another value is added directly below the list, the referenced range expands to include it.

This construction depends on the stated layout. A stray note elsewhere in column B, or a blank row inside the list, can make the count produce an unintended range. In a real workbook, Excel Tables are often a more dependable way to manage expanding data: add a row to the table and its structured references can expand automatically without calculating the range height with OFFSET.

Common Uses and Practical Examples

OFFSET is most useful when the reference must be calculated from a starting point. Common applications include:

  • Rolling summaries: Sum or average a specified number of the most recent entries in a regularly maintained list. The formula needs a reliable way to identify the latest row and should account for what happens before enough entries exist.
  • Variable-size ranges: Return a range with a height or width determined by another calculation, such as a count of records.
  • Neighboring values: Refer to a cell a fixed number of rows or columns from a known location, for example, a value two rows below a selected starting cell.
  • Charts and defined names: In some workbooks, a name based on OFFSET is used as a chart data source that adjusts as data is added. These setups need careful maintenance, and tables or newer dynamic-array tools may be easier to understand.

Before using OFFSET, decide what should happen when the source list is empty, shorter than the requested range, or contains gaps. A formula that works for a full list may return an error or include unintended cells in those cases. Making those assumptions explicit is more reliable than simply adding OFFSET to a formula and expecting every layout to behave as intended.

Common Errors and Performance Considerations

#REF! is the most direct error: it generally means the requested reference extends beyond the worksheet’s available rows or columns, or that another part of the formula has produced an invalid reference. Check the starting cell, row and column offsets, and requested height and width.

Another common problem is an invalid range size. Height and width must be positive when supplied; zero or negative values can cause an error. If either optional argument is omitted, Excel uses the corresponding dimension of the starting reference. For example, omitting both dimensions when the starting reference is a three-row, two-column block keeps that block’s size.

OFFSET is a volatile function. That means Excel recalculates it whenever a recalculation occurs, even if its direct inputs have not changed. A few OFFSET formulas are usually unremarkable, but large workbooks with many volatile formulas can recalculate more often and feel slower. If performance becomes an issue, look at how many volatile formulas the workbook uses and whether a nonvolatile alternative can express the same logic.

Also check whether your formula counts the intended cells. COUNTA counts nonempty cells, including text, while COUNT counts numeric values. Neither automatically detects the last meaningful record in every possible layout. Headers, notes, formulas returning an empty string, and gaps can change what a counting approach includes.

OFFSET Compared with Other Excel Options

INDEX is a common alternative when you need a reference at a particular position in a range. It is generally nonvolatile, so it may be preferable in a large workbook when it can express the required reference clearly. Unlike OFFSET’s row-and-column movement from a starting point, INDEX identifies a position within a supplied range.

For lists that expand over time, an Excel Table is often simpler. Select the data and use Insert > Table on Windows, then use the table’s column names in formulas. When records are added to the table, structured references typically adjust to include them. Tables are especially useful for ordinary lists with headers and consistent columns.

Microsoft 365 and Excel 2021 also include dynamic-array functions such as FILTER, SORT, and TAKE (TAKE requires Microsoft 365 or Excel 2024). These can make some tasks involving changing lists clearer, although they do not replace OFFSET in every workbook design. Choose based on the job: use OFFSET when relative reference movement is genuinely helpful, and prefer a table, INDEX, or an appropriate newer function when that gives a simpler and more maintainable formula.

The OFFSET function is available in Microsoft Excel for Microsoft 365 on Windows. Its formula syntax is the same in current Excel for Mac; keyboard shortcuts and some ribbon details can differ, but the examples above do not depend on a platform-specific command.

Frequently Asked Questions

What does OFFSET mean in Excel?
It means return a reference a specified number of rows and columns away from a starting cell or range, optionally with a chosen height and width. It does not physically shift worksheet cells.

Does OFFSET return a value or a reference?
OFFSET returns a reference. When that reference is one cell, Excel displays the cell’s value in a worksheet formula. To calculate across a multi-cell reference, use a function such as SUM or AVERAGE.

Why does my OFFSET formula return #REF!?
Check whether the requested position or range extends beyond the worksheet, and confirm that any supplied height and width are positive. Also inspect the starting reference and the calculations used for the row and column arguments.

Is OFFSET volatile in Excel?
Yes. Excel recalculates OFFSET when a recalculation occurs, even when its direct inputs have not changed. In a large workbook with many volatile formulas, consider whether a table or a nonvolatile reference method would be more efficient.

Can OFFSET create a dynamic range?
Yes. Its height or width can be determined by another calculation, allowing the returned reference to grow or shrink. The result depends on a sound counting method and a consistent data layout; an Excel Table is often easier for a growing list.

Is OFFSET available in Excel for Mac?
Yes. OFFSET is supported in current Excel for Mac, and the formula syntax is the same. Interface locations and shortcuts can differ between Mac and Windows, but they do not change how the function’s arguments work.

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.