In this Google Sheets tutorial I will explain you how to use Google Sheets Offset Function with the help of some Offset Function Examples.
The Offset Function in Google Sheets can use to return a shifted range reference from a starting cell reference. You can use offset to bring dynamic ranges in formulas.
OFFSET(cell_reference, offset_rows, offset_columns, height, width)
Normally I am not paying much attention to the syntax part of functions as it can confuse a newbie. But this time, the syntax is pretty easy to understand.
I hope I can easily explain you what is OFFSET function in Google Sheets with the help of below image. Before that let me tell you one more thing. In the above syntax, height and width elements are optional.
How to Use Offset Function Google Sheets
The below screenshot will help you to clearly understand the elements in the OFFSET function one by one.
First see the above image and compare it with the formula used.
Syntax: OFFSET(cell_reference, offset_rows, offset_columns)
The above formula refers to cell C8. Here From cell reference A1, 7 rows set to offset and then 2 columns to offset. Follow the above arrow mark and you can understand it.
Though height and width elements are optional, in most of the cases it is a must to make dynamic ranges.
Now let us use height and width elements with the above formula.
See the image below. Here also, I am going to use the above same formula but only adding the height and width elements. So I will explain that part only below.
I have already explained the first part of the formula, that without height and width elements, just above this image. There I told you the formula refers to cell C8. Now in the formula below, 3 is height and 4 is width. That I’ve clearly marked in the image.
Syntax: OFFSET(cell_reference, offset_rows, offset_columns, height, width)
Learn the use of OFFSET function thoroughly before moving to below OFFSET function examples.
Use of OFFSET function in Google Sheets
What is the use of Offset function in Google Sheets?
Google Sheets OFFSET function brings dynamism to ranges. To understand that you should follow all the examples below. There is a special section below that details dynamic ranges in Google Sheets using OFFSET.
Offset Function Examples in Google Sheets
If you have learned the above basic use of OFFSET function, you can now move to the below awesome OFFSET function examples.
Offset Function with SUM function in Google Sheets
See the below example to understand how can you replace ranges in SUM function with OFFSET function.
From the above example, you can understand different formations of offset function together with SUM function. But now to the killer part of OFFSET function with SUM. Find it below.
Dynamic Ranges Using Offset Google Sheets Function
How to update formula in Google Sheets when inserting rows at the end of data or beginning of data?
See the below offset formula.
When this formula in use, any new rows inserted in the beginning of data will be included in the sum. See the image below. With normal SUM function, this dynamism is not possible. You may need to change the formula to include the additional rows.
We can change this formula little bit to make it a complete dynamic formula. This time you can insert rows at the bottom or end of the data. The changes will automatically reflect in the result. See that formula below.
OFFSET Function with SUBTOTAL Function in Google Sheets
You can use OFFSET function dynamically with most of the Google Sheet functions that can take ranges in the formula. See one example below.
Use the above formula to find average in Google Sheets. Here I just change the SUM with “Subtotal(101,” which is equal to average Google Sheets function.
I used “Subtotal(101,” instead of average function in Google Sheets. Subtotal with functions numbers are one of the very useful function in Google Sheets. Subtotal function can replace 11 other Google Sheets functions with function numbers and very useful to deal with hidden rows.