The input data appears in column B.
Note: the formula explained below builds on morebasic examples explained here.
In addition, OFFSET has an optionalwidthand heightarguments, which specify the size of the reference to be returned.
The next argument isrows, which indicates the desired row offset from the starting reference.
TheROW functionis used to get the row number for cell D1.
We start with D1, because we want to start with the number 1.
We subtract 1, because we want the firstrowsoffset to be zero.
In other words, we want to zero out therowsargument and start with cell B5.
However, the result from OFFSET is averticalarrayof values, and we need ahorizontalarray as a final result.
To convert the horizontal array into a vertical array, use the TRANSPOSE function.
Thearrayis returned to cell D5 and the three valuesspillinto the range D5:F5.
OFFSET is handy in formulas that require a dynamic range.
ROW Function
The Excel ROW function returns the row number for a reference.
For example, ROW(C5) returns 5, since C5 is the fifth row in the spreadsheet.
When no reference is provided, ROW returns the row number of the cell which contains the formula.
