How to use the Excel Offset Formula to make excel refer to specific cells or ranges of cells based on inputs within the spreadsheet.
- For updated video clips in structured Excel courses with practical example files, have a look at our MS Excel online training courses . You can even try the Free MS Excel tips and tricks course.
- To see if this video matches your skill level (see the suggested skill score below) do our free MS Excel skills assessment.
- If you are based in South Africa look at the live courses we offer in Johannesburg and Cape Town.
Excel Offset Formula
The Excel OFFSET formula allows you to change the cell you refer to by moving the reference either up, down, left or right as you require. This can be done automatically and is very useful for cash flow projections.
In this segment you will learn how to activate and use this function.
In this example we have Sales figures for a five month period. But what we know is that these Sales don’t get paid until either a month or two later, and what we’d like to build is when the cash is likely to be received, but to build in the option that we can change how long it takes to get the money. This can be achieved by using the OFFSET function. To activate the OFFSET function,
you click on the cell,
activate the Function Wizard
and find the OFFSET function,
and click Ok,
The first question is, what is its original reference point, and that’ll be just in the same time period so
we’ll click up here
and then you can choose if you want it to move along rows, or do you want to move along columns, in this case we want it to look along columns
And what we want it to do is to look at this column, and depending on the payment terms get the column coupled back. So if in Month 1 there were sales of 100, and there’s a one month payment period we expect that Month 2 we’ll receive the money.
· so in this case it’ll be a negative,
· and then we’ll refer to this column
· and make it absolute
You can also tell the OFFSET function to combine a number of rows, if you put a number in the height
or a number of the columns, if you put a number in the width. However for these purposes we are just going to keep it to a simple OFFSET.
if you click ok
and copy it across,
what you’ll see is at the moment it refers in the same month
but if we say that it takes one month to receive payment
if you just watch here
you’ll see then it immediately, automatically updates the fact that sales in this month will only be paid here
and we can put a 2 here
and that’ll automatically update,
so Month 2 sales of 200, are only going to be received in month 4.