Lookup & Reference

OFFSET Function in Excel

Returns a reference offset from a starting point by a specified number of rows and columns.

Syntax

  • =OFFSET(reference, rows, cols, [height], [width])

Arguments

  • reference (required): The starting point reference
  • rows (required): Number of rows to offset (positive=down, negative=up)
  • cols (required): Number of columns to offset (positive=right, negative=left)
  • height (optional): Height of returned reference in rows
  • width (optional): Width of returned reference in columns

Examples

  • =OFFSET(A1, 2, 3) - Reference cell 2 rows down, 3 columns right - Result: Value at D3
  • =SUM(OFFSET(A1, 0, 0, 5, 1)) - Sum 5 cells starting from A1 - Result: Sum of A1:A5
  • =OFFSET(A1, COUNTA(A:A)-1, 0) - Get last non-empty cell in column A - Result: Last value

Excel OFFSET function documentation

  • OFFSET returns a reference shifted by a number of rows and columns from a starting cell — not the value itself unless wrapped.
  • Syntax: =OFFSET(reference, rows, cols, [height], [width]).
  • Common in legacy dynamic ranges, rolling 12-month windows, and dashboard templates.
  • Modern alternatives: [INDEX](/functions/index/), [FILTER](/functions/filter/), Excel Tables, and dynamic arrays.

OFFSET syntax and arguments

  • reference (required): starting cell — often A1 or the top-left of a data block.
  • rows (required): rows to move down (positive) or up (negative).
  • cols (required): columns to move right (positive) or left (negative).
  • height (optional): height in rows of the returned reference.
  • width (optional): width in columns of the returned reference.

Step-by-step: rolling 12-month range with OFFSET

  • Step 1 — Store monthly data in columns B onward with headers in row 1.
  • Step 2 — Put "months to include" (12) in a settings cell F1.
  • Step 3 — Dynamic width: =OFFSET($B$1, 0, COUNTA($B$1:$1)-F1, 1, F1) — adjust to your layout.
  • Step 4 — Pair with SUM: =SUM(OFFSET($B$2,0,0,1,12)) for last 12 columns from B2.
  • Step 5 — Test by adding a month column and confirming the range expands as intended.

OFFSET vs INDEX vs dynamic arrays

  • OFFSET — volatile; shifts from anchor; harder to audit in large models.
  • INDEX — returns values from fixed coordinates; non-volatile; clearer for most audits.
  • FILTER/SORT — M365 dynamic arrays replace many OFFSET patterns for live subsets.
  • Prefer named Excel Tables with structured references over OFFSET in new workbooks.

Worked examples to copy

  • Value one cell right: =OFFSET(A1, 0, 1) references B1.
  • Sum dynamic block: =SUM(OFFSET(A1, 1, 0, 10, 3)) sums 10×3 block below A1.
  • Last non-empty row (legacy): complex OFFSET+MATCH patterns — consider Tables instead.
  • Chart source: =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),5) for growing height.

People also ask

  • Why is OFFSET volatile? — Excel treats the shifted reference as dependent on calculation cycles.
  • OFFSET #REF! error? — Shift moves outside worksheet bounds.
  • OFFSET vs INDIRECT? — OFFSET shifts numerically; INDIRECT parses text addresses.
  • Should I avoid OFFSET? — Not always; but INDEX/FILTER are often clearer in modern Excel.

Common errors

  • #REF! when offset goes outside worksheet
  • #VALUE! with non-numeric offset values

Use cases

  • Dynamic ranges
  • Moving averages
  • Last N values calculations

Frequently asked questions

  • What is OFFSET used for in Excel? OFFSET creates a reference that is offset from a starting cell by a specified number of rows and columns. It's commonly used to create dynamic ranges that expand or contract based on data, calculate moving averages, and reference the last N values in a column.
  • How do I create a dynamic range with OFFSET? Use =OFFSET(start, 0, 0, COUNTA(column), 1) to create a range that grows with your data. Example: =SUM(OFFSET(A1, 0, 0, COUNTA(A:A), 1)) sums all non-empty cells in column A.
  • Why is OFFSET volatile and does it matter? OFFSET recalculates every time Excel recalculates, which can slow large workbooks. For better performance, use INDEX instead: =INDEX(A:A, 1):INDEX(A:A, COUNTA(A:A)) creates a similar dynamic range without volatility.
  • How do I get the last value in a column with OFFSET? Use =OFFSET(A1, COUNTA(A:A)-1, 0) to get the last non-empty cell. This offsets from A1 by the count of non-empty cells minus 1 (since we start at row 1).
  • Can OFFSET return multiple cells? Yes! Use the height and width parameters. =OFFSET(A1, 0, 0, 3, 2) returns a 3-row by 2-column range starting at A1. Use with functions like SUM, AVERAGE, or in array formulas.

Editorial review

  • Reviewed by Excel.Directory Editorial Team. Updated May 2026.

When to use OFFSET

  • Dynamic ranges — common Lookup & Reference scenario for OFFSET.
  • Moving averages — common Lookup & Reference scenario for OFFSET.
  • Last N values calculations — common Lookup & Reference scenario for OFFSET.

OFFSET in the Lookup & Reference category

  • Browse all Lookup & Reference functions at /categories/lookup-reference/ for related formulas.
  • OFFSET syntax: =OFFSET(reference, rows, cols, [height], [width])
  • reference (required): The starting point reference
  • rows (required): Number of rows to offset (positive=down, negative=up)
  • cols (required): Number of columns to offset (positive=right, negative=left)
  • height (optional): Height of returned reference in rows
  • Confirm OFFSET arguments match the syntax shown above before filling down.
  • Lock table and range references with $ when copying formulas across rows or sheets.

Formula checklist before you copy down

  • Confirm OFFSET arguments match the syntax shown above before filling down.
  • Lock table and range references with $ when copying formulas across rows or sheets.
  • If results look wrong, check for text stored as numbers and invisible spaces with TRIM.
  • Spot-check three known input rows manually against expected output.

Related Excel functions

  • INDEX (/functions/index/): Returns the value of an element in a table or array, selected by the row and column number indexes.
  • INDIRECT (/functions/indirect/): Returns the reference specified by a text string. References are immediately evaluated to display their contents.
  • ADDRESS (/functions/address/): Creates a cell reference as text from row and column numbers.
  • MATCH (/functions/match/): Returns the relative position of an item in an array that matches a specified value.

Errors to watch for

  • #REF! when offset goes outside worksheet — review causes on linked error pages in the directory.
  • #VALUE! with non-numeric offset values — review causes on linked error pages in the directory.

Copy-paste audit workflow

  • Enter OFFSET on three test rows with known expected output documented on a QA tab.
  • Fill down only after absolute references are locked on lookup tables and rate tables.
  • Compare against manual calculation or a calculator for financial and statistical functions.
  • Search this directory for comparison guides when choosing between similar functions in the same category.

OFFSET worked examples to copy

  • =OFFSET(A1, 2, 3) — Reference cell 2 rows down, 3 columns right. Expected result: Value at D3.
  • =SUM(OFFSET(A1, 0, 0, 5, 1)) — Sum 5 cells starting from A1. Expected result: Sum of A1:A5.
  • =OFFSET(A1, COUNTA(A:A)-1, 0) — Get last non-empty cell in column A. Expected result: Last value.

OFFSET reference summary for crawlers and offline review

  • OFFSET belongs to the Lookup & Reference category in Excel. Returns a reference offset from a starting point by a specified number of rows and columns.
  • Full syntax: =OFFSET(reference, rows, cols, [height], [width]). Open /functions/offset/ for parameters, FAQs, and related pages.
  • Common mistakes: #REF! when offset goes outside worksheet; #VALUE! with non-numeric offset values
  • Pair this function with comparison guides when another Excel formula might fit the same task better.
  • Review fix-excel-formula-errors when unexpected errors appear after upgrading Excel or sharing across locales.