How to use the offset function

Offset Function returns a reference to a range that is a specified number of rows and columns from a cell or range of cells. Depending on context, you can make use of it to reduce your administrative needs to update information.

 

Offset can be applied to

1) Summing a range

2) Make chart more dynamic

3) Create a always updated validation listing

4) Update the range for pivot table

 

In its simplest form, offset could be used to return the value in a cell. e.g

 

	A	B	C	D
1	5
2
3
4				4

 

If you apply the fomula offset =OFFSET(A1,3,3,1,1), you will reference from A1, count 3 rows down and 3 rows to the right and reference only one row and one column. The result is 4.

 

If you apply the fomula offset =OFFSET(D4,-3,-3,1,1), you will reference from D4, count 3 rows up (first -ve3) and 3 rows to the left (second -ve 3) and reference only one row and one column. The result is 5.

 

The next time, I will cover summing a range.