Excel’s OFFSET function returns a reference to a cell or range that lies a specified number of rows and columns from a starting reference. Its basic syntax is =OFFSET(reference, rows, cols, [height], [width]); for example, it can point to a cell three rows below A1 or define a range whose size changes as data is added.
What OFFSET Returns
OFFSET is a reference function: it identifies a location or range rather than moving cells, copying data, or changing the worksheet. In a formula, Excel uses the reference it returns. If the result points to one cell, that cell’s value can appear in the formula cell; if it points to a range, another function such as SUM can operate on that range.
Think of the first argument as a starting point, then picture moving from it by a number of rows and columns. Positive row or column offsets move down or right; negative offsets move up or left; zero means stay in the same direction. The optional height and width set the size of the returned reference. If you omit them, OFFSET uses the height and width of the starting reference.
For example, with a worksheet where A1 contains a heading and B3 contains 18, the formula =OFFSET(A1,2,1) returns a reference to B3, so a cell containing this formula displays 18. From A1, moving down two rows reaches A3, and moving right one column reaches B3. This is a useful way to understand the arguments without confusing the starting cell with the destination.
Syntax and Arguments
The arguments have distinct jobs, and their order matters:
- reference: The starting cell or range. It is required.
- rows: How many rows to move from the top-left corner of the starting reference. It is required and can be positive, negative, or zero.
- cols: How many columns to move from the starting reference. It is required and can also be positive, negative, or zero.
- height: The number of rows in the returned reference. It is optional, but if supplied it must be positive.
- width: The number of columns in the returned reference. It is optional, but if supplied it must be positive.
The brackets in the syntax indicate optional arguments; do not type the brackets into a formula. In Microsoft 365 for Windows, formulas normally use commas between arguments when the system’s regional settings use commas as list separators. Some regional settings use semicolons instead, so Excel may adjust the separator expected on a particular computer.
Simple Examples with Cells and Ranges
Suppose A1 is the starting cell, with values in the cells around it. The following formula moves one row up and two columns to the right from A1, pointing to C0—which is outside the worksheet—so it produces an error. Instead, to move down one row and right two columns from A1 to C2, use =OFFSET(A1,1,2). The example illustrates why checking the destination is important, especially when using negative offsets near the top or left edge of a sheet.
To return a range instead of a single cell, specify height and width. With monthly sales values in B2:B5 of 12, 18, 9, and 21, this formula starts at B2 and returns a four-row, one-column range: =SUM(OFFSET(B2,0,0,4,1)). SUM adds the referenced cells, and the result is 60. The zero offsets keep the starting point at B2; the final arguments define the range’s dimensions.
OFFSET can also move to a different starting cell before returning a range. With values in B2:B5 and a formula elsewhere on the sheet, =SUM(OFFSET(B2,1,0,3,1)) sums B3:B5. The start is shifted down one row from B2, and the returned reference is three rows high and one column wide. This flexibility is handy when a formula must select a block relative to a known anchor.
Creating a Range That Grows with Data
A common reason to use OFFSET is to create a range that expands when new rows are added. One approach combines OFFSET with COUNTA, which counts nonempty cells. Assume B1 contains a heading, B2:B6 contain five uninterrupted entries, and the formula is placed outside column B. To sum the last three entries, use =SUM(OFFSET(B2,COUNTA(B:B)-4,0,3,1)). COUNTA returns 6 because it counts the heading and five entries; subtracting 4 moves the start from B2 to B4, and the three-row reference covers B4:B6. If those cells contain 9, 21, and 15, the result is 45.
This example depends on its layout: the heading must be in B1, the data must begin in B2 without blank cells, there must be at least three entries, and the formula must not be placed in column B. COUNTA counts text as well as numbers, so it is appropriate when each populated data row should count, but it may not fit a column with notes or gaps. If fewer than three entries are present, the calculated starting position may not represent the intended range. Test the formula against the smallest and largest expected data sets.
OFFSET is also used in a defined name to supply a changing range to a chart or another formula. In Excel for Microsoft 365 on Windows, open Formulas > Name Manager > New to create a name, then enter a reference formula in the Refers to box. For a heading in A1 and a continuous list beginning in A2, a name could use =OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A)-1,1). It returns the entries below the heading, with a height based on the number of nonempty cells in column A. Replace Sheet1 with the actual sheet name, and account for blanks or other content in that column before relying on the count.
When OFFSET Is Useful
OFFSET can make a formula adaptable when the desired range is defined by a position, a changing row count, or a repeated layout. Common uses include:
- Summing the latest few rows in a growing log.
- Building a defined name for a chart series that should include newly entered records.
- Selecting a range a fixed distance from a heading or another anchor cell.
- Referencing different sections of a consistently structured worksheet.
Its main benefit is positional flexibility: one formula can describe a destination relative to a starting point. Its main cost is that a reader may need to mentally calculate the offsets to see which cells are involved. For a simple fixed range, a direct reference is usually easier to understand. For a growing list, an Excel Table often provides a clearer way to include new records without calculating a range’s height manually.
Volatility and Calculation Performance
OFFSET is a volatile function in Excel. Volatile functions recalculate whenever Excel recalculates, even if their direct input cells have not changed. A small workbook may show no noticeable difference, but many OFFSET formulas across a large workbook can add calculation work and make recalculation slower. This is worth considering in models with many dependent formulas, extensive data, or frequent updates.
Volatility does not make OFFSET incorrect or unsafe; it is a trade-off. If the range changes position or size in a way that simpler references cannot express cleanly, OFFSET may be appropriate. If the range is fixed, use a direct cell or range reference. If it grows with a list, consider a Table or another nonvolatile construction. Keeping formulas understandable is also important: a formula that is technically dynamic can still be difficult to maintain if its assumptions are undocumented.
Common Errors and Misunderstandings
#REF! usually means the reference OFFSET tries to create extends beyond the worksheet grid. For example, moving above row 1 or left of column A cannot produce a valid cell reference. Check the starting cell and the sign and size of the row and column offsets, including any values calculated by other functions.
A second frequent problem is choosing the wrong height or width. Those arguments describe the dimensions of the result, not extra movement from the starting point. To return a range four rows tall, set height to 4; do not add four to the row offset. Also check whether the top-left cell of the returned range is the one you intended before trusting a total or chart.
Another misconception is that OFFSET automatically expands a chart or list just because it appears in a formula. The formula must be used in an appropriate place—for instance, in a defined name that the chart series refers to—and its counting logic must match the sheet’s layout. OFFSET does not insert rows, detect a table’s meaning, or repair gaps. It follows the references and dimensions its arguments specify.
Alternatives to OFFSET
For a fixed calculation, use an ordinary range reference; it is usually the clearest option. For data that grows as rows are added, convert the data range to an Excel Table using Insert > Table. Table formulas and chart references can include newly added table rows, while structured references make the source easier to recognize. This is often preferable for a regularly maintained list with headings.
INDEX can provide a nonvolatile way to construct a range when the start and end rows are known or can be calculated. With a heading in B1 and values in B2:B5, the formula =SUM(INDEX(B:B,2):INDEX(B:B,5)) sums B2:B5 and returns 60 when the values are 12, 18, 9, and 21. The colon joins the two endpoint references into a range. For a changing list, the endpoint logic still needs to match the data layout; INDEX does not automatically solve blank rows or inconsistent entries.
Choose based on the worksheet’s structure. A Table is often easiest for a growing dataset; a direct reference is easiest for a fixed block; INDEX can help construct a calculated range without OFFSET’s volatility. OFFSET remains useful when its relative-position behavior makes the formula simpler than the alternatives.
Frequently Asked Questions
Does OFFSET return a value or a reference?
OFFSET returns a reference to a cell or range. A formula using that reference may display a cell’s value or pass the range to another function, such as SUM. It does not move or copy the referenced cells.
Can OFFSET move left or up?
Yes. Use a negative number for rows to move up or a negative number for columns to move left. The destination must still remain inside the worksheet, or Excel returns #REF!.
Why does OFFSET make my workbook recalculate?
OFFSET is volatile, so Excel recalculates it whenever a recalculation occurs. If many such formulas are slowing a workbook, consider direct references, Tables, or INDEX-based references where they fit the same purpose.
Does OFFSET automatically include new rows?
No. It returns the range described by its arguments. A formula using COUNTA or a defined name can calculate a changing size, but that logic must fit the data layout. An Excel Table is often a simpler choice for an expanding list.
Can OFFSET return a multi-cell range?
Yes. Set height and width to the desired positive number of rows and columns. The returned range can then be used by a function or a defined name; check the start and dimensions to ensure they select the intended cells.
Related
- Freeze in Excel: Keep Rows and Columns Visible on Screen
- Freeze a Specific Row in Excel: Steps, Limits, and Tips
- Freeze Panes in Excel: Keep Rows and Columns Visible
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel COUNTIF for Numbers: Exact and Range MatchesYes. In Excel, use COUNTIF to count cells that match a number or a numeric condition: for example,…
- Excel ABS Function: How to Return a Positive ValueTo turn a negative number into a positive one in Excel, use the ABS function: it returns the…
- Excel Formula for Range of Values: Find the SpreadTo calculate the numerical range of values in Excel, subtract the smallest value from the largest: =MAX(A2:A7)-MIN(A2:A7). If…
- XLOOKUP Example With SUM in ExcelWith product names in A2:A5, monthly values in B2:D5, and the product to find in E2, use =SUM(XLOOKUP(E2,A2:A5,B2:D5)).…
- Match Excel Two Sheets: Formulas and MethodsYes. To match two Excel sheets, compare a shared key such as an ID or email address. If…
- XLOOKUP Excel Version: Compatibility and AvailabilityXLOOKUP is available in Microsoft 365, Excel 2021, and Excel 2024, as well as Excel for the web…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.