Excel OFFSET Function for worksheet

What is Excel OFFSET Function?

Excel OFFSET Function returns the Range object which is number of rows and columns away from a specific range. You can specify to shift up, down, left or right from a cell.

For example, if column A contains employee ID, and column B contains employee name, you can find the employee name in B2 by shifting A2 to the right. (shift 1 column to the right and shift 0 row). The advantage of using OFFSET property is that even when the actual column change, you don’t need to hardcode “column B”, you just need to specify the column which is one column right from the employee ID column.

There is OFFSET “Property” for Excel VBA, but the parameters are slightly different (without height and width). Read the below article for OFFSET Property.

 Excel VBA OFFSET Property for Range

Syntax of  Excel OFFSET Function

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

reference is the specified range where the shift starts.

rows is the number of rows to shift from the specified range; positive number indicates right shift, negative number indicates left shift

columns is the number of rows to shift from the specified range; positive number indicates down shift, negative number indicates up shift

[height] and [width] are optional, used to expand the returned range

Note that if the returned Range contain multiple Cells, you can only see the first value of returned Range if OFFSET is used in worksheet formula, as one Cell cannot display multiple values.

Example of Excel OFFSET Function


Formula Return Explanation
=OFFSET(A1,1,0) 2 Shift 1 row down from A1 >>>A2
=OFFSET(A1,1,0,2) 3 Shift 1 row down from A1 >>> A2
then expand row down (height) by 2 >>>A2:A3
only return the first data (A2)
=SUM(OFFSET(A1,1,0,2,3)) 39 Shift 1 row down from A1 >>> A2
then expand row down (height) by 2 >>> A2:A3
then expand column right (column) by 3 >>> A2:C3
sum the range A2:C3

You can also combine the use of finding last used Range with OFFSET function in order to return a Range from first row to last used row.

For example, to find the last used row in column B


To set a Range from B1 to last used row in column B


This skill can be applied in setting dynamic range for Print Area and Chart, you are recommended to read the below articles

Excel Dynamic Print Area

Excel graph dynamic data range

 Outbound References




Wyman W
Compensation Survey Manager@Mercer
Wyman is human resources professional, specialized in business analysis, project management, data transformation with Access and Excel.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

You may drop me a message if you are interested in HR consultancy services from Mercer.

Leave a Reply

Your email address will not be published. Required fields are marked *