Skip to Content

How to Use the OFFSET Function in Google Sheets

The OFFSET function in Google Sheets is the cause of confusion for many spreadsheet users. Some find it confusing and don’t understand how it works or why it’s useful.

Because of this, it doesn’t tend to be used very often. But learning this function is definitely useful if you take the time to understand how and why it’s useful.

In this tutorial, I will show you how to use the OFFSET function in Google Sheets.

Table of Contents

OFFSET Function Syntax

The OFFSET function will return a range reference that is shifted from the starting cell reference a certain number of rows and columns according to the specified numbers entered into the formula.

In other words, with the OFFSET function, you can shift a range of a certain number of rows or columns.

This is usually used with formulas using dynamic ranges so that as you enter new data, your function will adjust for these changes. I will show you what I mean by that later in this post.

The syntax of the OFFSET function is:

=OFFSET(cell_reference, offset_rows, offset_columns, [height], [width])

  • cell_reference – this is the starting point that the offset rows and columns will be counted from
  • offset_rows – the number of rows to shift by
  • offset_columns – the number of columns to shift by
  • height – this argument is optional. This is the height of the range to return starting at the offset target
  • width – this argument is optional. This is the width of the range to return starting at the offset target

Here are a couple of important notes about using the OFFSET function

  • If offset_rows or offset_columns are negative, the returned range may end up being beyond the top or left of the spreadsheet, at which point a #REF! error will be returned
  • If this function is used on an array formula, the value returned may overlap with the offset target at which point a #REF! error will be returned
  • If offset_rows or offset_columns are decimal places the decimal place will be truncated

OFFSET Examples

Now that I have covered the basics of the OFFSET function,  let’s look at how it works in a spreadsheet.

In this example below, I have a basic table, and I have the OFFSET function entered several times:

OFFSET Formula example

Let’s break down what each of these formulas do so that you gain a better understanding of this function works.

Here are the formulas that I have entered into my spreadsheet and a breakdown of what each formula is doing:

  • =OFFSET(A2,1,0) – This formula starts at cell A2 and moves 1 row down, and 0 columns to the right, so it returns the value in cell A3
  • =OFFSET(A2,1,1) – This formula starts at cell A2 and moves 1 row down, and 1 column to the right, so it returns the value in cell B3
  • =OFFSET(A2,0,1) – This formula starts at cell A2 and moves 0 rows down, and 1 column to the right, so it returns the value in cell B2
  • =OFFSET(A2,2,1) – This formula starts at cell A2 and moves 2 rows down, and 1 column to the right, so it returns the value in cell B4

As you can now see, the OFFSET function is actually really simple. You are just shifting a range or cell according to the number of rows and columns that you enter into the formula.

Now you may be wondering how exactly this function is useful. I will show you in the next section one of the main ways the OFFSET function is used.

Using OFFSET to Create Dynamic Ranges

One of the main ways OFFSET is used is to create dynamic ranges, which are ranges that automatically update as new data is entered into the spreadsheet. To learn more about dynamic ranges, check out our post on how to create dynamic named ranges.

Here is an example of this is useful:

OFFSET dynamic range-1

In the data above I have a SUM formula that is set to “=SUM(B7:D7)”

The problem with this is that when I add new data, my formula does not automatically include the new column:

OFFSET dynamic range-2

You can see that in this example, I have added new data and my formula is still only performing the SUM on the original three columns.

But, with the OFFSET function, I can create a dynamic range that will automatically include new data in the spreadsheet.

Here is an example of this can be done:

OFFSET dynamic range-3

In this example, I am including OFFSET in the SUM formula. Here is the exact formula used:

=SUM(OFFSET(A7,0,1):E7)

Now when I add a new column, that column will automatically be calculated in the range my SUM formula is referencing:

OFFSET dynamic range-4

The reason this works is that my OFFSET function is setting my SUM range to start one column to the right of the first column. So instead of referencing an exact column like my initial formula was, this formula will begin at one column to the right of the first column. Any time a new column is added, it is automatically included in the range.

Closing Thoughts

Hopefully, you now understand the basics of the OFFSET function and can begin to see how this can be a useful formula to use in your spreadsheets.

Particularly if you have sheets with a lot of formulas where new data is being entered, it can be very tedious to constantly update your formulas. With the OFFSET function, it’s possible to create formulas that automatically include newly entered data.

We have only scratched the surface of how to do this in this guide, but as you get more creative you can begin to implement this in many useful ways.

More Google Sheets Tutorials:
How to Use ARRAYFORMULA
Array Formula VLOOKUP