# Functions that make your file DYNAMIC: Part Two: The OFFSET function

In the first part we discussed the INDIRECT function. In this second part, we discuss the OFFSET function.
The function of this OFFSET is to return a reference to a range that is a specified number of rows and columns from cell or range of cells.
This function is not easy to create because of the number of arguments involved.

## 1 Syntax

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

The breakdown of this is:

• reference indicate the starting point, supplied as a cell reference or range
• rows indicate the number of rows to offset below the starting reference
• cols indicate the number of columns to offset to the right of the starting reference
• [height] (optional) indicates the height in rows of the returned reference
• [width] (optional) indicates the width in columns of the returned reference

The first 3 arguments are required and the last 2 are optional. All the arguments can be references to other cells or results returned by other Excel formulas.

## 2 How does this formula work?

Think of it as a GPS:
You can give it a starting point and then you tell it to:

• How many rows to go down
• How many columns to move across
• And what range you want returned

Visualizing this, you would get the situation in figure 1.

## 3 Example

Next, I will discuss this deeper with the practical example we used for the INDIRECT function (see below).

Suppose you want that column H (from cell H5 till cell H13) is referring to the width of the following car (car 2 till car 10). You could use a static formula, namely H5=B6 (ceteris paribus =B7 .. B14).

Suppose you want to insert a row under car2. You will get, next situation.

Now, the cell H6 does not relate to B7 but to B8. I have a formula which result I do not want. I am interested in the row below the current row! The static function H5=B6 (ceteris paribus =B7 .. =B14) is not robust enough for me. Here, the best solution is to use the OFFSET function.

The formula for this solution is =OFFSET(B5;1;0).
The breakdown of this is:

• B5 is the reference is set to the first row
• 1 is indication that there is 1 movement shift down of rows
• 0 is indication that there are no movement columns

When we now insert in new row below car 2, we will get our obtained result.

The OFFSET function allows you to make more robust spreadsheets in a situation where people might insert rows or columns in the middle.