The OFFSET function allows you to indirectly refer to a cell or a range of cells in Excel.
This makes the OFFSET function extremely useful in dynamic Excel spreadsheets.
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.
Height and width are two optional parameters.
If you leave them blank, OFFSET will read them as 1 and return a single cell reference.
It will move to the right by the number of thecolsparameter.
From there on, OFFSET will return a reference to the destination 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.
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.
Let’s get started.
Once you pressEnterand the formula is enacted, the cell will return 5.
This is the number from cell C4, so your formula worked!
it’s possible for you to also usethe evaluate feature to better understand Excel formulas, such as OFFSET.
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.
Now the cell range C4 to D9 should appear where you put in the formula.
The output will be an array.
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.
The goal here is to create a dynamic spreadsheet using OFFSET in Excel.
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.
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 usethe SUM function in Excel.
The number you get as the total now depends on the number of years you’ve input.
Notice that the total updates instantly once you change the number of years.
