width<\/b> is the width of the array you want to return. The default is 1<\/li>\n<\/ul>\n[\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
How to use the Excel OFFSET function<\/h3>\n
Now I\u2019ll show the function in action. Take a look at the example below.<\/p>\n
I have some data from a clothing store that indicates how many of each item has been sold, per size. I use the OFFSET function to reference data within this table.<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/03\/1.-OFFSET-example-1.png” alt=”Excel OFFSET function 1″ title_text=”1. OFFSET example 1″ align=”center” _builder_version=”4.16″ _module_preset=”default” width=”640px” max_width=”640px” height=”440px” max_height=”440px” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
As you can see, I type the function =OFFSET(A1,4,2)<\/b> to pull the data I want from the table. In this case, I start with a reference cell of A1, then tell the function to move 4 rows down and 2 rows across. As a result, it performs that offset and returns the results from cell C5.<\/p>\n
If I want to get the sales of large T-shirts, I can use the formula =OFFSET(A1,2,3)<\/b>. This function is the same as in the previous case, except I tell the function to offset 2 rows down and 3 columns to the right of cell A1.<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/03\/2.-OFFSET-example-2.png” alt=”Excel OFFSET function 2″ title_text=”2. OFFSET example 2″ align=”center” _builder_version=”4.16″ _module_preset=”default” width=”640px” max_width=”640px” height=”440px” max_height=”440px” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Notice how I didn\u2019t input the two optional parameters, [height] and [width]. This is because I only want to return 1 cell, so I don\u2019t have to input anything for these since they default to 1.<\/p>\n
If I wanted to return multiple cells however, I would specify the dimensions of the desired output.<\/p>\n
[\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Using OFFSET with other functions<\/h4>\n
The OFFSET Excel function can also be combined with other functions.<\/p>\n
In this example, I combine OFFSET with the SUM function to get the total sales of Large clothing.<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/03\/3.-OFFSET-Sum-example.png” alt=”Excel OFFSET function 3″ title_text=”3. OFFSET Sum example” align=”center” _builder_version=”4.16″ _module_preset=”default” width=”640px” max_width=”640px” height=”440px” max_height=”440px” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
The formula here is =SUM(OFFSET(A1,2,3,7,1))<\/b>. This time, I input 7 for the height parameter. This makes the function return the range D3:D9, which encompasses all the large items. It then adds them all up with the SUM function.<\/p>\n
If you\u2019re still confused about this example, here\u2019s what we tell the function told to do: start at cell A1, then move 2 rows down, 3 rows across, and return the results from an array of 7 (height) by 1 (width).<\/p>\n
Finally, the SUM function then adds up this array (D3:D9).<\/p>\n
[\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Using OFFSET to create a dynamic range<\/h4>\n
One of the main uses of the OFFSET function is to create a dynamic range. A dynamic range is a range that updates automatically when new data is added.<\/p>\n
Take a look at the example below. Here, I use the OFFSET function to create a dynamic range of the entire table that will update if new data is inserted.<\/p>\n
Lastly, I combine this with the AVG function to get the average sales per item.<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/03\/4.-OFFSET-dynamic-range.png” alt=”Excel OFFSET function dynamic range 4″ title_text=”4. OFFSET dynamic range” align=”center” _builder_version=”4.16″ _module_preset=”default” width=”640px” max_width=”640px” height=”440px” max_height=”440px” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
This is achieved with the formula =AVERAGE((A2:OFFSET(D10,-1,0,))<\/b>). I use the OFFSET function to identify the lower bound of the range I want to average.<\/p>\n
The range goes from cell A2 (fixed) to one cell above D10. The benefit here is that if I insert a new row, the function will automatically update:<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/03\/5.-OFFSET-dynamic-range-updated.png” alt=”Excel OFFSET function 5″ title_text=”5. OFFSET dynamic range updated” align=”center” _builder_version=”4.16″ _module_preset=”default” width=”640px” max_width=”640px” height=”440px” max_height=”440px” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
The formula here is =AVERAGE((A2:OFFSET(D11,-1,0,)))<\/b>. The OFFSET updated from D10 to D11, which kept the entire table included in the average calculation. I didn\u2019t change the formula \u2013 I just added a new row (shoes) and the function updated automatically!<\/p>\n
I hope you now have an understanding of how and when to use the OFFSET function!<\/p>\n
[\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
OFFSET function<\/h3>\n
And there you go! Use the OFFSET function to return a reference range of cells that are offset from a reference cell.<\/p>\n
If you\u2019d like to learn more about the various formulas of Google Sheets, why not take a look at our blog post on the ARRAY_CONSTRAIN formula in Google Sheets.<\/span><\/a><\/p>\nAlternatively, check out related blog posts below!<\/p>\n
[\/et_pb_text][\/et_pb_column][\/et_pb_row][\/et_pb_section]<\/p>\n","protected":false},"excerpt":{"rendered":"
The Excel OFFSET function returns a reference range of cells that are offset (hence the name) from a reference cell. In other words, you specify the offset in terms of the number of rows and columns, and you can also indicate the size of the array that you want to reference.Syntax=OFFSET(reference, rows, cols, [height], [width]) […]<\/p>\n","protected":false},"author":40,"featured_media":32139,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"_et_pb_use_builder":"on","_et_pb_old_content":"","_et_gb_content_width":"","footnotes":""},"categories":[65],"tags":[39,48,28],"class_list":["post-32137","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-excel-functions","tag-connections-t","tag-excel","tag-spreadsheets"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/posts\/32137","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/users\/40"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/comments?post=32137"}],"version-history":[{"count":0,"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/posts\/32137\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/media\/32139"}],"wp:attachment":[{"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/media?parent=32137"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/categories?post=32137"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/tags?post=32137"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}