The Google Sheets OFFSET function is quite an under-used function. This is mainly because a lot of people don’t understand the purpose behind the function, or how it can be effectively made use of. In this tutorial we will help you understand what the OFFSET function does, its syntax, how it is used, and a few areas where it can be helpful.
Table of Contents
What Does the Google Sheets OFFSET Function Do?
OFFSET in Google Sheets mainly returns a reference to a range of cells that are a specified number of cells away from a given cell. In other words, if you want Google Sheets to select a range of cells that are, say, 5 rows, 2 columns away from a cell, then you can use the OFFSET function.
Syntax for the OFFSET Function in Google Sheets
The syntax for the OFFSET function is as follows:
OFFSET(reference, rows, cols, [height], [width])
Here,
- reference is a reference cell on which you want to base the offset. It can be a reference to a cell or a range of adjacent cells. Google Sheets can offset from the current cell too
- rows is the number of rows you want to offset from the reference cell.
- cols is the number of columns you want to offset from the reference cell.
- height is the height (number of rows) that you want the returned range of cells to have. This parameter is optional.
- width is the width (number of columns) that you want the returned range of cells to have. This parameter is also optional.
So if you want to reference a cell that is 5 rows, 3 columns away from cell A1 in the dataset shown below, and you want the returned range to be 3 rows high and 2 rows wide, your OFFSET formula in Google Sheets would be:
=OFFSET(A1,5,3,3,2)
We have marked the returned range of cells in a red rectangle in the image below:
Examples Using the OFFSET Google Sheets Function
To understand how to use the OFFSET function, let us take a look at some simple examples of the OFFSET function with different parameter values. Consider the dataset shown below:
Example 1:
If you want to get the value that is 2 rows and 3 columns away from cell A1, then you can use the following formula:
=OFFSET(A1, 2, 3)
Notice we did not specify a height and column value here, so by default both height and width are assumed to be 1, and the function returns a single value, 893.21.
Example 2:
Let’s say you want the last value of the month of May, starting from cell A1. In that case, you can use the formula:
=OFFSET(A1, 5,4)
Example 3:
If you want to access a cell in the same column but different row, then simply set the second parameter of the OFFSET function to 0. For example, if you want to access the 3rd value of 2018, starting from cell C1, then you can use the formula:
=OFFSET(C1, 3,0)
Here’s a screenshot of results from applying the examples discussed so far:
Example 4:
If you want to access the last 2 cells starting from cell A1 (cells corresponding to June and July, 2019 and 2020, then you can use the formula:
=OFFSET(A1,6,3,2,2)
Here, we specified the width and height of the reference to be returned as 2. What we get in return is a 2 x 2 array.
If you wrap another function around this, then you can perform further operations on this range of cells.
For example, you can wrap a SUM function around this OFFSET function to get the sum of values in the returned range:
=SUM(OFFSET(A1,6,3,2,2))
Note that the OFFSET function simply extracts the range of values. So any operations performed on the returned cell references does not make any changes to the original values at these cell addresses.
Why Use the OFFSET Function in Google Sheets?
An obvious question that can come to mind is, why do we need an OFFSET function, when we can simply reference the cells directly by using their cell references?
Well, while direct cell references work great in most cases, there might be times when you might need to get dynamic access to certain cell ranges.
Another area where it can come in handy is when you don’t know the address of the cell you want to access, but you know how far it is from a certain other cell.
Let us take a look at some areas where the OFFSET function can be practically applied:
Using OFFSET Google Sheets Function to Get Dynamic Access to Cell Ranges
Consider the dataset below:
The SUM function at the right of the last row takes into consideration all the values of July from 2017 to 2020.
Now watch what happens when we try to add a new column before 2017:
The change does not get reflected in the sum because it does not take into consideration this new column that has been added. The sum still considers only the range from C8 to F8. This means every time we add a new column to the left of the first column, we need to explicitly change the range in the SUM function.
This can become quite impractical if you need to add a new column quite often. To circumvent this problem, we can wrap the SUM function around an OFFSET function as follows:
=SUM(OFFSET(A8,0,1):F8)
The above formula finds the sum of values from the range starting from 1 column to the right of cell A8, up to the cell F8. This means irrespective of how many columns get inserted after A8, the range for this SUM function will always start from the column to the right of A8.
Similarly, let’s consider another situation where you always want to find the average of the last N months of 2020. This value, N, will be specified in another cell, say, cell B10. Let’s see how we can make sure that irrespective of whether a row is added or deleted, we always get the average of the last N months.
=AVERAGE(OFFSET(F1,COUNTA(F:F)-B10,0,B10,1))
Thus, using the OFFSET function can be really helpful when the cell ranges you want access to are dynamic.
Using OFFSET When the Address of a Cell is not Previously Known
In the following dataset, let’s say you want to find the average of values corresponding to the month value in cell B10. In that case, it will not be possible to provide direct cell references inside the AVERAGE function, since the cell ranges will keep changing depending on the month value specified in cell B10.
In such cases, the OFFSET function can be quite helpful. To find our average dynamically, we can use the following formula:
=AVERAGE(OFFSET(A1,MATCH(B10,A2:A8,0),1,1,5))
Explanation of the Formula
The MATCH function in the above formula finds the index of the cell between A2 and A8 that contains the value in cell B10. If the value in B10 is Mar then the MATCH function here will return the index 3.
Now the OFFSET function takes this index value and returns a reference to the range of cells that starts 3 rows below cell A1 and 1 column to its right. The range consists of 1 row and 5 columns, so basically the AVERAGE function considers the range of cells marked in the red shaded box in the image shown below:
Now you will always get the average of the month specified in cell B11.
How Do You Use Offset and Match Functions Together?
There is a very long workaround to get this working. However, it’s much easier to just use the INDEX MATCH function for the same purpose. Our sister site has a great guide on how to use the INDEX MATCH function you can follow.
Important Points to Remember
Before wrapping up, here are some points that you need to keep in mind when using the OFFSET Google Sheets function:
- The OFFSET function returns a #REF! error if your requested offset extends beyond the edge of the worksheet, so always make sure you calculate your row and column offset values correctly.
- If any of your rows, columns, height or width have formulas that return a non-numerical value, you will get served with a #VALUE! error.
- The OFFSET function is a ‘volatile function’, which means that it recalculates every time there’s a change in your sheet. So, if you have too many OFFSET functions in your worksheet, it might end up slowing it down.
- You can use the offset function along with any other function that uses ranges, we’ve given examples of AVERAGE, and SUM, but you could also use SUBTOTAL for example.
In this tutorial we went over how you can use the Google Sheets OFFSET function to get more dynamic access to cells in your worksheet. We hope this has been helpful for you.
