Excel OFFSET Function
This tutorial explains how to use the Excel OFFSET function.
Description
The Excel OFFSET function returns a reference to a range of cells.
Syntax
=OFFSET(reference, rows, cols, [height], [width], …)
Arguments
- reference: A range of cells or cell reference.
- rows: The number of rows.
- cols: The number of columns.
- height: [Optional]. The height (the number of rows) that you want the returned reference.
- width: [Optional]. The width (the number of columns) that you want to the returned reference.
The reference must refer to a cell or range of adjacent cells.
If height or width is omitted, it is assumed to be the same height or width as the reference.
The height and width must be positive.
The rows can be positive (below the starting reference) or negative (above the starting reference).
The cols can be positive (right of the starting reference) or negative (left of the starting reference).
Example
A | B | C | D | E | |
1 | Item Code | Item Name | Qty | Unit Cost ($) | Unit Price ($) |
2 | Item0011 | Desktop PC 11 | 23 | 200.00 | 225.00 |
3 | Item0012 | Desktop PC 12 | 24 | 150.00 | 750.00 |
4 | Item0013 | Desktop PC 13 | 25 | 210.00 | 235.00 |
5 | Item0014 | Desktop PC 14 | 24 | 780.00 | 785.00 |
6 | Item0015 | Desktop PC 15 | 32 | 1040.00 | 1010.00 |
7 | Item0016 | Desktop PC 16 | 30 | 230.00 | 255.00 |
8 | Item0017 | Desktop PC 17 | 44 | 274.00 | 290.00 |
Example 1:
=OFFSET(A5,1,1)
In Example 1, The cell A5 is the starting point of OFFSET. The number of rows (rows) is 1 and the number of columns (cols) is 1. Both height and width are omitted. The return value of the OFFSET is Desktop PC 17, which is the value of the cell B8.
Example 2:
=OFFSET(C5,-3,-1)
In Example 2, as you can see, the number of rows and columns can be set as a negative number. The number of rows is -3, and the number of columns is -1, the cell C5 is the starting point in the OFFSET formula above. The return value of the OFFSET formula is Desktop PC 11, which is the value of the cell B2.
Example 3:
=OFFSET(C2:D4,1,2)
In Example 3, the number of rows is 1 and the number of columns is 2 in the range C2:D4, so the starting point of OFFSET is the cell E3. Both height and width are omitted, so the height and width are the same as the height and width of the range C2:D4. The height is 3, and width is 2. The formula above is the same as =OFFSET(C2:D4,1,2,3,2). The return reference of OFFSET formula is a range of cells from E3 through F5.
Example 4:
In this example, we want to sum the values in the range of the return reference in Example 3. We can write the formula as below.
=SUM(OFFSET(C2:D4,1,2))
The return value of the formula is 1770.
Example 5:
=SUM(OFFSET(C2:D4,1,2,2,2))
In Example 5, the SUM function sums the values in the range E3:F4, which is the return reference of OFFSET(C2:D4,1,2,2,2). The return value of the SUM formula is 985.
In this tutorial, you heave learned how to use the Excel OFFSET function. We use the Excel OFFSET function to return a reference to a range of cells.
Here is a full list of Microsoft Excel functions.
Leave a Reply