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

excel_offset_function_01

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

=SUMPRODUCT(MAX(($B:$B<>"")*ROW($B:$B)))

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

=OFFSET(B1,0,0,SUMPRODUCT(MAX(($B:$B<>"")*ROW($B:$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

http://www.techonthenet.com/excel/formulas/offset.php

http://office.microsoft.com/en-gb/excel-help/offset-function-HP010342739.aspx

 

Wyman W
Wyman is a Business Analyst based in Hong Kong, specialized in business analysis, project management, and also creating custom Function and Sub solutions, and is proficient in report automation with Access.

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

Leave a Reply

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