Excel Offset Function

The OFFSET function returns the value of a cell that is a specified number of columns and rows away from the start cell.

Excel offset function

Structure of formula

=OFFSET(Start position,Rows,Columns)

Examples of Excel OFFSET function

The above example shows a use of the OFFSET function on year to date (YTD) data.

=OFFSET(B4,0,D7)

The formula starts at B4, and moves 0 rows down and across 5 rows (as dictated by cell D7) returning the value of 531.

The OFFSET function can be further enhanced by combining with the SUM function.

=SUM(Start position:OFFSET(3,0,D16))

Normally the SUM function is driven by the SUM(start range: end range) however in this example we have swapped the “end range” with the offset function thereby making it a dynamic function.

Using the formula

=SUM(B14:OFFSET(B14,0,D16))

Produces the same results as using the OFFSET function on the YTD values OFFSET in the first example.