OFFSET Function - Excel
Overview
The OFFSET function in Excel is a useful tool to return a reference to a range that is offset from a starting cell or range of cells. This function dynamically creates a reference that starts from a specified cell and then moves a certain number of rows and columns from the start point.
Example:
=OFFSET(D1:G4, 1, 1)
Syntax:
=OFFSET(reference, rows, cols, [height], [width])
reference: The starting cell or range of cells.
rows: The number of rows to offset from the starting reference.
cols: The number of columns to offset from the starting reference.
[height]: An optional argument specifying the height of the returned range. If omitted, the height is the same as the reference.
[width]: An optional argument specifying the width of the returned range. If omitted, the width is the same as the reference.
Example
In the example above, we use the OFFSET function to shift the range D1:G4 down 1 row and 1 column to the right. The output maintains the same shape as the original reference range so that is why you can see multiple 0 values around the edges output. The 0s are there to represent that there are no values.
Example: Optional input [height]
In this example, I included the optional height value input in the OFFSET function. This input specifies the height of the OFFSET function output. I set this optional input as a 3, which will only have the function return 3 rows now instead of the 4 it was returning in the earlier example.
Example: Optional input [width]
In this example, in addition to the optional height value input, we also include the optional width value input. This input specifies the width of the OFFSET function output. I set this optional input as a 3, which will only have the function return 3 columns now instead of the 4 it was returning in the first example. With both the height and width being 3, we remove all the unnecessary 0s we saw in the first example.
