Excel OFFSET Function: How to Use It in Formulas and Ranges

In Excel for Microsoft 365 on Windows, OFFSET returns a reference moved a chosen number of rows and columns from a starting cell or range. If B2 is the starting cell, for example, =OFFSET(B2,2,1) refers to D4: two rows down and one column right. Use OFFSET inside functions such as SUM to calculate from the returned cells, or to create a range that changes size. You will need a workbook with a clear starting point and, for dynamic ranges, consistently arranged data.

What You’ll Need

Excel for Microsoft 365 on Windows and a workbook where you can enter and check formulas. The OFFSET function is also available in other current desktop versions of Excel; the formula syntax is the same in Excel for Mac and Excel for the web.
A starting cell or range, plus a decision about how far the result should move down or up and right or left.
If you want a variable-height range, a reliable way to count its records, such as a filled column with no blank cells between records.

Step-by-Step

Choose the starting reference. Decide which cell or range is the anchor. OFFSET does not start at the active cell, nor does it move the selection or scroll the worksheet. It calculates a reference based on the reference you put in the formula. For a list of monthly figures in B2:B7, B2 could be the anchor for a range that begins at the first figure.
Work out the row and column movement. Count rows and columns from the anchor, not from the worksheet’s top-left corner. A positive row number moves down; a negative number moves up. A positive column number moves right; a negative number moves left. Zero means stay in the same row or column. With B2 as the starting cell, the formula =OFFSET(B2,2,1) refers to D4. The arguments move two rows down to row 4 and one column right from B to C—wait: one column right from B is C, so the result is C4.
Enter the basic formula and check the destination. Click a result cell, type an equals sign and OFFSET, and enter the starting reference, row movement, and column movement, separated by commas. For example, with B2 as the start, =OFFSET(B2,2,1) refers to C4. Press Enter, then verify the location by checking the row and column arithmetic. When a formula returns a single-cell reference, Excel can display the value from that cell in the formula cell. To see a useful result, you can wrap the reference in another function or inspect the formula’s inputs.
Set the size when you need a range. OFFSET can return a single cell or a multi-cell range. The optional height and width arguments specify the number of rows and columns in the returned range. Both must be positive numbers. If either is omitted, OFFSET uses the corresponding size of the starting reference. For a starting cell, omitting both means a one-cell reference. For a range, the default result keeps that range’s height and width.
Use the reference in a calculation. Suppose B2:B7 contains the values 120, 150, 130, 170, 160, and 190, with no heading inside the range. With B2 as the anchor, the formula =SUM(OFFSET(B2,1,0,3,1)) refers to B3:B5 and sums 150, 130, and 170, returning 450. Here, the range starts one row below B2, stays in column B, and is three rows tall by one column wide. SUM uses the reference OFFSET returns.
Build a rolling calculation if the list is continuous. To sum the latest three entries in B2:B7, use COUNTA to find how many cells are filled and use that count to set the starting position. With six numeric entries in B2:B7 and no blanks, =SUM(OFFSET(B2,COUNTA(B2:B7)-3,0,3,1)) refers to B5:B7 and returns 520 (170 + 160 + 190). This calculation assumes there are at least three entries and that every cell in the range represents one record. If there are fewer than three, the height and starting position can become invalid; use an IF check or wait until the data has enough rows.
Create a variable-height named range, if appropriate. For a list with a heading in A1 and uninterrupted entries beginning in A2, open Formulas > Name Manager > New. Enter a clear name such as SalesList, then enter the following in Refers to: =OFFSET($A$2,0,0,COUNTA($A:$A)-1,1). This counts nonblank cells in column A, subtracts the heading, and defines a range of that many rows starting at A2. The definition assumes A1 is the only heading in that column, all records are populated, and there are no gaps. If the list is empty, the calculated height is zero, which is not a valid OFFSET height.
Use and verify the named range. After creating SalesList, use it in a formula wherever a range is accepted. With the named range defined as above and numeric sales values in A2 downward, =SUM(SalesList) adds the currently included values. Add a record directly below the list and check that it is included; also test that a blank row or unrelated text in column A does not distort the count. If records may contain gaps, a formatted Excel Table is usually a more dependable solution.
Save and recalculate the workbook. Check that the displayed result agrees with the cells you intended to reference. OFFSET is volatile, meaning Excel recalculates it when recalculation occurs, even if its direct inputs have not changed. A small number of uses is normally straightforward, but a large number of OFFSET formulas in a complex workbook can affect calculation speed.

Common Mistakes

Counting from the wrong place: The row and column arguments describe movement from the reference, not an absolute worksheet address. If the anchor is B2, a movement of two rows and one column lands at C4, not D4. Write down the destination and count each movement before entering a more complicated formula.

Expecting OFFSET to move the cursor: OFFSET returns a reference for a formula to use. It does not select the destination cell, move the viewport, or copy data there. If you need to navigate to a cell, use the Name Box or Go To instead.

Leaving out a required size: Height and width are optional, but you need to provide them if you want a result larger or differently shaped than the starting reference. A formula can be syntactically valid and still return only one cell when you expected a block. Specify both dimensions when clarity matters.

Using zero or negative dimensions: The height and width of the returned reference must be positive. Negative row or column movement is allowed, but a negative height or width is not. A formula that calculates a height of zero, such as a dynamic range based on an empty list, can return a reference error.

Counting a column with blanks or unrelated values: COUNTA counts any nonempty cell, not just the intended records. A note, stray space, or extra heading in the counted column can make a dynamic range too tall; a blank within a list can make the range include an empty row. Keep the count column clean or choose another method.

Making an oversized OFFSET calculation: Because OFFSET is volatile, using it throughout a large workbook may cause more recalculation than necessary. For a straightforward expanding list, an Excel Table often adjusts automatically and is easier to read. Use OFFSET when its reference-shifting behavior genuinely helps, not just because it is available.

Tips

Build a complex reference in stages. First test the starting point and movement, then add height and width, and finally wrap the result in SUM, AVERAGE, or another function. This makes it easier to identify which argument is wrong. Keep the anchor fixed with dollar signs, such as $B$2, if you plan to copy a formula and want the starting point to remain unchanged; use relative references when you want the anchor to shift as the formula is copied.

Use labels and comments to document what a dynamic range counts. In the rolling three-entry example, the range must have at least three values and must not contain gaps. For a rolling window of a different length, change both the offset calculation and the requested height consistently. Test the formula with a short list, the expected full list, and one additional record before relying on it in a report.

Prefer a Table when rows are regularly added to a dataset. Select the data and choose Insert > Table, then use the Table’s column references in formulas. Tables expand as records are added and are usually easier for colleagues to maintain than a named range built with OFFSET. In Excel for Mac and Excel for the web, the formula itself works the same way; ribbon placement or wording can vary by platform, but the underlying OFFSET arguments do not change.

For troubleshooting, temporarily replace a long calculation with a simple OFFSET test in an unused cell, then verify its destination before restoring the full formula. You can also select part of a formula in the formula bar and press F9 to inspect the calculated part in Windows; press Esc afterward to avoid replacing the formula with the previewed value. On Mac, the function key may require the Fn key depending on keyboard settings.

Troubleshooting

You see #REF!: Check whether the requested reference would extend beyond the worksheet boundary. For instance, moving left from column A or above row 1 cannot produce a valid cell. Also check that calculated height and width are greater than zero and that the starting reference is valid.

The result is not the cell you expected: Recount from the anchor, including the sign of each movement. Confirm that positive rows mean down and positive columns mean right. Check for an accidental row or column insertion that changed a relative reference. Use absolute references for an anchor that must stay fixed when copying.

The formula returns a value instead of showing a range: A formula entered in one cell may display the value from a single-cell reference. To calculate over multiple cells, place OFFSET inside a function such as SUM or AVERAGE. A multi-cell reference on its own is not a display instruction to highlight or select those cells.

A dynamic name includes too many or too few cells: Inspect the column used by COUNTA for blanks, notes, extra headings, and stray entries. Confirm that the count includes the intended records only, then check the subtraction for any heading. If the source data is irregular or has multiple columns of metadata, use a Table or a more explicit range definition.

Excel reports a formula error: Check that every argument is separated by a comma, that parentheses are balanced, and that the reference uses a valid cell or range. Excel installations configured for some regional settings may use semicolons instead of commas as formula separators. If so, use the separator expected by that installation; the examples here use standard comma separators.

The workbook recalculates slowly: Look for many OFFSET formulas, especially formulas that use full-column counts across multiple sheets. Replace simple dynamic lists with Tables where practical, and limit repeated calculations by storing an intermediate result where that is appropriate. Avoid changing calculation settings globally unless you understand how manual calculation affects all formulas in the workbook.

Frequently Asked Questions

What does the OFFSET function do in Excel?
It returns a cell or range reference at a specified row and column distance from a starting reference. Other functions can use that returned reference to calculate a result.

Does OFFSET return a value or a reference?
OFFSET returns a reference. When the reference is to one cell and the formula is entered on its own, Excel may display that cell’s value, which can make the distinction less obvious. Wrap OFFSET in a function when you want a calculation over the returned range.

Can OFFSET move up or to the left?
Yes. Use a negative row argument to move up and a negative column argument to move left. The destination must still be inside the worksheet.

Why does OFFSET return #REF! in a dynamic range?
A calculated destination may be outside the worksheet, or the calculated height or width may be zero or negative. Check the count and minimum number of records as well as the anchor position.

Is OFFSET available in Excel for Mac and Excel for the web?
Yes. The formula syntax and behavior are the same in current Excel for Mac and Excel for the web. The ribbon layout can differ, particularly for named-range management.

Should I use OFFSET or an Excel Table?
Use a Table for ordinary datasets that grow as you add rows; it is often easier to maintain and avoids relying on volatile OFFSET calculations. Choose OFFSET when you specifically need a reference positioned or sized by row and column calculations.

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.