Offset Function

When you want to select a cell (or range of cells), that is 2 rows down and 3 columns to the right from Cell B12, Offset Function lets you do it. Offset function helps you select a range of cells based on a reference cell and row/column information you provide.

This function may seem a little confusing therefore a little useless at first. But actually it is a real gem. Because with the use of Offset Function, you are able to interact with cells that you don’t know their address.

Here is the syntax of Offset Function:

=OFFSET(reference, rows, cols, [height], [width])

Reference is the cell you are using as starting point

Rows is the number of the rows you want to move from your reference cell (positive nr: downwards, negative nr: upwards)

Cols (columns) is the number of the rows you want to move from your reference cell (positive nr: to right, negative nr: to left)

Height (optional) is the height (in rows) of the range you want returned (can be a negative number).

Width (optional) is the width (in columns) of the range you want returned (can be a negative number).

As you can see in the example below, you can fetch value 6 by moving 3 rows downward and 4 columns to right from cell A1.

You can also fetch ranges with offset function. It is very useful for making charts, etc. with dynamic ranges. Check Dynamic Ranges with Offset Function to see how good it is.

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *