Using the OFFSET function in Microsoft Excel (Great for budgets)

The OFFSET function is incredibly useful, especially when looking up budget information. It allows you to look up data based upon its position relative to another cell.

The format is:

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

[arguments in square brackets are optional]


where:

reference is the cell reference from which you wish to calculate the offset
rows is how many rows you wish to count down (up if negative) from reference
cols is how many columns you wish to count to the right (left if negative) of reference
height is the height of the range returned (1 if not entered)
width is the width of the range returned (1 if not entered)

To use this to look up budget data in management accounts:

Where your 12 months budget figures are in cells E5 to P5, and your current period number is stored in cell A5, you can use the formula =OFFSET(E5,0,A5-1) to return the current month's budget.

To return the cumulative budget for the current month, you would use the optional arguments along with the SUM function - i.e. =SUM(OFFSET(E5,0,0,1,A5)). This returns a the sum of the range that starts at cell E5 (month 1), has a height of 1 row and a width of the number of months to date.

Have a play with this function, see what you can do with it. Also, let me know in the comments below any other uses you have for this function.

If you enjoyed this post, go to the top left corner of the blog, where you can subscribe for regular updates and your free report.