The excel OFFSET function can be used to get the reference to a cell or cell range from the starting point ( cell ). It is very useful when you use it correctly. This article will tell you how to use it with some examples.
1. Excel OFFSET Function Introduction.
- The OFFSET function has the below syntax.
OFFSET(reference, rows, cols, [height], [width])
- The first argument reference is the starting cell address such as A1.
- The second argument rows are the number of rows that offset from the starting cell.
- If the rows argument is a positive number then it means to move down, if the rows argument is a negative number then it means to move up.
- The third argument cols are the number of columns that offset from the starting cell.
- If the cols argument is a positive number then it means to move to the right, if the cols argument is a negative number then it means to move to the left.
- With the above 3 arguments, you can get the reference of the target cell range’s top-left cell.
- The fourth argument height is the target returned cell range rows number if provided.
- The fifth argument width is the target returned cell range columns number if provided.
- The excel OFFSET function can only return the referenced cell range, it can not move the cells.
- The first argument of the OFFSET function should be one cell or adjacent cells, if not the function will return the #VALUE! error text.
- If the returned reference cell range is over the excel spreadsheet’s edge, you will get the #REF! error text.
2. How To Use Offset Function In Excel Example.
- In the below table, if you want to get the reference to cell range C3:D4 from the starting cell A1, you can input the formula =OFFSET(A1,2,2,2,2) in any cell.
A B C D 1 100 2 3 88 4 99 - The rows and cols argument value can be a positive or a negative number.
- When the rows and cols argument is a negative number, then it means offset to up rows and left columns from the starting cell.
- For example, OFFSET(D3,-2,-3) will return the reference to cell A1.
3. How To Offset From The Current Active Cell.
- You can use the formula =OFFSET(INDIRECT(ADDRESS(ROW(), COLUMN())),1,-1) to offset from the current active cell.
- The ROW function returns the currently active cell row number.
- The COLUMN function returns the currently active cell column number.
- The ADDRESS(ROW(), COLUMN()) function returns the currently active cell address text.
- The INDIRECT(ADDRESS(ROW(), COLUMN()))Â function returns the currently active cell object reference.
- And finally, the =OFFSET(INDIRECT(ADDRESS(ROW(), COLUMN())),1,-1)Â formula can return the cell offset 1 row down and 1 column left to the currently active cell.