Excel OFFSET Function and Formula with Examples


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 SUMSUMIFCOUNT, and COUNTIF

OFFSET Syntax

The syntax of the OFFSET function is as follows:

=OFFSET(reference, rows, cols, [height], [width], ...)

Return value

A reference to a range of cells.

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 the returned reference.

Remarks

  • 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:

 ABCDE
1Item CodeItem NameQtyUnit Cost ($)Unit Price ($)
2Item0011 HP Desktop PC 001123200.00225.00
3Item0012 HP Desktop PC 001224150.00750.00
4Item0013 HP Desktop PC 001325210.00235.00
5Item0014  HP Desktop PC 001424780.00785.00
6Item0015 HP Desktop PC 0015321040.001010.00
7Item0016 HP Desktop PC 001630230.00255.00
8Item0017 HP Desktop PC 001744274.00290.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:

=OFFSET(A5,1,1)

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.

=OFFSET(C5,-3,-1)

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:

=OFFSET(C2:D4,1,2)

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:

=OFFSET(C2:D4,1,2,3,2)

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:

=SUM(OFFSET(C2:D4,1,2))

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.

=COUNT(OFFSET(C2:D4,1,2,2,2))

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 functionOFFSET is a function to return a reference to a range of cells. You can use it with other Excel functions, such as AVERAGE, SUMSUMIFCOUNT, and COUNTIF

Leave a Reply

Your email address will not be published. Required fields are marked *