Excel’s OFFSET function returns a reference to a cell or range a chosen number of rows and columns away from a starting reference. For example, =OFFSET(A1,2,1) points to cell B3; you can use the returned reference in a calculation such as SUM, or use it to build a range that changes size.
What OFFSET means in Excel
OFFSET is a reference function: it identifies a location on a worksheet rather than moving, inserting, or copying cells. Think of the first argument as a starting point, then imagine taking a specified number of steps down or up and right or left. The result is a reference to the destination cell or range.
The function is useful when a formula needs to refer to a location relative to another one. That can be a fixed offset, such as the cell two rows below a heading, or a range whose height depends on how much data is currently present. OFFSET has been available in Excel for many versions and works in Microsoft 365 for Windows. It is also available in Excel for Mac and Excel for the web.
A frequent misunderstanding is that OFFSET changes the worksheet layout. It does not move cells or alter their contents. It only supplies a reference for another part of a formula to use. If a formula asks OFFSET for a single cell, Excel may display that cell’s value; if it returns a range, a function such as SUM can work with the whole range.
Arguments and direction of movement
OFFSET takes a starting reference, a row displacement, and a column displacement, in that order. It can also take an optional height and width to specify the size of the returned range. Positive row numbers move downward and negative row numbers move upward. Positive column numbers move right, while negative numbers move left. A displacement of zero means stay in the same row or column.
- Reference: The cell or range from which to start. For a multi-cell reference, Excel uses its upper-left cell as the starting point.
- Rows: How far to move vertically from that starting point.
- Columns: How far to move horizontally.
- Height: Optional number of rows in the returned range.
- Width: Optional number of columns in the returned range.
If height and width are omitted, the result keeps the size of the original reference. So starting with a two-row by three-column reference and omitting those optional arguments returns another two-by-three range at the offset location. When you specify height and width, those dimensions describe the new range beginning at the shifted location. They must be positive sizes.
A simple cell-reference example
Suppose A1 is the starting cell. With that layout, =OFFSET(A1,2,1) refers to B3: move two rows down from row 1 and one column right from column A. If B3 contains the word Complete, entering the formula in an otherwise empty cell displays Complete. The formula does not put the word into B3 or change A1.
Negative offsets work the same way in reverse. If D5 is the starting reference, a row displacement of -2 and a column displacement of -1 point to C3. A requested destination must remain inside the worksheet grid; moving beyond Excel’s last row or column produces a #REF! error.
Returning a range for a calculation
OFFSET becomes especially useful when a function can consume the range it returns. Assume the values in B2:B6 are 10, 12, 8, 15, and 5, respectively. With those values in that range, =SUM(OFFSET(B2,0,0,5,1)) sums B2:B6 and returns 50. The row and column displacements are zero, and the final arguments specify a range five rows tall and one column wide.
You can use the same pattern with other functions that accept ranges. For example, OFFSET can provide the input range for AVERAGE, COUNT, or MAX. The function that surrounds OFFSET determines what Excel does with the referenced cells; OFFSET itself only identifies them. This distinction matters when troubleshooting: a valid reference can still lead to an unexpected calculation if the surrounding function or source data is not what you intended.
Building a range that adjusts to a list
A common use is summing the most recent few entries in a contiguous list. Assume B2:B7 contains six numeric values with no blank cells, and you want the last three. With that exact layout, =SUM(OFFSET(B2,COUNTA(B2:B7)-3,0,3,1)) returns the sum of B5:B7. COUNTA finds six populated cells; subtracting three moves the starting point down three rows from B2, and the requested range is three rows tall. If B5:B7 contain 15, 5, and 20, the result is 40.
This example depends on its assumptions. The list must be contiguous, and it must contain at least three entries. A blank in the middle can make the count position differ from the actual last row, while fewer than three populated cells can make the calculated reference wrong or invalid. If records may contain gaps, or if you need a more robust way to identify the last row, consider an Excel Table or another approach based on your data structure rather than copying this formula unchanged.
When OFFSET recalculates and why it matters
OFFSET is a volatile function. Excel recalculates volatile formulas whenever a calculation occurs, even if a change elsewhere in the workbook does not appear to affect their references. In a small sheet, that is often unnoticeable. In a large workbook with many volatile formulas, repeated recalculation can add work and may contribute to slower performance.
Volatility is not the same as an error, and it does not mean the formula recalculates continuously while the workbook is idle. It means the formula is included in calculation cycles more readily than a nonvolatile function. If a workbook feels slow, the number of OFFSET formulas is one factor to inspect alongside other formulas, data size, and calculation settings. Avoid replacing a clear, useful formula solely because it is volatile; weigh the performance needs of the workbook against the value of a reference that adjusts automatically.
Common errors and checks
The most direct OFFSET error is #REF!, which means the requested reference falls outside the worksheet. Check the starting cell and the signs and sizes of the row and column displacements. Also check the requested height and width: a range beginning near the worksheet edge can run out of available rows or columns if it is too large.
- Wrong direction: Remember that positive rows go down and positive columns go right.
- Unexpected range size: If height and width are omitted, OFFSET preserves the starting reference’s dimensions, which may be larger than one cell.
- Blanks in a counted list: COUNTA counts nonempty cells, not the physical distance from the first entry to the last.
- Unintended result from a range: Check whether the surrounding formula expects a single value or can use multiple cells.
- Slow recalculation: Review how many volatile formulas the workbook contains before assuming one OFFSET formula is the cause.
OFFSET compared with other ways to reference data
OFFSET is a natural fit when the desired location is defined by a distance from a known starting point or when the range size is calculated. However, it is not the only way to create adaptable formulas. INDEX can often return a cell reference without OFFSET’s volatility, and it may be a better choice in a large workbook where recalculation efficiency matters. The formulas are not interchangeable in every situation, so the right replacement depends on how the reference is determined.
For growing lists, an Excel Table is often simpler to maintain. To create one in Excel for Microsoft 365 on Windows, select the data and choose Insert > Table, or press Ctrl+T. Tables expand as you add rows and offer structured references based on column names, which can make formulas easier to read. On Mac, the Table command is also available from the Insert tab; keyboard shortcuts can differ by keyboard and Excel settings. A table is especially helpful when the range represents records with headings, rather than an arbitrary block of cells.
OFFSET may still be the clearest tool when the relationship really is positional—for example, a range a fixed number of rows below a starting cell. Choose based on clarity, workbook size, and how the source data grows. A formula that is easy for another person to audit is often preferable to a compact but opaque reference trick.
Frequently Asked Questions
Does OFFSET return a value or a reference?
OFFSET returns a reference. If the reference is to one cell, a formula using it may show that cell’s value. When it returns multiple cells, another function such as SUM can calculate over the referenced range.
Can OFFSET move cells in a worksheet?
No. It only points to a cell or range for a formula to use. To move cells, use Excel’s cut-and-paste or other editing commands; OFFSET does not change the worksheet contents.
Why does OFFSET give me a #REF! error?
The requested reference may extend beyond the worksheet’s row or column limits. Check the starting reference, the direction and size of the offsets, and the returned range’s height and width.
Is OFFSET volatile in Excel?
Yes. Excel recalculates OFFSET during calculation cycles, even when a change is not directly related to the cells it references. A few uses are usually manageable, but many volatile formulas can add recalculation work in a large workbook.
What can I use instead of OFFSET?
INDEX can often create a reference without OFFSET’s volatility, while Excel Tables can automatically extend formulas as records are added. Which option fits best depends on whether your range is defined by position, a changing list, or a structured set of records.
Related
- Excel vs CSV Row Limits: How Many Rows Can Each Hold?
- Excel Vendor List Template: Build and Manage Your Records
- Excel Data Validation from a List: Setup and Troubleshooting
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel MATCH Errors: Causes, Fixes, and Formula ExamplesThe most common Excel MATCH error is #N/A: MATCH cannot find the lookup value in the specified range.…
- Excel Wildcard XLOOKUP: Find Partial MatchesYes. In Microsoft Excel for Microsoft 365, XLOOKUP can find a partial text match when you set its…
- HLOOKUP in Excel: Purpose, Examples, and LimitsHLOOKUP in Excel searches for a value across the top row of a table and returns a related…
- Excel Midpoint of Dates: Formula and ExamplesTo find the midpoint between two dates in Excel, put the earlier date in A2 and the later…
- Excel Filter Shortcut: How to Turn Filters On and OffIn Excel for Microsoft 365 on Windows, press Ctrl+Shift+L to turn AutoFilter on or off. Select a cell…
- Excel Filter Dates by Week: Quick MethodsTo filter dates for the current or a nearby week in Excel for Microsoft 365 on Windows, select…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.