The OFFSET function allows you to indirectly reference a cell or a range of cells in Excel. With OFFSET, you can choose a starting reference point, and then input an address into the target cells instead of referencing them directly.
This makes the OFFSET function extremely useful in dynamic Excel spreadsheets. You can use the OFFSET function on its own, but its true potential is revealed only when you nest it inside other functions in Excel. Read on to learn about OFFSET in Excel with three examples.
What is OFFSET Function in Excel?
The OFFSET function returns a reference to a cell or range of cells. OFFSET takes five parameters, and its syntax is given below.
OFFSET will move to the position of the reference cell, and then advance that number of rows and columns from there, and then return the reference to the destination cell.
If the height and width parameters are set to more than one, 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 cell reference.
How to Use the OFFSET Function in Excel
To use OFFSET in Excel, you need to input three required parameters. Once you set the reference cell, the OFFSET will move from that cell to the cell below it according to the number of row parameters. This calls to the right by the number of parameters.
From there, OFFSET will return a reference to the destination cell. If you also set parameters for height and width, OFFSET will return a reference to a range of cells, where the original destination cell will be the upper-left cell.
It may sound complicated on paper, but in action it is very easy to understand. Let us see how the OFFSET function performs with some examples.
1. Offset Function Example: Referencing a Single Cell
To start things off, let’s use the OFFSET function itself to understand how it navigates a spreadsheet. In this example, we have a set of numbers in an Excel spreadsheet. We’re going to use the OFFSET function to reference C4.
When the OFFSET function is used explicitly, it will output the contents of the destination cell. So we should get the contents of cell C4, which is number five, once we input the formula. let’s get started.
Once you press Enter and the formula is applied, the cell will return 5. This is the number from cell C4, so your formula works! You can also use the Evaluate feature to better understand Excel formulas, such as offsets.
2. OFFSET Function Example: Referencing a Range of Cells
You can also return a range of cells with the OFFSET function in Excel, using two optional parameters, height and width. Let’s start where we left off in the previous example.
This time, we are going to move cell C4 back to D9 using OFFSET in Excel. As before, we’re going to use A1 as our starting reference point.
This formula works in the same way as the previous one, except that once it reaches the destination cell (C4) it will take in 6 rows and 2 columns instead of the target cell alone. The C4 will be the first cell in the new range.
Now the cell range C4 to D9 should appear where you entered the formula. The output will be an array.
3. Offset Function Example: Compound Formula
The two examples before were a warm-up to see how OFFSET works in Excel. OFFSET is usually nested with other Excel functions in practical scenarios. Let us demonstrate this with a new example.
In this example, we have a side project income from 2017 to 2021. On the top side, we have a cell showing the total income for the years starting from 2021.
The goal here is to create a dynamic spreadsheet using offsets in Excel. When you change the number of years for the total, the formula that calculates this value must also be updated.
To achieve this goal, we are going to use the OFFSET function along with the SUM function. Instead of referencing the cell directly, we are going to use OFFSET. This way, we can dynamically get the range of cells that will be included in the SUM.
In this example, the year 2021 is in cell H5, and the total number of years 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 year text is a suffix. Let’s start by creating a formula to calculate the total. We are going to use the SUM function in Excel.