OFFSET function

OFFSET returns a reference to a range that is a specified number of rows and columns from a starting cell, optionally sized by height and width.

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

Generate a OFFSET formula

Describe what you need. The generator will reach for OFFSET where OFFSET is the right tool, and tell you when it is not.

How to get a better answer
  • Name your columns by letter and by header: "column F (Net Value)" beats "the amount column".
  • State every condition, including the negatives — "not cancelled" changes the formula's shape.
  • Say where the data starts if it is not row 1, and whether it will grow.
  • Check the settings above match your spreadsheet: the wrong argument separator is a syntax error on your machine.

Arguments

How OFFSET reads its arguments
referencerequiredrowsrequiredcolsrequiredheightoptionalwidthoptionalOFFSET →
ArgumentRequiredDescription
referenceRequiredA range (single cell or block) that serves as the anchor point; if it falls outside the sheet, #REF! is returned.
rowsRequiredA numeric offset indicating how many rows to move down (positive) or up (negative) from the reference; non-numeric values cause #VALUE!.
colsRequiredA numeric offset indicating how many columns to move right (positive) or left (negative) from the reference; non-numeric values cause #VALUE!.
heightOptionalOptional. The number of rows the returned range should span; must be a positive integer or omitted (defaults to reference height). Zero or negative triggers #NUM!.
widthOptionalOptional. The number of columns the returned range should span; must be a positive integer or omitted (defaults to reference width). Zero or negative triggers #NUM!.

Returns

A cell reference or range that can be used in further calculations.

Availability

Excel: All · Google Sheets: Supported

Worked examples

1. Get Gamma Inc's MRR using OFFSET from the header row

CustomerPlanMRRSignup DateChurn Date
Acme CorpPro12002023-01-15
Beta LLCBasic3002023-02-012024-03-10
Gamma IncEnterprise50002022-11-20
Delta CoPro12002023-05-052025-01-01
=OFFSET(Billing!$A$1,3,2)

Result: 5000

Starting at the top-left cell A1, OFFSET moves three rows down to row 4 (Gamma Inc) and two columns right to column C, which holds the MRR. The formula therefore returns the numeric value 5000.

2. Sum the MRR of the first three customers with a dynamic range

MRR
1200
300
5000
1200
=SUM(OFFSET(Billing!$C$2,0,0,3,1))

Result: 6500

OFFSET begins at C2 (the first MRR value) and expands three rows tall and one column wide, creating the range C2:C4. SUM then adds 1200 + 300 + 5000, yielding 6500.

3. Find the signup date of the first Pro plan using OFFSET and MATCH

PlanSignup Date
Pro2023-01-15
Basic2023-02-01
Enterprise2022-11-20
Pro2023-05-05
=OFFSET(Billing!$D$2, MATCH("Pro", Billing!$B$2:$B$6,0)-1, 0)

Result: 2023-01-15

MATCH locates the first occurrence of "Pro" in column B, returning 1. Subtracting 1 gives a row offset of 0, so OFFSET starts at D2 and moves 0 rows, returning the signup date in D2, which is 2023-01-15.

Common errors

Which OFFSET error are you seeing?
OFFSET returned an error#REF!
Reduce the row or column offset so the resulting cell stays inside the sheet.
#VALUE!
Replace the text with a numeric value or wrap the argument in VALUE() to coerce it to a number.
#NUM!
Specify a height and width of at least 1.
ErrorWhy it happensHow to fix it
#REF!OFFSET tries to move the reference beyond the worksheet limits, such as =OFFSET(Billing!$A$1,100,0).Reduce the row or column offset so the resulting cell stays inside the sheet.
#VALUE!A non-numeric argument is supplied, for example =OFFSET(Billing!$A$1,"two",0).Replace the text with a numeric value or wrap the argument in VALUE() to coerce it to a number.
#NUM!Height or width is set to zero or a negative number, like =OFFSET(Billing!$C$2,0,0,0,1).Specify a height and width of at least 1.

Tips and when to use something else

  • Use OFFSET to build ranges that shift when a lookup result changes, enabling rolling periods or dynamic dashboards.
  • Combine OFFSET with aggregation functions (SUM, AVERAGE, MIN, MAX) to calculate totals over moving windows.
  • OFFSET is a volatile function; it recalculates on every sheet change and can slow large workbooks—consider INDEX for static lookups.
  • When you only need a single cell by index, INDEX is usually faster and less volatile than OFFSET.

Related functions

INDEXINDEX returns the value or array of values from a specified position within a given range, based on row and optional column numbers.MATCHMATCH returns the relative position of a lookup value within a one-dimensional range, optionally using exact, less-than, or greater-than matching.INDIRECTINDIRECT returns the value or reference specified by a text string, letting you build dynamic cell or range references on the fly.ROWROW returns the row number of a given cell or range, or the row of the formula itself when no reference is supplied.COLUMNCOLUMN returns the column number of a given reference, or the column of the cell containing the formula when no reference is supplied.

Frequently asked questions

How can I use OFFSET to create a moving 12-month window of MRR?
Place the first month’s MRR in a column, then use =SUM(OFFSET(C2,ROW()-2,0,12,1)) copied down. ROW() generates a dynamic offset so each row sums the next 12 months.
Why does my OFFSET formula return a #SPILL! error?
#SPILL! appears when the range that OFFSET returns cannot expand because another cell blocks the spill area. Clear the obstructing cells or move the formula to a location with enough empty space.
Can OFFSET be used in Google Sheets the same way as Excel?
Yes, Google Sheets supports OFFSET with identical syntax, but the function is also volatile there, so large spreadsheets may experience similar performance impacts.
What is the difference between OFFSET and INDEX for dynamic range creation?
OFFSET returns a reference that can be sized by height and width, making it ideal for variable-sized ranges, while INDEX returns a single cell or array without the height/width arguments. INDEX is non-volatile and generally faster for fixed-size lookups.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator