How to use the Offset Excel 2013 function to make excel refer to a cell or group of cells based on the contents of other cells.
- 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.
- - 06:17
- - Skill Level: 7
- - What's my skill level
Offset Excel 2013
The OFFSET Excel 2013 function allows you to move where Excel must look for a cell from numbers within the cell. The easiest way to explain it is with an example.
So here I have got some sales that occurred each month. However depending on my debtors days the actual cash may be received a little bit behind – so let’s say if we made this sale in month 1, we expect to receive the cash in month 2. Depending on what’s typed in here, we may want to go back two months.
So the offset function is fairly straightforward at this point. The first thing it asks you for is a reference point. So what it is saying is where must it go stand – so I am going to say just go and stand in that cell so long. The next two options, rows and columns is asking from where it is standing, how many rows must it move up and down and how many columns must it move left and right. In this case I don’t want it to move rows, so I am going to put a zero.
But the columns, what I want it to do is to look at the cell and if there is a zero there it must stand where we pointed it. If for example, we put minus one, it must look there -if we put plus one it must look there. So I am just going to say go and look at that cell and put dollar signs on it. We are going to leave the height and width for now – we will explain those just now. When I say OK, you’ll see it has pointed out 200, because what Excel has done it has gone, stood in that place, looked here and said Ok I’m going to move zero columns to the right or left and it stays there. If I go and put minus one, you’ll see it says 100 – let me just copy these across. So what you will notice now because of the way I have set it up, this cell here goes, stands in that cell, goes back one and gives me the answer from there. This cell over here – Excel stands in that cell, goes one to the left and gives me that answer there.
But now what I can do is what happens if we decide to increase our credit terms to two months, if I put a minus two in there – notice that this one here is a zero because these cash flows would be from prior months. Over here this R100 – Excel goes stands here, goes back 1, 2 and it pulls that number through. So sales we make here, only turn into cash over here. So just to emphasise what Excel is doing – I could have gone like this – equals that cell – and copied it across.Effectively this formula, the offset formula is doing the same thing. Because of the offset it knows that it must go look at cell D6. The benefit of the formula, however, is if someone changed this to minus one, offset immediately knows that it no longer looks two back, it looks one back. I will have to come here – go equals that cell and copy it across to get the same answer.So the offset command allows you to control where Excel looks based on the contents of cells.
Now to look at the height and width options in Offset. So in this example I have got sales, and depending on what’s in here, that’s how many month’s worth of sales I want to add up. So because there is a 2 there, I want it to add up and give me an answer of 300. But if I put a 5 there, I expect it to add up all of those cells. The way to do that is we create an offset – so I am going to go, create the offset. Reference point again – Excel saying where must I go stand. I am going to say please go start over there – stand there. It asks you for the rows and columns – in this case I don’t want it to stand elsewhere – I am just going to put a zero and a zero. Height would be the equivalent of up and down, width is left and right. In this case what I want is the left and right option. So I am going to say width and just point it there, and just put my command in. When I say OK, you’ll notice I get 100. Now 100 is the incorrect answer – we were hoping it would give us an answer of 300 – so why is it giving 100?
Well if I went like this – equals – and I highlighted those two cells and clicked ENTER, it’ll also give me 100. Why? Because what you have told Excel is go to those two cells – you haven’t told it what to do with it and at the moment it is just bringing back the first number it sees. So just like here, what we need to do is wrap a SUM around it. The offset, this whole function is effectively just saying D13 to E13 so we need to go and wrap a SUm or average or whatever else we are trying to do around it – when I click ENTER – it gives me 300. So now it knows to go highlight those two and add them up. If I put a 5 in, it gives me 1500. This is a particularly useful function for things like NPV, average, etc, where you can control how high – like that – or wide it must go. And because you can control both it is possible to highlight whole sections.