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 rangeNote 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
=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 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
