How to Use the OFFSET Function and Formula in Excel
In this tutorial, you'll learn how to use the OFFSET function and formula in Excel. OFFSET is a function to get a reference to a range of cells. You can use it with other Excel functions, such as SUM, SUMIF, COUNT, and COUNTIF.
The syntax of the OFFSET function is as follows:
=OFFSET(reference, rows, cols, [height], [width], ...)
A reference to a range of cells.
- 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 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).
OFFSET Excel Function Examples
The following is the sample list of items we're going to use in the examples:
|1||Item Code||Item Name||Qty||Unit Cost ($)||Unit Price ($)|
|2||Item0011||HP Desktop PC 0011||23||200.00||225.00|
|3||Item0012||HP Desktop PC 0012||24||150.00||750.00|
|4||Item0013||HP Desktop PC 0013||25||210.00||235.00|
|5||Item0014||HP Desktop PC 0014||24||780.00||785.00|
|6||Item0015||HP Desktop PC 0015||32||1040.00||1010.00|
|7||Item0016||HP Desktop PC 0016||30||230.00||255.00|
|8||Item0017||HP Desktop PC 0017||44||274.00||290.00|
Example 1: Return a Reference of a Single Cell
Let's say you want to get a reference to cell B6 from A5, and then you can write the OFFSET formula as follows:
Here, cell A5 is the first argument, and it is the starting point of the OFFSET function. The number of rows is 1, and the number of columns is 1. Both height and width omitted. So, a referent to a cell starts from the cell A5, one column to the right and one row to the bottom.
The above formula returns HP Desktop PC 0015, which is the value of the cell B6.
The above formula returns a reference to a cell at the bottom and right position. Now let's see take a look at another example.
The following example illustrates a formula to get a reference to a single cell at the top and left position.
As you can see, both the number of rows and columns as negative values and cell C5 is the starting point of OFFSET function. Both height and width omitted.
The above formula returns a reference to cell B2 (from cell C5, three rows to the top and one row to the left), and the value of cell B2 is Dell Desktop PC 0011.
Example 2: Returns a Reference to a Range of Cells
In this example, I show you an OFFSET() formula to get a reference to a range of cells.
Let's take a look at an example:
Here, 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 E3. Since both height and width omitted, the height is the same as the height of the range C2:D4, which is 3, and the width is also the same as the width of the range C2:D4, which is 2.
If you include both height and width in the formula, you can write the OFFSET formula as follows:
Both OFFSET() formulas return the same reference to a range of cells E3 through E5.
Example 3: Using OFFSET() with the SUM() Function
In this example, I show you we use OFFSET inside the SUM function.
Let's take the previous formula in Example 3, and place it inside the SUM function.
The formula would like this:
First, OFFSET() returns a reference to a range of cells E3 through E5, and then SUM() sums all the values in the range E3:E5 and returns 1770.
Example 4: Using OFFSET() with the COUNT() Function
In this example, I show you how we use OFFSET() with the COUNT() function.
First, OFFSET() returns a reference to a range of cells E3 through F4, and then COUNT() counts the number of cells in that range and returns 2.
In this tutorial, you've learned how to use the Excel OFFSET function. OFFSET is a function to return a reference to a range of cells. You can use it with other Excel functions, such as AVERAGE, SUM, SUMIF, COUNT, and COUNTIF.