SheetFX
≡
<< Go back to all functions

OFFSET

The OFFSET function in Google Sheets is a useful tool to return a range reference that has been shifted by a specified number of rows and columns from a starting cell reference. Whether you need to dynamically adjust a range based on certain conditions or want to perform calculations on a specific range, the OFFSET function provides flexibility in your spreadsheet tasks.

Function Syntax and Parameters

Syntax: OFFSET(cell_reference, offset_rows, offset_columns, [height], [width])

Parameters:

  • cell_reference: The starting cell reference for calculating the offset.
  • offset_rows: The number of rows, up or down, to shift the range reference.
  • offset_columns: The number of columns, left or right, to shift the range reference.
  • [height] (optional): The height of the range to return. If not provided, the height defaults to the height of the reference range.
  • [width] (optional): The width of the range to return. If not provided, the width defaults to the width of the reference range.

Step-by-Step Tutorial

  1. Using OFFSET to dynamically adjust range:

    • Example: =OFFSET(A1, 2, 1)
    • Result: Returns a range reference that starts at a cell 2 rows below and 1 column to the right of cell A1.

  2. Using OFFSET to perform calculations on a specific range:

    • Example: If A1:B5 has a range of numbers, then =SUM(OFFSET(A1, 1, 0, 3, 2)) will return the sum of a range that starts 1 row below A1 and spans 3 rows and 2 columns.

Use Cases and Scenarios

  1. Conditional Formatting: Highlight a dynamic range based on specific conditions.
  2. Dynamic Chart Range: Adjust the range for a chart based on user input or changing data.
  3. Data Analysis: Perform calculations on a range that is determined by certain criteria.

Related Functions

  • INDEX: Returns the content of a cell in a specified range or array.
  • INDIRECT: Evaluates a text string as a valid cell reference and returns the content of that cell.

Related Articles