Excel OFFSET Function - How to Use (2024)

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.

Excel OFFSET Function - How to Use (1)

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 reference argument 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

Excel OFFSET Function - How to Use (2)

=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

Excel OFFSET Function - How to Use (3)

=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

Excel OFFSET Function - How to Use (4)

=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

Excel OFFSET Function - How to Use (5)

=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

Excel OFFSET Function - How to Use (6)

=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:

Excel OFFSET Function - How to Use (7)

=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))

Excel OFFSET Function - How to Use (8)

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:

Excel OFFSET Function - How to Use (9)

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.

Excel OFFSET Function - How to Use (2024)

FAQs

How to use Excel offset function? ›

How to use the Excel OFFSET function
  1. Insert the OFFSET function. Click on the cell where you want the function to return the value or values. ...
  2. Enter the reference argument. ...
  3. Enter the rows argument. ...
  4. Enter the cols argument. ...
  5. Enter the height and width arguments. ...
  6. Press "Enter"
Dec 19, 2022

Why not to use offset Excel? ›

Spreadsheet gurus hate OFFSET because it is a volatile function. If you go to a completely unrelated cell and enter a number, all of the OFFSET functions will calculate. Even if that cell has nothing to do with H1 or B2.

How does offset match work in Excel? ›

MATCH looks for a value and returns the row number of the found value to the OFFSET function. And the OFFSET function returns the cell content of a corresponding cell in the same row as the found value.

What is the offset function in Excel for dynamic calculations? ›

The OFFSET formula in Excel is a powerful tool that helps you reference cells or ranges by specifying a starting point and moving a certain number of rows and columns. This makes managing large datasets or tables much easier, as you can create formulas that automatically adjust as your data changes.

How to calculate offset value? ›

To calculate the offset values for the coordinates that you are working with: Determine the lowest negative X, Y, and Z coordinates within the range of coordinates for the locations that you want to represent. If your data is to include negative measures, determine the lowest of these measures.

Why is offset useful? ›

OFFSET is a great formula to keep in mind when you need to create reports that involve moving data ranges. Some examples are: Calculating the average of the last three months when you add new data to your table. Getting data from the last cell in your raw data range – either last row or last column in that range.

What is the purpose of offset? ›

The goal of offsetting is to reduce an investor's net position in an investment to zero so that no further gains or losses are experienced from that position. In business, an offset refers to the generation of gains in one business unit that makes up for losses in another unit.

What is the difference between index and offset functions in Excel? ›

While the OFFSET function moves from the starting point by a certain number of rows and/or columns, INDEX finds a cell at the intersection of a particular row and column.

What is the error in offset function in Excel? ›

If the rows and cols argument values move the formula past the edge of the worksheet, OFFSET returns the #REF! error, which means that the reference is invalid.

How does offset work in sheets? ›

OFFSET in Google Sheets shifts a cell range based on your inputs. It is ideal for ever-changing datasets because it alters the range of references as your data grows or shrinks. Syntax simplified, use OFFSET like this: cell_reference: Your starting point.

What is the difference between Xlookup and offset? ›

The Excel XLookup Function if used right, allows you to look in one row, and to return the result set from a different row, different tab, or even a different workbook. The term for that is “Offset“. Offsetting is so useful that there is an Excel that strictly, does this, it is the Offset Function.

What is the offset rule in Excel? ›

OFFSET can be used with any function expecting a reference argument. For example, the formula SUM(OFFSET(C2,1,2,3,1)) calculates the total value of a 3-row by 1-column range that is 1 row below and 2 columns to the right of cell C2.

What is dynamic offset? ›

A Dynamic Offset is a special kind of dimension line with at least one end anchored to another drawing object or survey object. This kind of offset is dynamic because it automatically updates itself as the anchor objects change. Dynamic offsets are used to show lot line setbacks (offsets) on a map.

What is the indirect and offset function in Excel? ›

The INDIRECT and OFFSET are Volatile functions, that cause recalculation of the formula in the cell where it resides every time Excel recalculates. In workbooks with large data sets and lots of formulas, adding a volatile function can make the worksheet feel sluggish.

How to copy value from one cell to another in Excel using formula? ›

Use “= (equal) key” (Suitable for beginners)

Step 1: Select the cell where you want the formula to be created. Press the “= (equal)” key on your keyboard, then click on the cell containing the necessary value. The procedure is built for you using a cell reference. Step 2: Press the “Enter” key on your keyboard.

How do you use the offset function in name manager? ›

How to use the OFFSET formula with a defined name
  1. In a new worksheet, enter the following data. Number. ...
  2. Click the Formulas tab.
  3. In the Defined Names group, click Name Manager.
  4. Click New.
  5. In the Name box, type Date.
  6. In the Refers to box, type the following text, and then click OK: ...
  7. Click New.
  8. In the Name box, type Sales.
Jun 6, 2024

How do I move down a line in Excel within a cell? ›

To start a new line of text or add spacing between lines or paragraphs of text in a worksheet cell, press Alt+Enter to insert a line break.

How do you apply offset bottom right in Excel? ›

Specifically to add a shadow effect, you will need to look for the shadow options in the Format Plot Area dialog, where you can select the Offset: Bottom Right shadow effect. Such customizations help in enhancing the visual appeal of the chart and making the data more understandable when presenting.

References

Top Articles
Latest Posts
Recommended Articles
Article information

Author: Kareem Mueller DO

Last Updated:

Views: 5957

Rating: 4.6 / 5 (46 voted)

Reviews: 93% of readers found this page helpful

Author information

Name: Kareem Mueller DO

Birthday: 1997-01-04

Address: Apt. 156 12935 Runolfsdottir Mission, Greenfort, MN 74384-6749

Phone: +16704982844747

Job: Corporate Administration Planner

Hobby: Mountain biking, Jewelry making, Stone skipping, Lacemaking, Knife making, Scrapbooking, Letterboxing

Introduction: My name is Kareem Mueller DO, I am a vivacious, super, thoughtful, excited, handsome, beautiful, combative person who loves writing and wants to share my knowledge and understanding with you.