What is EXCEL OFFSET FUNCTION
The Excel OFFSET Function is listed under Microsoft Excel's Lookup Functions category. It returns a reference to a range which is a specific number of rows and columns away from a cell or range of cells. In simple words, you can refer to a cell or a range of cells by using rows and columns from a starting cell.
How to use it
To learn how to use the OFFSET function in Excel, you need to understand its syntax and arguments:
OFFSET(reference, rows, cols, [height], [width])
- reference: The reference from which you want to offset to start. It can be a cell or range of adjacent cells.
- rows: The number of rows that tell OFFSET to move up or down from the reference. To go downward you need a positive number and for going upwards you need a negative number.
- cols: The number of columns tells OFFSET to move to the left or right from the reference. To go right you need a positive number and for going left you need a negative number.
- [height]: A number to specify the rows to include in the reference.
- [width]: A number to specify the columns to include in the reference.
- OFFSET is a "volatile" function, it recalculates whenever there is any change to a worksheet.
- It displays the #REF! error value if the offset is outside the edge of the worksheet.
- If height or width is omitted, the height and width of reference are used.
To master the OFFSET function we need to try it out in an example and below is one which you can try out:
In the below example, we have used SUM with OFFSET to create a dynamic range which sums the values from all the months for a particular product.
This tutorial is the part of our Excel Functions with Examples (Function Guide) and below are some of the related functions:
- EXCEL ADDRESS Function
- EXCEL AREAS Function
- EXCEL CHOOSE Function
- EXCEL COLUMN Function
- EXCEL COLUMNS Function
- EXCEL FORMULATEXT Function
- EXCEL HLOOKUP Function
- EXCEL HYPERLINK Function
- EXCEL INDEX Function
- EXCEL INDIRECT Function
- EXCEL LOOKUP Function
- EXCEL MATCH Function
- EXCEL ROW Function
- EXCEL ROWS Function
- EXCEL TRANSPOSE Function
- EXCEL VLOOKUP Function
About the Author
Puneet is using Excel since his college days. He helped thousands of people to understand the power of the spreadsheets and learn Microsoft Excel. You can ﬁnd him online, tweeting about Excel, on a running track, or sometimes hiking up a mountain.