The OFFSET function is one of the most enigmatic Excel functions that may take a while to wrap your head around. But I will ensure that by the end of this tutorial, you would not just be able to understand what the Excel OFFSET function does, but also learn how you can use it in your own worksheets.
OFFSET is an in-built worksheet function categorized as a Lookup/Reference function in Excel. The purpose of the OFFSET Excel function is to return a reference to a single cell or a range of cells, based on the rows and columns prescribed in the arguments of the function, from a cell or range of cells.
Table of Contents
Syntax
=OFFSET(reference, rows, cols, [height], [width])
Arguments:
reference – This is a required argument where you put in the range’s starting point from where you would like to apply the offset.
rows – This is a required argument that instructs Excel whether you would like to move down or up the rows starting from the value in the reference argument. The value in the rows argument may be positive or negative. A positive value will instruct Excel to move downwards, while a negative value will instruct Excel to move upwards from the cell or range in the
reference – argument. Entering a value greater than the number of available rows in the rows argument will return a #REF! error.
cols – This is a required argument that has a purpose similar to the rows argument. Input a positive value in the cols argument to instruct Excel to move towards the right, or a negative value to instruct Excel to move left from the cell or range in the reference argument. Entering a value greater than the number of available columns in the cols argument will return a #REF! error.
height – This is an optional argument that lets you prescribe the size for the number of rows in the returned range. Omitting this argument will result in the formula returning a default size of 1 row.
width – This is an optional argument that lets you prescribe the size of columns in the returned range. Omitting this argument will result in the formula returning a default size of 1 column.
Important Characteristics of the OFFSET function
- The OFFSET function will return a reference without actually moving the cells or range of cells.
- The OFFSET function may be nested into other Excel functions, provided that such function’s arguments accept a cell or range reference.
- The rows and columns in the range of cells returned by the OFFSET function always use the cell lying 1 column up and 1 column to the left of the returned range for reference.
- The OFFSET formula returns a #VALUE! error when the function’s
referenceargument does not include either a cell or range of end-to-end cells. - If the rows and columns specified in the OFFSET function argument refer to cells outside the spreadsheet, the OFFSET function will return a #REF! error.
Examples of the OFFSET function
Let’s go through some examples to just look at what happens when you execute OFFSET function. If you prefer, do these examples on a spreadsheet alongside, to get a good grip over the OFFSET function.
1. Basic example for the OFFSET function
The plain-vanilla version of the OFFSET function
=OFFSET(B2,4,0)
Enter the cell B2 as a value for the reference argument, 4 for the rows argument, and 0 for the cols argument. Via this OFFSET formula, you are instructing Excel to return value in the cell that sits 4 rows downwards and 0 columns towards the right (i.e., in the same column), starting from the reference cell B2. See if you can manually spot this cell, and then check what the function has returned. The function returns the value “250” from cell B6.
Did you get it right? If not, try the next one.
2. Return when the cell has no value
=OFFSET(C7,1,-2)
Let’s try it out.
Enter the cell C7 as the reference argument, 1 as the rows argument, and -2 as the cols argument. Here is what you just instructed Excel to do: Find me the value in the cell that sits 1 row downward, and 2 columns to the left, of the cell C7.
Walk yourself through this instruction and see which value you arrive at. Did you end up in an empty cell? Well, so did Excel. Therefore, it will return ‘0’.
3. Cell references for the values in the rows and cols arguments
=OFFSET(B2,A3,0)
Enter the cell B2 as a value for the reference argument, the cell A3 as the rows argument, and 0 as the cols argument. Since the value in cell A3 is 2, Excel will return the value 2 rows down from the cell B2, which is “150” in our example.
4. Use the optional height and width arguments
=OFFSET(A1,1,1,3,1)
Entering the optional arguments will allow you to fetch a range of cells. Let’s use cell A1 as a value for the reference argument, 1 for both the rows and cols argument, 3 for the optional height argument, and 1 for the optional width argument.
This should give you a range, but if you are on an older version of Excel, you may encounter a #VALUE! error.
Just go back to editing the formula, and instead of Enter, hit Ctrl + Shift + Enter. This will give you the value of the range’s first cell. Extend this formula into other cells to the extent of value input in the height and width arguments and that should do away with the #VALUE! error.
5.#REF! Error
=OFFSET(B2,-3,-3)
This is what happens when you instruct Excel to return a cell that does not exist on your worksheet.
Let’s look at it in real-time. Enter the cell B2 as your reference argument, -3 as the value for both the rows and cols arguments. Since the referenced cell, i.e., the cell 3 rows upwards and 3 columns to the left of the cell B2 does not exist on the worksheet, it will return a #REF! error.
Example OFFSET + SUM Function
We will pick up the same formula we used while entering the optional height and width arguments and embed it in a SUM function, like so:
=SUM((OFFSET(A1,1,1,5,1)))
Here is what is happening when you use this formula: The OFFSET formula will return the range B2:B6, and subsequently, the SUM function will sum up the values in the range (50 + 100 + 150 + 200 + 250) and return the value ‘750’.
Example OFFSET + AVERAGE, MIN & MAX Function
Let’s say you are trying to keep track of your average monthly expenses. You want a moving average for the previous 3 months so you can draw up a budget accordingly. To accomplish this, we will use a combination of 3 functions:
- OFFSET function,
- COUNTA function or COUNT function, and
- AVERAGE, MIN, MAX function.
So, let’s quickly run through how a COUNTA function or COUNT function works when embedded in the OFFSET function. Once we are through, we will see where the AVERAGE function fits into the equation.
You already know how to sum up a range by fetching it with the OFFSET function. With the COUNT function, our aim is to include freshly added data points automatically so that the OFFSET function returns a range accordingly. We will then average out this range to obtain our final value. If you cannot seem to wrap your head around this, try to work it out with this example:
=AVERAGE(OFFSET(B1,COUNT(B:B)-E4+1,0,E4,1))
Let’s break this formula down. Essentially, the COUNT function works as the rows argument in the OFFSET function. COUNT(B:B) will return 5, which is the number of numeric data points in column B. From this, we will subtract the value in cell E4 (which is the number of data points we want to sum up from column B, starting from the last data point – which is 3 in our case). We will then add 1 because we want to include 3 data points, excluding +1 will only return the final 2 data points since there is a header cell at the top.
If you choose the COUNTA function, you do not need to add the +1 because this function will count the non-numeric header cell as well.
So, let’s see what our entire function looks like at this point when the COUNT function returns 3 (i.e., COUNT(B:B) = 5, – 3, and + 1):
=AVERAGE((OFFSET(B1,3,0,E4,1)))
As our next step, we will deal with what the OFFSET function will return. We know from our previous example that prescribing a height argument will return a range. Here, the returned range will include the values 150, 200, and 250 or the range B4:B6.
This simplifies our function to:
=AVERAGE(B4:B6)
This will return 200 [(150 + 200 + 250)/3].
Perfect, the remaining part of this example is a piece of cake. You only need to update the formula a little. But first, let’s see why we would want to do that.
Perhaps, instead of averaging the expenses of the past 3 months, you choose to look at the month in which you spent the maximum or minimum amount from the immediately preceding 3 months.
To find those amounts, we will use the following formulas, depending on the value you would like to fetch:
=MAX(OFFSET(B1,COUNT(B:B)-E4+1,0,E4,1))
OR
=MIN(OFFSET(B1,COUNT(B:B)-E4+1,0,E4,1))
The only difference, as you can see, is the use of the MAX or MIN function instead of the average function.
It’s that simple.
Example OFFSET + VLOOKUP Function
The VLOOKUP function’s limiting factor is its inability to work with values that lie towards the lookup column’s left. This means that the VLOOKUP function returns only those values that lie on the lookup column’s right.
To perform a lookup towards the left, we will combine the OFFSET function with MATCH and ROWS functions, like so:
=OFFSET(table_range,MATCH(row_lookup_value,OFFSET(table_range,0,col_lookup_offset, ROWS(table_range),1),0)-1,col_return_offset,1,1)
The formula is not as complex once you know what the arguments are. Here is what each argument is for:
table_range: This argument defines a table within which the formula needs to look.row_lookup_value: This argument is the value you would like to match.col_lookup_offset: This argument specifies how many columns sit between the lookup column and starting column.col_return_offset: This argument specifies how many columns lie between the column from which we want to fetch the return and the starting column.
If you are still having a little trouble, quickly scan through the MATCH and ROWS function, and you will be set.
Here is what it should look like on your sheet:
I swapped the sequence of the columns and used the mentioned formula. It would have been impossible to fetch this output with the VLOOKUP function.
What the formula does here is it looks inside the table_range (i.e. A2:B6) for the row_lookup_value mentioned in cell E4 (i.e., 4). Since we will need to search for this row_lookup_value in column B, we will set the col_lookup _offset to ‘1’ for moving 1 column rightwards starting from the table’s 1st column (i.e., I). Also, since the value we want the formula to return lies in the first column, we will set the col_return_offset to ‘0’.
Upon executing the formula, it returns ‘200’, the value that sits left of the row_lookup_value ‘4’.
Limitations of Excel OFFSET function
I hope these extensive examples gave you a good handle on the Excel OFFSET function. While the OFFSET function offers abundant utility, there are 2 shortcomings that you need to be aware of.
Here are the 2 limitations of the OFFSET function:
- The OFFSET function is resource-intensive. This does not matter much if you are working with a fairly straightforward spreadsheet with a scarce number of formulas. However, when your worksheet is loaded with formulas, OFFSET formula recalculations triggered by changes in the source data may take Excel some time.
- OFFSET formulas may be challenging to debug. In particular, the nested OFFSET formulas that return dynamic references may make you feel dizzy.
Alternatives to the OFFSET function
Much like the way we used the OFFSET function to deal with the limiting factors of the VLOOKUP function, there are other Excel functions that can gracefully replace the OFFSET function. Here are 2 such functions:
- INDEX function
INDEX is a non-volatile function and will therefore not cause your Excel to slow down during recalculations. Although the INDEX function does not perfectly resemble the OFFSET function, it is capable of creating dynamic range references.
- INDIRECT function
INDIRECT is a dynamic Excel function that can refer to other sheets or workbooks. Additionally, it allows the creation of dynamic range references using several sources like named ranges, cell values (using the ‘&’ operator), and text strings.
