Excel Hints & Tips - The OFFSET Function

Let us assume the spread-sheet uses the standard accounts layout of figures for each month, so 12 columns of figures in all, for January to December. You want to indicate the current month with a number (1 for January, 2 for February etc) and have this month’s figure automatically displayed separately.

One approach is to use a very complicated IF statement:

IF(P1=1,C4,if(P1=2,D4,if(P1=3,E4 … and so on up to P1=12

However this is long-winded and complex and therefore invites errors. Our rule of thumb is if you get to three levels of Ifs, there is probably a simpler way to do it. And in this case there is a much simpler way.

The OFFSET Function

OFFSET is a neat little function which allows you to say, from any cell go 3 to the right and 4 down – or, better, use cell references. So to extract this month’s accounts:

=OFFSET(B4,0,P$1)

P1 is the number of the month. B4 is the column before the financial data starts, and the 0 indicates you don’t want to move down a row. So it will take the value of C4 when when P1 is 1, D4 when P1 is 2 and so on.