# Documentation/Calc Functions/OFFSET

TDF LibreOffice Document Liberation Project Community Blogs Weblate Nextcloud Redmine Ask LibreOffice Donate

## Function name:

OFFSET

## Category:

Spreadsheet

## Summary:

Returns the value of a cell offset by a certain number of rows and columns from a given reference point.

## Syntax:

OFFSET(**Reference**; **Rows**; **Columns**[; **Height**[; **Width**]])

## Returns:

Returns the content of the cell(s) whose reference's position and dimension are modified.

## Arguments:

**Reference** is the reference from which the function searches for the new reference.

**Rows** is the number of rows by which the reference was corrected up (negative value) or down. Use 0 to stay in the same row.

**Columns** is the number of columns by which the reference was corrected to the left (negative value) or to the right. Use 0 to stay in the same column

**Height** is the vertical height for an area that starts at the new reference position.

**Width** is the horizontal width for an area that starts at the new reference position.

Arguments **Rows** and **Columns** must not lead to zero or negative start row or column.Arguments **Height** and **Width** must not lead to zero or negative count of rows or columns.

- If
**Row**and/or**Column**leads to a negative or 0 start then the function returns a (#REF!) reference error.

## Additional details:

- If the
**Width**or**Height**is included, the OFFSET function returns a range and thus must be entered as an array formula. If both the**Width**and**Height**are missing, a cell reference is returned.

## Examples:

Formula | Description | Returns |
---|---|---|

=OFFSET(A1;2;2) where C3 contains 100 | The function returns the value in cell C3 (A1 moved by two rows and two columns down). | 100 |

=SUM(OFFSET(B2:C3;1;1)) where C3:D4 contains 1,2,3,4 | The function returns a reference to B2:C3 moved down by 1 row and one column to the right (C3:D4). Further the function sums up all the values. | 10 |

=OFFSET(B2:C3;-1;-1) where A1:B2 contains 1,-1,1,-1 | The function returns a reference to B2:C3 moved up by 1 row and one column to the left (A1:B2). Further the function sums up all the values. | 0 |

=OFFSET(B2:C3;0;0;3;4) passed as an array function | The function returns a reference to B2:C3 resized to 3 rows and 4 columns (B2:E4). | (Contents of B2:E4 array are displayed) |

=SUM(OFFSET(A1;2;2;5;6)) where each cell in C3:H7 contains 1 | The function determines the total of the area that starts in cell C3 and has a height of 5 rows and a width of 6 columns (area=C3:H7). | 30 |

=OFFSET(B2,-2,-2,4,4) | The function returns a reference error since a non-existant cell occurs in the range. | #REF! |

## Related LibreOffice functions:

## ODF standard:

## Equivalent Excel functions:

OFFSET