The OFFSET function allows you to indirectly refer to a cell or a range of cells in Excel. With OFFSET, you can pick a starting reference point, and then input an address to the target cells rather than directly referring to them.

This makes the OFFSET function extremely useful in dynamic Excel spreadsheets. You can use the OFFSET function on its own, but its true potential comes out only when you nest it inside other functions in Excel. Read on to learn all about OFFSET in Excel with three examples.

What Is the OFFSET Function in Excel?

The OFFSET function returns a reference to a cell or a range of cells. OFFSET takes five parameters, and its syntax is as below:

        =OFFSET(reference, rows, cols, height, width)
    

OFFSET will take in the position of the reference cell, and then move from there by the number of rows and cols, and then return the reference of the destination cell.

If the height and width parameters are set to greater than one, then a range of cells in that area will be returned. Height and width are two optional parameters. If you leave them blank, OFFSET will read them as 1 and return a single cell reference.

How to Use the OFFSET Function in Excel

To use OFFSET in Excel, you need to input the three required parameters. Once you set the reference cell, OFFSET will move from that cell to the cells below by the number of the rows parameter. It will move to the right by the number of the cols parameter.

From there on, OFFSET will return a reference to the destination cell. If you set parameters for height and width as well, OFFSET will return references to a range of cells, where the original destination cell will be the upper-left cell.

This might sound complicated on paper, but it's much easier to understand in action. Let's see how the OFFSET function performs with a couple of examples.

1. OFFSET Function Example: Referring to a Single Cell

A sample spreadsheet in Excel

To get things started, let's use the OFFSET function on its own to get a grasp of how it navigates the spreadsheet. In this example, we have a bunch of numbers in an Excel spreadsheet. We're going to use the OFFSET function to refer to C4.

When the OFFSET function is used plainly, it will output the content of the destination cell. So we should get the content of cell C4, which is the number five, once we input the formula. Let's get started.

  1. Select a cell to input the formula. We're going to use cell G1.
  2. In the formula bar, enter the formula below:
            =OFFSET(A1, 3, 2)
        
    This formula calls on the OFFSET function to take in the position of cell A1, then move down by 3 cells, and then move right by 2 cells to reach cell C4.
  3. Press Enter.

Once you press Enter and the formula is enacted, the cell will return 5. This is the number from cell C4, so your formula worked! You can also use the evaluate feature to better understand Excel formulas, such as OFFSET.

2. OFFSET Function Example: Referring to a Range of Cells

Using the two optional parameters, height and width, you can also return a range of cells with the OFFSET function in Excel. Let's pick up where we left off from the last example.

This time, we're going to return cells C4 to D9 using OFFSET in Excel. Just like before, we're going to use A1 as our starting reference point.

  1. Select the cell where you want to input the formula. We're going to use cell G1 again.
  2. In the formula bar, enter the formula below:
            =OFFSET(A1, 3, 2, 6, 2)
        
    This formula works much like the previous one, except that once it reaches the destination cell (C4) it will take in 6 rows and 2 columns rather than the target cell alone. C4 will be the first cell in the new range.
  3. Press Enter.
OFFSET function used to return a range of cells

Now the cell range C4 to D9 should appear where you put in the formula. The output will be an array.

3. OFFSET Function Example: Compound Formulas

The two examples before this were a warm-up to see how OFFSET works in Excel. OFFSET is usually nested with other Excel functions in practical scenarios. Let's demonstrate this with a fresh example.

In this example, we have the incomes of a side project throughout the years 2017 to 2021. On the upper side, we have a cell that is supposed to show the total income in given years starting from 2021.

A practical example of the OFFSET function in Excel

The goal here is to create a dynamic spreadsheet using OFFSET in Excel. When you change the number of years for the total, the formula calculating this value should update as well.

In order to achieve this goal, we're going to use the OFFSET function along with the SUM function. Rather than referring to the cells directly, we're going to use OFFSET. This way, we can dynamically get the range of cells that will be incorporated into SUM.

In this example, the year 2021 is in cell H5, and the number of years for the total is in cell C1. We have used custom formatting in the cell containing the years. The actual value is the number of years, and the Years text is a suffix. Let's start by creating a formula to calculate the total. We're going to use the SUM function in Excel.

  1. Select the cell where you want to calculate the total.
  2. In the formula bar, enter the formula below:
            =SUM(OFFSET(H5,0, 0, 1, -C1))
        
    This formula consists of OFFSET and SUM. In the first step, OFFSET will go to cell H5. From there, it will move by 0 rows and 0 columns, and then it will return a range of cells starting from H5. This selection will be 1 cell in height, and -C1 cells in width. This means that OFFSET will return a range starting from H5 and moving to the left by C1 cells.
  3. In the final step, the SUM function will add the numbers in the range together and output them.
  4. Press Enter.
Results of an OFFSET example in Excel

The number you get as the total now depends on the number of years you've input. If the number of years is set to 1, then you will get the total for the year 2021 only.

Notice that the total updates instantly once you change the number of years. If you change the number of years to 7, the SUM function will calculate the total from 2015 to 2021.

Dynamic Spreadsheets With OFFSET

The OFFSET function in Excel has many parameters, and that can cause confusion on its own, but once you get to use it, you'll learn that, like many other Excel functions, OFFSET is a friendly function.

Though there isn't much use to OFFSET on its own, when coupled with other Excel functions, OFFSET gives you the power to create dynamic and sophisticated spreadsheets.