fx
FunctionPedia
Quick Answer

OFFSET Function

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

✓ Excel✓ Google SheetsExcel All versions

Syntax

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

Parameters

ParameterDescriptionRequired
referenceThe starting cell or range.Required
rowsNumber of rows to shift down (positive) or up (negative).Required
colsNumber of columns to shift right (positive) or left (negative).Required
heightHeight of the returned range in rows (optional).Optional
widthWidth 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 Tutorials

Absolute vs Relative Reference

Absolute vs relative reference in Excel explained: what $ really locks, when to use mixed refs, the F4 shortcut, and dynamic alternatives in Google Sheets.

Excel REF! Error Fix

The complete Excel REF error fix: why #REF! appears, which edits break cell references, and how to repair the damage or prevent it from ever returning.

INDIRECT Function: Build Dynamic References From Text

INDIRECT function dynamic reference: convert text into a live reference for dynamic sheet names, expandable SUM ranges and dependent dropdowns in Excel.

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.