I. Intro
The OFFSET function returns a range reference based on the number of columns left or right, or rows above or below a specified starting point. The result can be a single cell or a range of cells. For example, =OFFSET(A1,1,1) will move one cell to the right and one cell down from A1, which is the B2 cell.
You can also specify the number of rows and columns to return.
II. About the function
- Formula: OFFSET(cell_reference, offset_rows, offset_cols, [height], [width]).
- Arguments:
- cell_reference: The starting position from which to offset the cells. It can be a single cell or a range.
- offset_rows: The number of rows to move up or down. Positive numbers mean moving down, and negative numbers mean moving up.
- offset_cols: The number of columns to move left or right. Positive numbers mean moving to the right, and negative numbers mean moving to the left.
- height: Optional; the number of rows of data to return. Must be a positive number. If omitted, 1 row of data is returned by default.
- width: Optional; the number of columns of data to return. Must be a positive number. If omitted, 1 column of data is returned by default.
Note: If the number of row or column offset exceeds the limits of the sheet, the #REF! error will be returned. When setting the height and width and you want to omit the height, a comma needs to be added to use of the default value.
- Example: ARRAYFORMULA(OFFSET(A1,1,1,2,2)) to move the data to the B2:C3 range. Formula explanation:
- Starting from the A1 cell, move down one cell, and then move one cell to the right to get the new position of B2.
- Starting from B2, return two rows and two columns of data.
Note: You need to nest OFFSET in the ARRAYFORMULA function to return a cell range.
250px|700px|reset
III. Steps
Use the function
- Open the spreadsheet, select a cell, and enter the formula. For example, =OFFSET(A2,3,4) as shown in the image below.
- Press Enter to get the result.
250px|700px|reset
Delete the function
Select the cell with the function and press Delete.