ExcelHow-ToBeginner · 3 min read

How to Use OFFSET in Excel: Syntax, Examples, and Tips

The OFFSET function in Excel returns a reference to a cell or range that is a specified number of rows and columns away from a starting cell. You provide the starting point, how many rows and columns to move, and optionally the height and width of the returned range.

📐

Syntax

The OFFSET function has this syntax:

reference: The starting cell or range.
rows: How many rows to move up or down (positive moves down, negative moves up).
cols: How many columns to move left or right (positive moves right, negative moves left).
[height]: Optional. The height in number of rows for the returned range.
[width]: Optional. The width in number of columns for the returned range.

If height and width are omitted, the returned reference is a single cell.

excel

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

💻

Example

This example shows how to use OFFSET to get the value 30 from a table starting at cell A1.

Suppose cells A1 to B3 contain:

A1=10, B1=20
A2=30, B2=40
A3=50, B3=60

The formula =OFFSET(A1,1,0) moves 1 row down and 0 columns right from A1, returning the value in A2, which is 30.

excel

=OFFSET(A1,1,0)

Output

⚠️

Common Pitfalls

Common mistakes when using OFFSET include:

Using negative rows or columns that move outside the worksheet range causes an error.
Forgetting that rows and cols are relative to the reference cell.
Not specifying height and width when you want a range, which defaults to a single cell.

Example of a wrong formula that causes an error:

=OFFSET(A1,-1,0) tries to move one row above A1, which is invalid.

Correct usage to get a 2-row range starting from A2:

=OFFSET(A1,1,0,2,1)

excel

=OFFSET(A1,-1,0)  // Causes error
=OFFSET(A1,1,0,2,1) // Correct: returns range A2:A3

📊

Quick Reference

Parameter	Description
reference	Starting cell or range
rows	Number of rows to move (positive down, negative up)
cols	Number of columns to move (positive right, negative left)
height (optional)	Number of rows in returned range
width (optional)	Number of columns in returned range

✅

Key Takeaways

OFFSET returns a reference shifted by rows and columns from a starting cell.

Rows and columns can be positive or negative but must stay within the sheet.

Height and width define the size of the returned range; omit for a single cell.

Use OFFSET carefully to avoid errors from invalid references.

OFFSET is useful for dynamic ranges and flexible data selection.