INSTRUCTOR: Welcome to this section of Mastering Excel,we're going to cover a topic that's extremely important.So far, we have used this spreadsheethere to calculate the profit for each month,and we calculated the total profit for the year.And so basically, we were using a formula that we copied down.And we said over and over again that the original formula
INSTRUCTOR [continued]: I used was here, and it was dealing with multiplyingand adding stuff on row 2.When I copy the formula down, Excel knows the relationship,and it knows that this formula comes from the row above.So when you paste it, it copies the relationship down,and it shifts it down to operate on row 3.So that relationship, that pattern matching,
INSTRUCTOR [continued]: is really what's so powerful with Excel.I can have very complicated formulas,and I could copy them around, and it'llkeep the same relationship between the numbers.So it's very good for tabular data,which is really what Excel is.Let me delete the Year to Date Profit column,because I'm trying to de-clutter the screen a little bit.What if I want to calculate the percent of--
INSTRUCTOR [continued]: I'll hit Alt-Enter-- yearly profit.Let me pull this out a little bit.What I mean by that is, for instance, in JanuaryI made $26.58.All right?And in February, I made $20.40.And I've already summed all of these things up,and I found out that my total profit is $293.04,
INSTRUCTOR [continued]: and that comes from a sum across all of these thingsthat we've already calculated.So this is a formula that's adding everything up.And I know the individual guys here.So my question is, what if I wanted to know what percentof my yearly total-- $293.04--what percent of my yearly total is the January sales?
INSTRUCTOR [continued]: Is it about 5% of the yearly total?Is it about 10%?Which one of these months has the highest percent?How do I calculate that?So I'm going to use a formula.I'm going to start it off, and I'mgoing to say, well, the way I calculate percentis part over whole, right?So in this case, I'm interested in January.And I'm going to divide by the total profit for that year.
INSTRUCTOR [continued]: All right?And that's going to give me a decimal.So let me go ahead and hit Enter there.And I've got 0.0907 blah, blah, blah.Right?Now, what I want to do to make thisinto a percentage is I'm editing the formula here,is I'm going to multiply by 100.And so it's 9.07%.All right, so what I've calculated
INSTRUCTOR [continued]: is that the January sales is basically 9%--9.07 and a bunch of change percent--of my yearly profit.And you could see that with the equation here.I take my monthly, I divide by the annual,and that's giving me a decimal.I then multiply by 100.That's going to give me what I want to see.So I'm like, this is a great thing here.
INSTRUCTOR [continued]: I want to go and calculate the same quantityfor February, March, and so on.And I should be able to find out what percentof my yearly profit each of these months is.So what I'm going to do is, I cando it a couple different ways.I can drag it down here with the fill handle.I can copy and paste the formula, whatever.In this case, I'm going to drag the fill handle down one spot.
INSTRUCTOR [continued]: And I have a major problem here.It does not do what I expect it to do.So I'm going to hover over it, and I'mgoing to look at the formula, and see what it's doing.Well, it's trying to take the February dataand it's trying to divide by G17.And that's my problem.See, Excel is pattern matching.It knows that the original formula comes from I2.
INSTRUCTOR [continued]: So when I shift it down, it's taking the two piecesof information that I used in that formula, whichwas this one and this one, and since I'm shifting the formuladown, it's assuming I want to shift all of the inputsto that formula down as well, much as we'vedone in the previous examples.So clearly, this is not quite going to cut it.
INSTRUCTOR [continued]: Because what's happening here is, in the original formulaand then in the pasted formula, it'swhat we call relative address.So whenever I copy and paste it, it'schanging the address to whatever it thinks it should be,based on the pattern.That's called a relative addressing in Excel.Now what I need to do is somehow force Excel to take
INSTRUCTOR [continued]: the February number $20.40 but always divide by $293.04.Now, I could type that in.I could type $293.04 here.Yeah, I could do that.But then that screws up the whole sheet.What if I change these numbers over here,then the total profit calculation will change,and it will not ripple through.So I really don't want to put hard numbers in.
INSTRUCTOR [continued]: What I need to do is get away from this relative addressing,and get into absolute addressing.So what I'm trying to tell Excel to do is take this number,and divide it by this number, but don'tuse relative addressing, use absolute addressing.So I can force it by putting a dollar sign in front of the Gand a dollar sign in front of the 16.
INSTRUCTOR [continued]: The dollar sign has nothing to do with money.So forget about that.It's just a symbol that Excel uses.When Excel sees dollar signs G dollar sign 16,it means that it's always going to use this value herefor this number here.And if I paste this formula anywhere else,it's always going to use G16, always.
INSTRUCTOR [continued]: So I hit Enter, I get the same answer.But when I copy it down here, now I get a real answer.If I double click, I can see what's happening.It's taking the $20.40 and it's dividing by the total profit,because I've anchored this value to always be basically G16.So now, I'm pretty confident.I can take and drag the fill handle down,
INSTRUCTOR [continued]: and I'm going to find out what my percent of yearly profit is.So this one here, you can see it's taking $57.63and dividing by the total profit, multiplying by 100.You go down here, and you can see the same thing happening.It's taking this cell and it's dividing by the total profit.The reason it's not shifting this downis because I'm not using a relative address here.
INSTRUCTOR [continued]: I'm using what we call in Excel a absolute address.I'm using an absolute address.That means that any formula, no matter where I paste it,it's always going to use the absolute address of G16.Now, notice that the first one here, this one--this is not an absolute address.This is relative.So Excel knows that when it copies these formulas around,
INSTRUCTOR [continued]: it's going to try to change the first one herebased on where it thinks it shouldbe, in terms of the table that you've set up.But this one here is always going to be anchored.And that is exactly what I'm alwaystrying to do in this case.One more thing I want to show you before I close this outis, I don't like all these decimal places here.
INSTRUCTOR [continued]: It just bugs me.So you can highlight these thingsand right click, and go to Format Cells down here.And I can do Number, and I can tell it two places.And so basically it's going to bea number that has two places after the decimal point.And I can change that to reflect anything else I want.But right now I can see that in January, itwas 9% of the profit.
INSTRUCTOR [continued]: Here in May was 19% of my yearly profit.So May is a really good year.Maybe I want to do some extra advertising in May, and so on.So that's how you're going to calculate and usethe difference between relative and absolute addressesin Excel.All right.One final thing I want to show you.I didn't mention it to you earlier.Notice we did our calculation, wesaid the profit for every green shirt was $1 65,
INSTRUCTOR [continued]: and we said it was constant for every month.We said the profit for every red shirtwas a constant for every month-- $1.44.So when we went back up here, and wecalculated our monthly profit, we used all relative addresseshere.And so whenever we copied this formula down,we had an entry for the profit every single month
INSTRUCTOR [continued]: along the way.So as we basically copied the formula down,then, for instance, when it's using this one,it's using this value of the profit.Well, this is a little bit cumbersome,having this same entry all over the place, because number one,it clutters up the worksheet.Number two, if I wanted to changethe profit for each red shirt, I'dhave to change all of these entries.
INSTRUCTOR [continued]: So that's kind of cumbersome.I didn't want to mention it to you earlier.But now that we know how to do absolute addressing,we can change this very easily.Let's delete all of these, and let's delete all of these.So now our spreadsheet goes crazy,because we've taken out all the computations for allthe monthly profit, and it's basically all disappeared.
INSTRUCTOR [continued]: So let's go over here to this monthly profit calculation,and let's see.We're multiplying the number of green shirts times the profit,plus the number of red shirts times the profit.But as we copy this formula down, let's see what happens.Let's just show you what happens.If we drag this fill handle down, what's going to happenis you're not going to get anything,
INSTRUCTOR [continued]: because there is a formula here now, but it's copyingand it's doing everything on row 3 now.So it's 8 times 0 plus 5 times 0.And when it gets down here, it's 2 times 0 plus 12 times 0.So since we're using that relative addressing,when we copy this formula down, it just uses blanks,and it just gets crazy.
INSTRUCTOR [continued]: So what we need to do is change this C2 to dollar signC dollar sign 2.And we'll change this E2-- this profit for the red shirt--to dollar sign E dollar sign 2.This is causing it to anchor to use the absolute address.So in other words, the number 10, or whateverquantity I am going to end up multiplying,
INSTRUCTOR [continued]: is always going to be multiplied by this value, $1.65,because I've anchored it into this absolute address.And the same thing's going to happen here.So let me hit Enter, right?And now I'm going to drag this fill handle.Well, let's just do it a little differently,since we've been doing the fill handle a lot.Let's copy it, let's select all of these to paste.We'll go in, I'll paste here.
INSTRUCTOR [continued]: And now everything seems to be correct again,because if I go examine this formula, noticewhat's happening.It's taking the quantity 8 times $1.65 because I've anchored it.In other words, when I copy this formula down,it does not try to move this value aroundlike we were doing before.If I go skip down here, it's taking
INSTRUCTOR [continued]: the 4 times the $1.65 plus the 4 times $1.44 and so on.So my monthly profit is exactly how it was before.My total profit is the sum of all this,so that's exactly how it was before.And my yearly profit is exactly the same as it was before.It's taking the monthly divided by the yearlyas an absolute address times 100.
INSTRUCTOR [continued]: So now my spreadsheet is functioning exactly howit was before, but now it looks cleaner,because I can see, OK, this $1.65 and this $1.44,that's my profit for each of these types of shirts.What if I get a better deal on these shirts,and now my profit jumps to $1.90 for the green shirt?Then my entire spreadsheet updates.
INSTRUCTOR [continued]: What if my profit for the red shirt--what if I get an incredible sale on the shirts,and now my shirts are netting me a profit of $2.05?And so since I've changed it to absolute addressing,I don't have to make changes in this entire columnlike I did before, right?So that is how you use, and as a practical reason
INSTRUCTOR [continued]: why you would want to use absolute addressing in Excel.Basically, most of the time you'reprobably going to use relative addressingwhen you're copying things.But if you have an anchored value that you'reusing for the entire worksheet, then youprobably want to go ahead and use an absolute address thereto force the formula to use the value--in this case, our profits--that you're going to be dealing with.
INSTRUCTOR [continued]: So it's going to depend on your application.But there's many, many cases whenyou would use absolute versus relative addressing.
Series Name: Mastering Microsoft Excel: Essential Skills
Publication Year: 2013
Methods: Practical skills
Segment Num.: 1
Jason Gibson explains how and why to use relative and absolute addressing in Excel.
Looks like you do not have access to this content.
Jason Gibson explains how and why to use relative and absolute addressing in Excel.