The syntax of OFFSET(…) Function
Examples of OFFSET(…) Function
Example: =OFFSET(A5, 2, 2, 1, 1)
The above example says that the reference value is A5, then go two steps down(two rows down, you are now in cell A7), now go two steps right (two columns right, you are now in cell C7). As height and width, both are 1, no cell range will be selected.
To remember easily just use this way: A5 is the reference, add two rows and two columns with this to find out the next reference: A5+2 rows=A7 and A7+2 columns= C7. Simple.
Another Example: OFFSET(C7,5,3,1,1). New reference will be: C7+ 5 rows=C12 and C12+3 columns= F12. Our new reference will be F12 as height and width are both 1, so no cell range will be selected.
See the following screenshot.
More Example: OFFSET(A5, 2, 1, 2, 2). This time height and width are 2. The new cell reference is B7 and the cell range will be now: B7: C8.
More Example: OFFSET(A5, 2, 1, 2, 3). This time height and width are 2 and 3. The new cell reference is B7 and the cell range will be now: B7: D8.
Important: This cell range you can’t show, just use this cell range to do other jobs like SUM, AVERAGE, and other functions. We have used SUM to use this cell range.
Do the above example practically in the sample file.
Happy Excelling 🙂
- How to Use INDIRECT Function in Excel (12 Suitable Instances)
- How to Use ROW Function in Excel (With 8 Examples)
- How to Use ROWS Function in Excel (With 7 Easy Examples)
- How to use COLUMNS Function in Excel (3 Examples)
- How to Use COLUMN Function in Excel (4 Easy Examples)
- How to find text in an Excel range & return cell reference (3 ways)
- Excel Reference Cell in Another Sheet Dynamically