The OFFSET function in Excel returns a value from a cell, or range of cells that are a specified number of rows and columns from another cell.
This function can be really useful for creating dynamic references for your formulas and charts. Here you can see a great example of OFFSET being used for a rolling chart.
The syntax for the OFFSET function in Excel is:
=OFFSET(reference, rows, columns, [height], [width])
| Argument | Purpose |
|---|---|
| reference | The starting cell reference from which the offset will be applied |
| rows | The number of rows to offset from the reference. Enter a positive number for the number of rows below the reference, or a negative number for the number of rows above the reference |
| columns | The number of columns to offset from the reference. Enter a positive number for the number of columns to the right of the reference, or a negative number for the number of columns to the left of the reference |
| height | The height, in number of rows, of the returned range |
| width | The width, in number of columns, of the returned range |
Returning a Value from a Cell
Take the example data below.
To return the value of £160 from cell E2, the following formula can be used.
=OFFSET(A1, 1, 4)
This will offset 1 row down and 4 columns across from cell A1.
Although the numbers are typed into the OFFSET function here. They could be returned by other functions, or form controls for more impressive examples.
Returning a Range using the OFFSET Function in Excel
In addition to returning a value from a cell, OFFSET can also return a range of cells. This can be really useful, because it can be used to feed other functions such as SUM.
=SUM(OFFSET(A1, 1, 4, 5, 1))
This returns the result of £1720.
It sums the Total column for all the products. The height and width arguments are used to select range E2:E6.
Returning the Last Value in a List
A very useful example of using the OFFSET function is to return the last value in a column, or a row. For this, we can insert the COUNTA function into OFFSET for more dynamism.
Take the example data below. This show a list of stock checks. And we want to return the value from the most recent check.
The following formula could be used.
=OFFSET(B1,COUNTA(B:B)-1,0)
The COUNTA function is used for the rows argument to find the bottom of the column. This moves 9 rows from B1 so a 1 is subtracted to make it the last cell in the column (instead of the first cell after the list).
