Excel's OFFSET Function: Examples and Usage

Buzz

Ngày cập nhật gần nhất: 15/4/2026

Content

OFFSET Function in Excel

Guide to Using OFFSET Function in Excel - Illustrated Examples

In Excel, the OFFSET function is quite popular among users. If you're not familiar with this OFFSET function, you can learn more in the following article to apply it accurately and achieve high efficiency in your work.

The OFFSET function in Excel returns a reference to a specific range, calculated by a starting cell or range and a specified number of rows or columns. You can specify the number of rows and columns of the returned reference range.

OFFSET Function in Excel

Guide to Using OFFSET Function in Excel - Illustrated Examples

Syntax: =OFFSET(reference, rows, cols, height, width)

Where:

- Reference: is the reference range serving as the base for the function (the starting point) to create a new reference range.
- Rows: are the number of rows above or below the reference, counted from the first cell (top-left corner) of the reference.

- Columns:- Vertical Range:- Horizontal Range:

Note:

- Reference must point to a single cell or a contiguous range; otherwise, the function will return an error #VALUE!.
- Rows can be positive (below) or negative (above)
- Columns can be positive (right of the reference range) or negative (left of the reference range).
- Height and width must be positive numbers.
- If the row and column offsets cause the reference range to extend beyond the worksheet boundaries, the OFFSET function returns an error value #REF!
- If height and width dimensions are omitted, they default to the same height and width as the reference range.

Example

Example 1: Offset is currently at cell C9 and we want to fetch the value of cell E10 by moving down 1 row and right 2 columns, the result will be as shown below.

Example 2: Error Scenario: You fetch a value beyond the boundary of the worksheet. Offset is currently at cell C9 and you move up 3 cells and left 4 cells. The result will return an error as shown below.

Example 3: Combining OFFSET with Another Function

Assuming OFFSET is at cell C9 and you want to calculate the total salary of the last employee, Phung Huu Thien, you type the following formula =SUM(OFFSET(C9,2,0,1,3)) and the result will be as shown below.

The Offset function does not actually move any cells or change the selection, it simply returns a reference.

Frequently Asked Questions

What is the primary function of the OFFSET feature in Excel?

The primary function of the OFFSET feature in Excel is to return a reference to a specific range. This reference is calculated based on a starting cell or range and a specified number of rows and columns.

How do I properly use the OFFSET function in my Excel spreadsheets?

To use the OFFSET function in Excel, you should follow the syntax: =OFFSET(reference, rows, cols, height, width). This will allow you to create a reference range effectively.

Can the OFFSET function cause errors in Excel if misused?

Yes, the OFFSET function can cause errors if it references a range that exceeds worksheet boundaries. For example, using negative rows or columns that extend beyond the worksheet will result in a #REF! error.

What parameters are necessary to execute the OFFSET function correctly?

To execute the OFFSET function correctly, you need to provide the reference range, the number of rows, columns, and optionally the height and width of the returned range.

Mytour's content is for customer care and travel encouragement only, and we are not responsible.

For errors or inappropriate content, please contact us at: [email protected]