Quick Answer

OFFSET Function

Returns a range shifted by a given number of rows and columns from a starting point.

Syntax

OFFSET

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

Parameters

Parameter	Description	Required
reference	The starting cell or range.	Required
rows	Number of rows to shift down (positive) or up (negative).	Required
cols	Number of columns to shift right (positive) or left (negative).	Required
height	Height of the returned range in rows (optional).	Optional
width	Width of the returned range in columns (optional).	Optional

Basic Example

Return the value one row below A1

=OFFSET(A1, 1, 0)

ResultValue in A2

Starts at A1 and shifts down 1 row, returning A2.

Advanced Examples

Example 1: Dynamic range

Rolling average

Create a range that grows as data is added

=AVERAGE(OFFSET(A1, 0, 0, COUNTA(A:A), 1))

Result: Average of all populated cells in column A

COUNTA determines the height of the range dynamically.

Example 2: 2D offset

Matrix lookup

Return a 3x2 range starting 2 rows down and 1 column right from A1

=SUM(OFFSET(A1, 2, 1, 3, 2))

Result: Sum of range B3:C5

OFFSET returns a 3-row by 2-column range starting at B3.

How OFFSET Works

OFFSET starts at the reference and moves the specified number of rows and columns. It then optionally expands the result to a range of the given height and width. It returns a reference, not a value, so it is typically used inside another function.

1

Choose starting cell

Select the anchor cell or range.

2

Specify row offset

Enter how many rows to move down (positive) or up (negative).

3

Specify column offset

Enter how many columns to move right (positive) or left (negative).

4

Optional size

Set height and width to return a range larger than one cell.

Important Notes & Limitations

Volatile: recalculates on every workbook change, which can slow performance.
Can be hard to debug because the returned range is not visible.
Does not work across closed workbooks.
Using it with entire-column references can cause performance issues.

Common Errors & Fixes

#REF! errorThe offset points outside the worksheet boundaries.

Fix: Reduce the row or column offset values.

Performance issuesOFFSET is volatile and used in many cells.

Fix: Use INDEX or structured references instead where possible.

Wrong range sizeHeight or width parameters are incorrect.

Fix: Double-check the height and width values.

Download Practice File

Practice OFFSET with Real Data

Download a sample CSV file with pre-populated data and practice exercises for the OFFSET function. Works in both Excel and Google Sheets.

Works in Google SheetsCompatible with ExcelIncludes exercises

File format: CSV (comma-separated values) - opens in Excel, Google Sheets, and all spreadsheet apps

Related Functions

CHOOSE

Returns the nth value from a list.

HLOOKUP

Looks up a value across rows (horizontal search).

INDEX

INDEX retrieves a value from a specific row and column in a range.

INDIRECT

Turns text that looks like a cell reference into an actual reference.

MATCH

MATCH finds the position of a value in a range.

TRANSPOSE

Flips rows and columns of an array.

Frequently Asked Questions

What is the difference between OFFSET and INDEX?

OFFSET returns a reference by shifting from a starting point. INDEX returns a value or reference at a specific position in an array.

Is OFFSET volatile?

Yes, it recalculates whenever the sheet changes.

Can I use OFFSET for dynamic named ranges?

Yes, but modern Excel often prefers structured tables or FILTER.