If you want to offset multiple cells, you also indicate the size of the range of cells<\/strong> using height and width parameters.<\/p>\n Offset is a handy function that allows you to use dynamic ranges<\/strong> in formulas. This means that the function will adapt to changes in the spreadsheet, such as when you add new data or columns.<\/p>\n [\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.16″ border_width_left=”4px” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n =OFFSET(cell_reference, offset_rows, offset_columns, [height], [width])<\/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 [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/10\/OFFSET-function-1.png” alt=”OFFSET function 1″ title_text=”OFFSET function 1″ _builder_version=”4.16″ _module_preset=”default” 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 Here, I have typed some data into the red range of cells.<\/p>\n I am using the OFFSET function to reference that data in the gold range of cells, by typing the formula =OFFSET(B2,6,4,3,5)<\/b>.<\/p>\n [\/et_pb_text][et_pb_text admin_label=”Connections T – One tool – Horizontal” module_class=”sheetgo-post-no-shadow-img md2-contained-button vertical-banner-container” _builder_version=”4.27.4″ _module_preset=”default” background_color=”#f2f7ff” max_width=”700px” module_alignment=”center” max_height=”300px” custom_margin=”20px|0px|20px|0px|true|true” custom_padding=”25px|25px|25px|25px|true|true” sticky_limit_bottom=”section” border_radii=”on|20px|20px|20px|20px” border_width_all=”1px” border_color_all=”#d9e7ff” global_colors_info=”{}” global_module=”255434″ theme_builder_area=”post_content”]<\/p>\n One tool to merge, split, and filter all your spreadsheet data<\/span><\/p>\n Find out how<\/a><\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n As is the case with all functions and formulas, the best way to understand OFFSET is to see it in use.<\/p>\n Below is some fictional sales data for a few employees across several business quarters.<\/p>\n As you can see, I have used a different OFFSET function in each row:<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/10\/OFFSET-function-2.png” alt=”OFFSET function 2″ title_text=”OFFSET function 2″ _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n If you use the OFFSET function directly, like I did in rows 2 and 3, it directly returns the range of cells and displays them across the spreadsheet. Therefore, for the case in row 3, the function displays the output in cells H3 to K3, because I specified that I wanted the range to have a width of 4<\/strong>.<\/p>\n You can also use the output range reference as an input to another array function, similar to what I did in rows 4 to 9.<\/p>\n If you reference a range of cells that are before the first row <\/strong>or the first column, <\/strong>it\u00a0will result in a #REF! error. You can see this in row 10, where I tried to reference a cell above A1 (which obviously doesn\u2019t exist).<\/p>\n One of the main benefits of using the OFFSET function is that it allows you to create dynamic ranges. These are ranges that automatically adjust when you add new data to the spreadsheet.<\/p>\n To illustrate this, I\u2019ve taken a subset of the data from the previous example, and I\u2019m using it to get the average Q1 sales for the salespeople shown.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/10\/OFFSET-function-3.png” alt=”OFFSET function 3″ title_text=”OFFSET function 3″ _builder_version=”4.16″ _module_preset=”default” 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 Instead of just using a typical AVERAGE function, I\u2019ve combined it with the OFFSET function by typing =Average(B2:Offset(B6,-1,0))<\/strong>.<\/p>\n I am using the OFFSET function to define the range I want to be averaged. Here that is the array in cells B2 to B5<\/strong>.<\/p>\n However, instead of typing B5<\/strong>, I offset it, essentially saying \u201cone cell above B6\u201d. Doing this creates a dynamic range that will update if I add or remove data.<\/p>\n You can see this in the example below. Here I have added another salesperson, Albert<\/strong>, and the average sales updates automatically \u2014 thanks to the dynamic range.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/10\/OFFSET-function-4.png” alt=”OFFSET function 4″ title_text=”OFFSET function 4″ _builder_version=”4.16″ _module_preset=”default” 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 You will notice that the formula has adjusted automatically. Instead of showing B6<\/strong> in the OFFSET function, it is now using cell B7<\/strong> instead.<\/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 More often than not, you may come across instances where the OFFSET function output overlaps with the offset target, resulting in a circular reference.<\/p>\n In such scenarios, Google Sheets returns a #REF! error.<\/p>\n I’ve demonstrated this in the snapshot below with colored borderlines. You\u2019ll notice that I highlighted the possible overlap in red borders and yellow fill.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/10\/OFFSET-function-5.png” alt=”OFFSET function 5″ title_text=”OFFSET function 5″ _builder_version=”4.16″ _module_preset=”default” 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 That’s how to get started with Google Sheets OFFSET function. Looking for more spreadsheet tutorials and tips? Check out our other posts below!<\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.22.2″ background_color=”#f9f9ff” custom_margin=”40px||40px||false|false” custom_padding=”15px|25px|15px|25px|true|true” border_width_left=”3px” border_color_left=”#808e95″ saved_tabs=”all” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Editor\u2019s note<\/strong>: This is a revised version of a previous post that has been updated for accuracy and comprehensiveness.<\/em><\/p>\n [\/et_pb_text][\/et_pb_column][\/et_pb_row][\/et_pb_section]<\/p>\n","protected":false},"excerpt":{"rendered":" The OFFSET function in Google Sheets references a range of cells that are shifted or\u00a0displaced from a starting reference cell. You specify this displacement by a number of rows and columns. In other words, OFFSET references a range that is a specific number of rows and columns away from the starting cell. If you want […]<\/p>\n","protected":false},"author":40,"featured_media":29339,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"_et_pb_use_builder":"on","_et_pb_old_content":"The OFFSET<\/a> formula in Google Sheets gives us the reference to a range of cells displaced from a starting reference cell. We specify the displacement in terms of a number of rows and columns. And, we indicate size range of cells using the height and width parameters.\nSyntax<\/h3>\n
\n
![\"\"](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2024\/09\/connections-top-processors-main.webp\")
<\/p>\n![\"39\"](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2024\/09\/Recommended-for-Google-Workspace-badge.webp\"){kind=icon}
<\/p>\nHow to use the OFFSET function<\/h3>\n
How to use OFFSET to create dynamic ranges<\/h3>\n
#REF! Error \u2013 Circular reference<\/h3>\n
Syntax<\/h3>\nOFFSET(cell_reference, offset_rows, offset_columns, [height], [width])<\/span><\/strong>\n
\n \t
![\"OFFSET](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/11\/offset1-1.png\")
\n\n\u00a0\nUsage: OFFSET Formula<\/h3>\nAs is the case always, practically applying the formula clears a lot of things up. Below is a fictional sales data of a few employees across quarters. So, let us try our hands on it. Please observe the behavior in all the cases in the snapshot below.\n\n
![\"OFFSET](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/11\/offset2.png\")
\n\nIf we use the OFFSET formula directly, like we did in the row # 2 and 3, it directly returns the range of cells and displays them across. Therefore, for the case in row # 3, the formula displays the output from H3 to K3.\n\nAlso, we can use the output range reference as an input to another array formula. Similar to what we did in the rows 4 through 9.\n\nIt is noteworthy that referencing a range of cells that are before first row or the first column, can result in an #REF! error. We illustrate this in the example on row # 10.\n#REF! Error - Circular reference<\/h5>\nMore often than not, we may come across instances where the OFFSET formula output may overlap with the offset target, resulting in a circular reference. In such scenarios, Google Sheets returns an #REF! error. We demonstrate this diagrammatically in the snapshot below with colored border lines. You'll notice that we highlighted the possible overlap in red borders and yellow fill.\n\n
![\"OFFSET](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/11\/offset3.png\")
\n\n\u00a0","_et_gb_content_width":"","footnotes":""},"categories":[54],"tags":[39,28],"class_list":["post-3448","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-google-sheets-formulas","tag-connections-t","tag-spreadsheets"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/posts\/3448","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/users\/40"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/comments?post=3448"}],"version-history":[{"count":0,"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/posts\/3448\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/media\/29339"}],"wp:attachment":[{"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/media?parent=3448"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/categories?post=3448"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/tags?post=3448"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}