Search other Excel resources (AccountingWeb, Beancounters Guide, IT Counts):

Johnny Depp explains Excel OFFSET()

Introduction

OFFSET() is a very useful Excel function. It can allow you to choose a cell, or a block of cells, based on the contents of another cell. OFFSET() has two forms. The simpler form has three arguments:

• Starting cell reference

• Number of rows

• Number of columns

This allows you to return the value of a cell a given number of rows and columns 'offset' from the starting cell. So OFFSET(B4,1,1) would move one row down and one column right from the starting position of B4 to return the value in cell C5. Negative numbers move up and left and the numbers of rows and columns can come from references to other cells.

The more complicated form has five arguments and allows you to specify a rectangle based on a starting cell. This can be useful in creating dynamic ranges for aggregate functions such as SUM(). The five arguments are as follows:

• Starting cell reference

• Number of rows to top left corner

• Number of columns to top left corner

• Height in number of rows

• Width in number of columns

For example, this formula: =SUM(OFFSET(E8,0,1,2,3))

Would sum a rectangle starting at:

E8 plus zero rows, plus one column; so F8
and being 2 rows high and 3 columns wide; so F8:H9

You might have noticed that OFFSET() bears a striking similarity to pirate treasure map instructions, so here's an attempt to explain the function using a combination of Excel form controls and conditional formatting: