Arrow left
Back to product guides
Google Sheets Formulas

OFFSET Function in Google Sheets: Explained

In this article, you will learn what the OFFSET function is and how to use it in Google Sheets.

What is the OFFSET formula in Google Sheets, and why is it used?

The OFFSET returns a cell or a range moved by a certain number of rows and columns from a cell specified in the formula. This formula is beneficial when you want to get some information from a dynamic range or when you don’t know the address of the array you wish to refer to but know which cell the array starts from. We know it is hard to imagine how this function works in Google Sheets. Move on to the next section and learn how it works with examples.

How to use the OFFSET formula in Google Sheets

  1. Type “=OFFSET” or go to “Insert”“Function” (or directly navigate to the “Functions” icon)  “Logic”“OFFSET”.
  2. Define a starting point from which a reference range moves by the number of rows and columns you specify in other arguments.
  3. Specify how many rows and columns the starting point shifts from the original cell by.
  4. Determine the size of the array, which is returned by the OFFSET formula.
  5. Press the “Enter” key.
How to insert the OFFSET function in Google Sheets

The general syntax is as follows:

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

Cell_reference: This is the initial address from which a cell shifts by a certain number of rows and columns (specified in the following two arguments).
Offset_rows: This number should be an integer. The specified range moves by the number of rows defined in this argument.
Offset_columns: This argument needs to be an integer as well. The specified range makes a move vertically by the number of columns.
Height [Optional]: The height of the range.
Width [Optional]: The width of the range.

Note: “offset-rows” and “offset-columns” can be negative integers, but if the target destination is outside of the spreadsheet, the OFFSET formula returns “#REF!”. Also, the shifted range should not overlap the same size of the range starting from the starting points at all, as it causes a circular reference.

See the following examples of how the OFFSET function works in Google Sheets. In the first example, we will describe how the first argument (“cell_reference”) and the optional arguments (“height” and “width”) work because they are easier to understand. Then, we explain how the rest of the parameters (“offset_rows” and “offset_columns”) affect a return.

Example 1

How the OFFSET formula works in Google Sheets with an example

The arguments of this sample formula and their descriptions are as follows:

Cell_reference: B3 / Offset_rows: 0 / Offset_columns: 0

These three mandatory arguments mean that the starting point (cell B3) stays unchanged and spreads, and if the optional arguments are input, the specified range spreads from cell B3.

Height [Optional]: 3 / Width [Optional]: 4

How these two optional arguments work is visualized in the picture above. The green boxes illustrate how the “height” argument affects the output range, and the red boxes show how the “width” changes the output. As a result, the array with a 3x4 size starting from cell B3 is specified, and the range spreads from cell B11, where the OFFSET formula is inserted.

Example 2

Another example of how the OFFSET function works in Google Sheets

The arguments of this sample formula and their descriptions are as follows:

Cell_reference: A14 / Offset_rows: 2 / Offset_columns: 1

These three mandatory arguments mean that the starting point (cell A14) shifts to cell B16 as the “offset_rows” and “offset_columns” arguments command that the starting point moves down by two rows (visualized by the purple arrow and box) and right by one column  (visualized by the dark blue arrow and box).

Height [Optional]: 6 / Width [Optional]: 2

Due to these two optional arguments, the range with 6x2 size starting from cell B16 is specified, and the array spreads from cell B24, where the OFFSET formula is inserted in this example.

How do you use the MATCH function with the OFFSET function?

The OFFSET formula is often combined with other functions to keep the output brought by the OFFSET function dynamic. We introduce one of the combinations here. Assume you have monthly revenue data by company and want to pull out revenue for a specific company for a particular month (e.g., a revenue amount of “Potato” in “March 2022”). See the following screenshot to learn how to create a formula returning the information by combining the OFFSET function and the MATCH function.

How to use the OFFSET and MATCH formulas together

The arguments of this sample formula and their descriptions are as follows:

Cell_reference: B32 

Offset_rows: MATCH($C$39, B33:B37, 0) - this MATCH function defines how many rows the starting point goes down. The MATCH returns 2 as “Potato” is in the second row in the selected range in the formula.

Offset_columns: MATCH($C$40, C32:H32, 0) - the second MATCH formula determines the number of columns by which the initial point shifts. This MATCH gives 3 because “Mar 2022” sits in the third column in the chosen range in the formula.

In summary, these three mandatory arguments say that the origination point (cell B32) shifts to cell E34 (B+”3”=E and 32+”2”=34) as the “offset_rows” and “offset_columns” arguments command that the starting point moves down by two rows (visualized by the purple arrow and box) and right by one column  (visualized by the dark blue arrow and box).

Note: The “height” and “width” arguments are blank and considered zero.

In this example, the OFFSET is used to look up a specific value. The combination of the INDEX and MATCH functions is also often used for this purpose. Check this article to learn how to create a dynamic search: How To Create Dynamic Dashboards with Index/Match. If you are interested in other formulas to pull out specific information, read the following articles.

Other formulas related to look-up

If you don’t get the right answer to your question in this article or you have other questions on lookup formulas, you may find the answers in the following articles.

How to Use VLOOKUP in Google Sheets

How to Use HLOOKUP in Google Sheets

XLOOKUP - Google Sheets: Explained

INDIRECT Function in Google Sheets: Explained

How to Use INDEX Function in Google Sheets

How to Use MATCH Function in Google Sheets

XMATCH in Google Sheets: Explained

Analyze your live financial data in a snap

Are you learning this formula to build a financial KPI dashboard? If so, LiveFlow may help you to automate manual workflows and keep numbers updated in real time. For instance, you can easily compare your Budget versus Actual without any manual work. Furthermore, you can access other templates here, from the simple Income Statement and Operational KPI templates to Multi-Currency Consolidated Income Statement, Balance Sheet, and Cash Flow templates. You can also customize these templates as you want without breaking the automated data inflow.

To learn more about LiveFlow, book a demo.

Automate any custom financial dashboard in Google Sheets with LiveFlow

Learn how to do this step-by-step in the video below 👇

Do you need personal help?

Our team of real people are here to help you any time between 9am and 10pm GMT.
Check Icon
Email us at: founders@liveflow.io
Check Icon
Call us at +1 (415) 650-1711