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

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.

Figure 1: Offset – GPS visualization

3 Example

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

Figure 2: Car specifications

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).

Figure 3: Car specifications – Static formula

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

Figure 4: Car specifications – situation after inserting row

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.

Figure 5: Car specifications – Dynamic formula – solution

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.

Figure 6: Car specifications – situation solution after inserting row

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