The OFFSET function in excel is a simple function but with many uses. These uses are showcased when you are aware of the workings of the function and how you can use it to your advantage when working on your models. In today’s post we would walk you through all you need to know about the OFFSET Function and how you can deploy it to a real-life modeling scenario. Before we delve into the scenarios, let’s first check out the purpose of this function, and we would treat some examples immediately after.
Purpose of the OFFSET Function
The OFFSET function is used to define positions. in lay man terms you may ask the question: why do I need this? Can’t I just write the cell reference directly rather than using a fancy function to specify positions. Well, just imagine combining this function with other functions, or using it in named ranges. Unlimited power, I tell you!!. Jokes aside, it really is very powerful in creating dynamic ranges and dynamic formulas.
To elaborate, when we refer to dynamic ranges, we allude to automatically adjusted ranges based on data provided. If we refer to a range such as A1:E5, then we make references to a static range. Now, imagine that we have a couple of worksheets with various functions referring to these static ranges and in the course of our analysis we have been provided with additional data subsequently extending our static range to A1:G5. Having used the initial static range in our functions, we would need to go into each function and manually adjust the cell references. Completing this could cost you countless number of hours. However, by combining the Offset function and a conditional function to define a named range, you can automatically adjust your function without having to edit each function when the static range changes.
Another purpose of the OFFSET function is for defining a position from the starting cell. This is commonly used in FP&A to sum up Year-to-date (YTD) numbers. You are aware that the first month is January, second month is February, third month is March and so on. By combining the Sum and Offset function, you can adjust the additions by taking a starting range from January and automatically selecting the ending range through the use of the OFFSET function.
Syntax & Argument
The Syntax of the OFFSET function is as follows:
=OFFSET(reference, rows, cols, [height], [width])
The OFFSET Function has 5 arguments, 3 required and 2 optional. Below are the details of these arguments:
Required Arguments
- Reference: This is the starting point of the formula. It is the cell or range you base the OFFSET function on. The final position is calculated by counting the number of columns (to the right or left) and the number of rows (above or below) specified by the user.
- Rows: The number of rows to move from the reference point. A positive number implies that the function moves down from the starting point whilst a negative number implies that the function moves up from the starting point.
- Cols: The number of columns to move from the reference point. A positive number implies that the function moves right from the starting point whilst a negative number implies that the function moves right from the starting point.
Optional Arguments
- Height: Used to return a range of rows as the final position. For example B2:B7
- Width: Used to return a range of columns as the final position. For example B2: G2
Now that we are done with the Syntax and arguments, let’s take some examples:
OFFSET Function Examples
In this section, we would be taking you through various examples of utilizing the OFFSET function in excel. Let’s start things off with a basic example
Basic Example
Let’s assume that we want to return a cell reference 3 columns to the right of cell A1, and 2 rows below cell A1. To achieve this, we use the OFFSET function below:
Using the formula above would return whatever is in cell D3.
OFFSET Function with dynamic row & column number
Now let’s do this with a little visual representation. In the data below, we have 6 month’s actual discretionary income report for Helen, and we would show in the next paragraphs how to use the OFFSET function to return a specification.

We want to be able to select the month and category, and then return the exact amount using the Offset function. The criteria we have selected are the month of April and the discretionary income in that month.

To make our row and column count as dynamic as possible, we would add numbers to the immediate left of our column B containing the Month information in the dataset. We simultaneously add numbers to the top of row 2 containing the categories. Since the first numerical value is located in cell D4 which represents disposable Income In January, and having a column and row number of zero (0), we can select this as our reference cell.

To make the row and column number automatic, we use the Index Match combination to lookup the row number and column number. Let’s start with the row number first.
Lookup Row number
To lookup the row number based on the month selection, use the Index Match combination as follows:

Please note that you can also use the XLOOKUP Function to achieve this, however we chose to go with the INDEX MATCH Combination because the XLOOKUP function is only available in Microsoft Excel 2021 and newer versions.
Lookup Column number
For this, we use the same INDEX MATCH Combination, please see the formula below:

Use the Offset Function to return the amount
Now that we have our dynamic row number and column number, we can use the offset function to return the discretionary income in April. Our row number is on cell M6, and our column number is on cell M7. We would use the OFFSET Function as seen below to return the amount from our data set.

Using OFFSET Function for Variance reporting
In this section, we would be touching on how to use the OFFSET Function for variance reporting. Our data set for this section is the actual and budgeted advertising expenses for a company. Our task is to use the OFFSET function to calculate our Year-To-Date actual (“YTD”) and YTD Budget.

Now let’s create a column for the YTD Actual using the OFFSET function. As you can see from the above image, our current month is June and we need a single OFFSET function to sum up the amount from Jan – June for each spend line.
The formula to use to achieve this is as follows:
We are not defining a column or row number, but rather defining the width of the range we need excel to return. So, we use the number 6 (representing June) because that is the number of months with Actuals.

Next step is to repeat for YTD Budget. Our formula for this is similar to the previous one, we just need to change the cell reference:

Finally, we can calculate the variance and apply conditional formatting to signify Above Budget, Equal and Below Budget. We can use a nested IF statement for the latter:

Using the OFFSET Function for Dynamic Named Ranges
We treated dynamic named ranges using the INDEX Function, and in this section, we would show you how to do it with the OFFSET Function. Like we did in the INDEX Function page, we would create a dynamic named range and use it in a dropdown. Let’s begin:
For our example in this section, we have the names of 5 of the disciples of Jesus Christ in Column A and we need to create a drop down based on this data. We however want to make it flexible. The drop down is to increase automatically in the likelihood that we increase the list of disciples.

Define Dynamic Named Range
Now let’s go through the steps:
Step 1: Navigate to the Formula Tab and click on Name Manager

Step 2: Click on new in the name manager tab

Step 3: Type in a name that is easy to remember into the “name” box and type in the formula below in the “Refers to” box. Click on ok when you are done.

Result: A new named range appears in your name manager

We are done with the first part of this section, the second part is deploying our newly defined named range in a drop down list
Dynamic Drop-down list
Our final part of this section is to make a dropdown list using the named range we defined above. To do this we just need to follow the steps below:
Step 1: Navigate to the Data tab, and under the data group click on Data Validation.

Step 2: Now click the drop down in “Allow:” and select List. For the Source, reference the named range with a simple “=named range”. Remember that our named range is disciples, so we just need to reference that.

Result: We included another disciple of Jesus, let’s see if our dropdown would be updated.
