OFFSET Function in Google Sheets | 3-min Guide

Reading Time: 4 minutes

What the function does: Returns the value of a cell a specified number of rows or columns away from the reference cell or range.

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

cell_reference – the starting point from which to count the offset rows and columns. It can be a single cell or a range of cells.

offset_rows – the number of rows that the function needs to move

offset_columns – the number of columns that the function moves

height – [Optional] the height of the range to return

width – [Optional] the width of the range to return

Sample Usage
=OFFSET(A2,5,3)

//This will return the value in the fifth row and third column from A2 as the answer.

Quick answer:
A simple way to return an answer from a single cell using the OFFSET function:
Enter the formula =OFFSET(A2,5,3) in an empty cell

This will return the value in the fifth row and third column from A2 as the answer.

Objective

Learn what the OFFSET function in Google Sheets does and how to use it.

Introduction

The  OFFSET function in Google Sheets returns the value of a cell of a range of cells, a specified number of rows or columns away from the reference cell or range (cell reference). You can use the OFFSET function to return just one cell or a whole range of cells.

The OFFSET function in Google Sheets is especially useful when working with moving data ranges and the data is updated periodically, such as calculating the rolling average of sales for the last three months. The OFFSET function removes the need to manually update the data each time the record is updated.

Other uses of the OFFSET function include financial modelling and dynamic charts.

How to use the OFFSET function in Google Sheets

To understand how to use the OFFSET function in Google Sheets, let us consider the sales value of a small imaginary company.

Example 1: Using the OFFSET function to return a result from a single cell

In this first example, let us use the OFFSET function to return a result from a particular cell.

Suppose we want to look for the sales value in the third week for the month of June, we can use the OFFSET function to do so.

Enter the formula below in an empty cell where you want the result displayed.

=OFFSET(A2,6,3)

Here, we take A2 as the reference cell, 6 is the number of rows for June from A2, and 3 is the column for the third week.

This will return the sales for the third week of June, which is 2545, as shown below.

Pro tip:
Use negative numbers for the offset rows and offset columns to move to the left of the reference cell or up.

Example 2: Using the OFFSET function in a dynamic data

In this example, we will consider a case where the OFFSET function can be used for dynamic data to keep the data record up-to-date without having to manually do so.

Let’s assume that we want to know the sales value for the last three months for which we have the data. We can do this using the OFFSET function with the COUNTA function. The COUNTA function counts the number of cells that have a value in them.

The formula is:

=OFFSET(A2,COUNTA(B3:B10)-2,0,3,5)

The COUNTA function is used to fetch values from the available data from the range specified, ie, B3:B10, and we use -2 so that the values for the last three months is fetched and returned. The offset column is 0 as the months lie in the same column as the reference cell. 3 is the height and 5 is the width of the data to be returned.

On entering the formula, the value for the last three months will be shown. The result is updated accordingly when the monthly sales data is updated.

And below is the result after the monthly sales data has been updated.

Example 3: Using the OFFSET function inside another formula

The OFFSET function can also be used–in fact, most commonly used–inside another function. Let us consider how we can use the OFFSET function inside the AVERAGE function in Google Sheets to calculate the average of a dynamic data set.

In order to compute the average of the monthly sales data, we use the following formula

=AVERAGE(F3:OFFSET(F2,COUNTA(F3:F10),0))

Here, F2 is the reference cell, COUNTA(F3:F10) is the rows from which to pull the data, and 0 is the distance of the column from the reference cell, which is the same as the column of the reference cell.

This will give us the rolling average for the months for which we have the data. If any change is made to the data, the average will change automatically.

Why am I getting #REF! Error?

It may sometimes happen that you mistype the formula or specify invalid rows and columns. The formula will then return a #REF! Error. This happens when the values that you input to the OFFSET function fall out of the bounds of the rows and columns in your data. To avoid getting this error, ensure that you enter the row and column values correctly.

See Also

SWITCH Function in Google Sheets: Well explained guide on how to use the SWITCH function in Google Sheets.

IRR Function in Google Sheets: Learn how to use the IRR function in Google Sheets.

GROWTH Function in Google Sheets: Learn how to use the GROWTH function in Google Sheets.