Welcome to Highline Excel 2013 class video number five. If you want to download this week two workbook and follow along, click on the link below the video, We're going to start on the sheet cell references. Yes, cell references, one of the most important topics in Excel. Now on this sheet here, if you CTRL + P, it has two pages of notes of the four basic cell references. Now, in the next couple of videos, we'll see that there's the eight different types of references. Now, if I CTRL + roll and zoom in, most people know the difference between relative and absolute cell references. In the prerequisite for this class and already in this class we've talked about these, but the real power with cell references comes from learning mixed cell references. Now in some ways, learning mixed cell references, it's the dividing line between really knowing Excel and then not knowing Excel so well. So we're going to learn about it, actually in our prerequisite Business 216, we had an introduction to it. Alright, we're going to start off with some examples, I'm going to go over to the (CR1) and we're going to do relative and absolute, a couple examples and will jump into mixed cell references. Right so here we have a column and we just need to calculate total revenue minus expense. Now this is a formula, equals, left arrow, left arrow, minus, left arrow. Now this is a relative cell reference. All the way down, this formula needs to always look two cells to my left and subtract one cell to my left, so when I CTRL + Enter, and double click and send it down, I go to the last one and hit F2, I can see sure enough that's not B2 minus C2, its two cells to my left minus one cell to my left. Now, notice we put the same formula into all the cells. An alternative to putting the formula in the cell and double clicking and sending it down, is if you already have the range highlighted, in the active cell, you build a formula, and then to populate whatever's in the active cell into all the cells, you hold CTRL and tap Enter. Alright, let's go take a look at our next example, (CR2). Now, here we have prices. We have some prices for these products, 40% markup on sell price, 50%, 60% and we need a new price increasing by 5%. So here we're going to build a formula and then copy it down and over. So the same formula will go into all the cells. Equals, well for this column, I need all of the 40%, in this column, I need all the 50%, so it's simply going to be a relative cell reference. As I copy this way, it'll move from 40, to 50, to 60, as I copy down, it'll move to the next product. And I'm going to have to multiply it by 1.05, that will give us a 5% increase for all these prices. Now, this needs to be locked in all directions, I'm going to hit F4. CTRL + Enter, double click and send it down. This is a two-step process when you go down and over, there's no way too simply grab the fill handle and go over and down at the same time. You have to either go down and let go, grab the fill handle and drag over, or, CTRL + ZZ, you can drag horizontally, let go and then double click the fill handle and send it down. Now instead of doing that two-step process, if we had highlighted it, in advance, in the active cell we could say Equals, relative cell reference, three to my left, times, absolutely B11, I'm going to hit F4, now I can populate the entire highlighted range using CTRL+ Enter. I go to the last cell and hit F2, perfect. Relative cell reference, times that 1.05. Now let me show you another little trick here. Notice we went Equals, relative cell reference, three to my left, times absolutely B11, F4 to lock it, CTRL + Enter to populate. Now I really want to go over to the corner to check it by putting it in edit mode, Well, there's a keyboard to jump between the corners of a highlighted range its CTRL + Period. So, CTRL + Period jumps and this is nice if you have a huge data set, but even here, I can CTRL + Period, Period and then F2, and see my handiwork. Relative cell reference three to my left, times absolutely B11, Escape Let's go look at our next example. Let's click on (CR3). We have Sales Rep in one column, their sales in the second column, and we need to calculate their earned commission in the third column. For each sales rep we say Equals, relative cell reference one cell to my left, times, locked on our commission rate. The B2 is relative right now but when I hit the F4 it puts two dollar signs in, one in front of the column, one in front of the row, that means it will be locked. CTRL+ Enter, double click and send it down, CTRL+ down arrow, and I'm going to hit the F2 key, and sure enough, you can see relative reference, times, still locked on B2. Escape, CTRL+ Home. Now let's go look at our next example, (CR4). Here we want to see how to lock a range and have an absolute cell reference. So we have some sales reps here, and we have a column of Sales Reps and we need to add their total sales. So, I'm going to use the SUM IFS. Now IF is great, only one condition, but IFS, this is 2013 we're using, since 2007, that means three versions, 2007, 2010, 2013. SUM IFS has been around, and it is great because it'll do one or more criteria or conditions, and the screen tip is much more polite. SUM range, click in the first cell on that Sales column, CTRL + Shift + down arrow, F4, comma, the criteria range, I go over to the Sales Rep and CTRL + Shift + down arrow, F4, and then the criteria. Two absolute ranges and a relative cell reference, CTRL + Enter and it will actually go through this column and add only Tina's sales. Now, if you're learning this for the first time, sometimes it seems kind of mysterious how it does this, but let's try a trick. Let's see if we can do this manually, I mean we're learning on a small data set here, I'm going to click in Tina and then I'm going to use the CTRL key, and only click on the rest of Tina sales. I'm using the CTRL key to highlight things not next to each other and then I'm going to look down here in the status bar. $2641.48, So the status bar is a great trick to verify. Now we verified that Tina's number was correct, but let's come to the last cell and hit F2, and verify that our formula is correct. It looks like we've got absolute cell reference, absolutes cell reference and relative cell reference there, so that's looking good. Alright, now we're going to zoom ahead a few sheets, to sheet (CR6) and introduce ourselves to... Actually, we're going to stop on (CR5) first, we'll do (CR6) next. We're going to introduce ourselves to mixed cell references. Now, here's the situation, we have--- we're going to build a budget here. We have January through June revenue. That's at the head of each column, but we need to calculate our expenses for each month. Here's the expenses, down here in the assumption table, where our formula inputs are, here are our labels, our expenses, and here's the percentages, for budgets. The accounting department looked at past data and estimated that cost of goods sold are 25.5% of revenues, office expense are 4.5% of revenues, and the rest of the percentages here. So we can use these percentages in budgets, We've already estimated the revenue and so now we simply take the revenue number, times each one of these percentages and that'll give us our estimate for expenses. Now, here's the way most people would create this, they'd say "I need the revenue, but I need to lock it 'cause I'm going to copy down and every cell needs the same revenue, and then you'd say Times, and you'd click on the percentage." Notice that's a relative cell reference, so as I copy it down, it'll work perfect. So, when I get to the last one, and hit F2, sure enough, Depreciation Expense is being calculated using the depreciation percentage. Then you come to the next column, Equals, locked on the revenue for February, F4, times, and I get my percentage again. Drag it down. Now the problem with this is that you would have to create, in our case, six formulas for 12 months, 12 formulas, that's 12 individual formulas, Well here's the thing. If you know mixed cell references, you only have to create one formula and copy it down, and over, and you're done. So that means we can create this block of formulas here six times faster for this half year budget, if we know mixed cell references. Alright you ready? Now before we create our formula, we do want to note, you cannot use mixed cell references to reduce the time it takes to create all your formulas, unless the labels in the table, that's where the table or the formulas are going to be, and the labels down in your assumption table are orientated the same direction, vertical, vertical that's the only way you're going to get mixed cell references to work. Now, I put this down here, if you had your assumption table like this, forget it. The only way you can do it, is to put an absolute and relative cell reference in each individual column and copy it down. Vertical, vertical, then you can use mixed cell references. Vertical, horizontal, forget it, you're not going to do it. The fastest way you can do it, if you use this assumption table is six individual formulas. Alright, so here, let's build our base formula. We're going to say the revenue times the percentage. This is the expense as a percentage of revenue. Now, the trick for mixed cell references is you have to look in each cell reference and we have two of them in our formula. You have to look at each individual cell reference and ask two questions: Alright, so we'll start with this blue one. Hey, when I copy the formula down,
Do I want it locked on the January revenue or do I want it to move relatively? Well I want it to be locked. Here's the thing, in which direction is the formula being copied? We're copying it down across the rows. That means we need to lock the row reference, not the column. So, watch this. We know that we can hit the F4 key and it locks it, both dollar signs, but now hit it again, oh, and again, I'm hitting the F4 key, and again, Hit it again. It's a merry-go-round key that toggles through the different types of cell references. I'm toggling through. Now watch this, that's a relative cell reference, when I hit the F4 key, locked in all directions, when I hit the F4 key again, only the row reference is locked. That's called a mixed cell reference with the row reference locked. Hit the F4 key again, dollar sign in front of the B, that means only the column reference is locked. Mixed cell reference with the column reference locked. Hit it again and we're back to relative. So, I'm going to, one, two, stop, when the dollar sign is in front of the number. That means when we copy it down, the three will not be allowed to move. Alright, now, that's the first question. The second question is when we copy this formula to the side, do we want the B3 to move relatively, meaning when this formula is right in cell C4, should it still be looking at January, or should it move to February? Yeah, it should move to February, this whole column needs February, so that's why we do not put a dollar sign in front of the column reference. When we copy this formula to the side, the B will move to C. Alright? So there you go. We asked two questions, answered both of them, we're done with that cell reference, now we go to the next one. Alright. So when we copy this formula down, do we want the B14 to move to B15? Yeah, exactly right. Look, office expense, office expense. So we want this 14 to change to 15, and 16, and 17, so as we copy down across the rows, we want it to be relative, we want the 14 to move to 15 then 16. Alright, so relative as we go down. Now the second question is, when we copy it across the columns, do we want it locked? So, imagine, if we copied this formula over here, do we want the orange box to move to C14? No, we want it to be locked. So we put the dollar sign in front of the column reference, the B, So I'm going to hit F4, F4, F4, and there we have mixed cell reference with only the row reference locked. Mixed cell reference with only the column reference locked. Now, we're going to CTRL + Enter, copy it down, it’s a two-step process when you go down and over. You have to drag it down, let go, grab the fill handle a second time, and drag it over to the side. Now, immediately go diagonally furthest across, either with your mouse, or CTRL + Period, Period, and hit the F2 key. That is amazing. It got exactly the right percentage in exactly the right month. And you could select, when you do this for the first time, it's amazing. All those formulas were created and calculated the right expense, and all I have to do is create one formula. You could click somewhere in the middle and hit F2, Look at that. Administrative expense, March revenue. Absolutely amazing. Now I want to re-do this and do it a slightly different way. I know this is a hard concept when you're first learning, so I'm going to build my base formula, and I'm going to ask the same two questions. Alright? This B3, when I copy it down across the rows, do I want it locked? Yes, every cell needs this thousand, while I'm copying it across the rows. So, I'm going to put the dollar sign in front of the number, the row reference. Now the second question, for B3 is, when I copy it this way, do I want it locked on January, or do I want the blue box to move also? I want the blue box to move also. So, now I'm going to enter this, copy it down one, and then over one. And I just want to see, F2, ok, B3, enter,
B3, ok, so it really did, it locked the number on the third row. So as I copy it down, that's working. Now, let's do this, F2, B3, tab, F2, C3. So, look at that, it works perfect. When I copy this way, the B moves to a C, and that's exactly what it wants. February for this whole column. If I were to copy it one over, and forget that the orange one isn't right, just admire, the B3 moved to D3. Now I'm going to delete that, leave those two there, alright? You ready? Let's go back and do the same little experiment with the B14, the orange cell reference. Alright. B14, oh yeah, that's the 25.5% for Cost of Goods Sold. So, when I copy it down, do I want it to move to Office Expenses? Yeah. So, down I want relative cell references, I'm not going to put dollar signs anywhere. Now, when I copy it over to February, do I want the orange box to move to here? No, cuz there's nothing there. So, when I copy it to the side, across the columns, I need it locked on B14. So watch this. F4, F4, F4. Dollar sign in front of the B, but not in front of the 14. Now, let's do that same thing, CTRL + Enter, I'm going to copy it just down one, and then over one. I'm going to click in that first cell, F2, Ok. So B14, Enter, F2 to put it in edit mode. Oh look, the 14 changed to 15. Because there was no dollar sign in front of the number, when you copy it across the numbers, it moves, 14 goes to 15.` That's a relative cell reference, right? However many down, this is one, two, three, four, five, six, seven, eight. Ten down, alright? Ten below me, that's the relative position. I'm the formula and relative to me, ten, exactly the same number. So that as we copy it down, the number is moving. No dollar sign in front of it, so when we copy it down across the numbers, it over, as a relative cell reference. Now, F2, B is locked, 14 is not. When I copy it across the letters, across the columns, because there's a dollar sign, it better remain locked. F2, oh look, it didn't change. The C changed here, because there's no dollar sign in front of it, but that B remained locked. Dramatically reduce formula creation. I just created all of those formulas, with a single formula. F2, look at that. Now it does take practice to learn them, but I promise you, once you learn them, you can create formulas more quickly, and then advanced tricks, like conditional formatting for a row, and things like that, you can do, cuz you know mixed cell reference, and we will see some cool tricks moving forward in the class. Now I want to go over to the sheet (CR6), and here is another trick. This is to help you when you're out there making your formulas and you all of a sudden you forget, you can't remember how to do mixed cell references. This is a cool trick. You just put one to twelve as the row headers and one to twelve as the column headers, and it's exactly like that budget, we have some column header, that was the revenue, and then instead of the percentages for expenses being down here, multiplication table has them right here, so it's really easy to try and figure out. But notice, each cell in this table, a hundred and forty-four formulas, gets the same formula. Look at the column header and multiply times the row header. So let's go over, I clicked Escape, build our base formula, I still remember, when I was out working, I was trying to do this budget, and I was like "I can't remember how to do this." and I quickly went and did this multiplication table over to the side, it took me a few minutes, and I went "Oh, yeah. That's how you do it." Alright, so remember two questions. C3, when I copy it down across the rows, do I want it locked? Yes, every single formula gets the row header number 1. So, I'm going to go F4, F4, and lock the number, cuz I'm copying across the rows, which are numbers. Alright, now, when I copy this formula to the side, the C3, do I want it locked on the one, or do I want it to move relatively? I want it to move relatively. C3 needs to move to D3, where that number two is. Alright, I answered both questions about that cell reference, now I'm going to go to the next one. Try and answer both questions. Ok, you want to copy it down, across the rows, do I want it locked, or not locked? Oh yeah, I want it relative, so each one of these cells here, needs to look one cell to my left. So, not going to do anything, no dollar signs when I copy it down. Now, how about B4, when I copy it to the side? Do I want it locked on that number one, that row header? Or do I want it to move relatively? Well, imagine when I'm over here what do I need? Four times one. Oh, so I need it locked when I go this way, across the letters, across the columns. So, the fact that I'm copying it across the columns, that's my hint, my memorization trick. Oh, if I want it locked, it's got to go in front of the letters, right? So, F4, F4, F4. Ctrl + Enter. Double click and send it down, two steps and then copy it over. I'm going to delete all those. If you have them all highlighted to begin with, then you just do your formula, Equals, one cell above, F4, F4, to lock the row but not the column, Times, the row header, F4, F4, F4, column reference locked but not the row and the CTRL + Enter to populate your entire table CTRL + Period, Period, F4 and there we have it, twelve times twelve is a hundred forty-four. Alright, so mixed cell references take some practice. Let's do another example. Actually, we're going to go back to four. We're going to scroll down a little bit. Alright? We're going to come back to this
SUMIFS. Those fences, or railroad tracks, or pound signs, mean the column is too small. So, I'm going to highlight all of these columns and double click to best fit all of them. Alright, Oops, that one too. Double click. Here, I have a different situation. I'm going to want to add all of the sales. But each intersecting cell has criteria that comes from the column header and from the row header. So, when I add here, I want to know, what was the total for Sunset Product, that we sold in the West? If I'm right here, it's "What's the total for Sunset Product sold in the Midwest?" Every single cell, we're going to do SUMIFS, but the criteria is going to come from column header, row header. Alright? So we're going to try to build our formula right here. Equals, SUMIFS, Sum_range? Well, I'm going to add all of the Sales. CTRL + Shift + Down arrow, F4, Comma. Criteria_range. Well, you can do this one first or this one, it doesn’t matter. This is AND Criteria, we mentioned this before. AND Criteria, it doesn't matter in which order you do it. It needs to get a true for Sunshine from the products column, and a true for East from the Region column. True, True. It doesn't care which one comes first, it could be
True, Sunshine, True, East, or it could be True, East, True, Sunshine. Alright, so I'm going to do the region first. So, this is "criteria_range". The word range means you want the column with all the criteria. CTRL + Shift, down arrow, F4. Now, Comma, "criteria", that's this "East". Now we need to ask the question of that cell reference. Two questions. When I copy it down, I'm going across the rows or numbers, when I copy it down, do I want it locked on "East"? Yeah, every single cell in this column needs "East". Well, if I need it locked going down, I just look over and say "What am I copying across?" OH, the numbers, so I'm going to hit F4, F4. Alright? So, this way it's locked. Now, the SUMIFS, this whole formula is going to have to be copied over to the next cell with "MidWest" and then the next cell, "West". Well, when I copy it across the columns, do I want it locked on "East"? So, when I get over to the "MidWest" column, do I still want it locked on "East?" No. I want that K to move to an L. Right? So no dollar sign. There we go. We asked both questions, we answered both questions, we're done. Now, ready? Comma, "criteria_range1", oh yeah, the whole products column, CTRL + Shift, down arrow, F4. Those are locked in all directions, cuz every single cell always needs the same column. Oops! Now notice, I tried to move my screen tip and once you do that, the dancing ants were still moving so it's in edit mode, so I have to go re-get the product column. Click in that cell, CTRL + Shift, down arrow, F4. Now watch this, I'm going to try to be more careful, cuz if I use this cursor, the dancing ants are still going, so it will just re-direct it. OK, Ready? I'm trying to move my screen tip right here, so I can watch when I type a Comma, there's the criteria_range that's bold, Now it says "Hey, give me the criteria." Alright, so I'm going to click on "Sunshine"'. Now, when I copy down, this SUMIFS formula, do I want Sunshine all the way down, or do I want the dancing ants to move to "Carlota" and then "Sunset"? Yeah, as I copy this formula down, all the way to "Bellen", it needs to move relative, so I'm not going to put any dollar signs in. Now, the SUMIFS, when I copy it over to "MidWest", and then "West", do all of these cells need to be locked on "Sunshine"? You betcha. So, we put our cursor and we go "Well, what are we doing?" We're copying it across the columns, they have letters, and since I want it locked going in this direction, I'm going to put it in front of the "J". I'm hitting the F4, key, one, two, three times. There we go. Copy down it moves relatively, copy over to the side, it is going to be locked. That's it. Closed parenthesis, CTRL + Enter. Double click and send it down. Two steps and then click and drag it over. Go to the diagonal furthest one away, and hit
F2. That is absolutely amazing. Look at that. It got all of the ranges over here are locked, perfectly, absolute, and it got the "Bellen" and the "South". The actual column header and the row header perfectly. Again, the idea here is, because we know how to create one formula, copy it down and over, it means that we have to create one, instead of four, different, separate formulas. Alright. One more example. I'm going to CTRL + Page down, Page down, until I get to (CR7). Here's another situation There's many situations where you have criteria or formula inputs as row headers and as column headers. We saw the budget, we saw the multiplication table, we saw the adding with two conditions, now we have a future value, or "What is your pension worth?" Now, if you put $3000 into an account, every year, and you saved it for 25 years, and you earned on average seven percent, we could calculate what your pension would be worth. Now, in a finance class, we would learn the lovely math behind it, and we would do this big huge formula. But luckily, in Excel, there's a function called "Future Value" or "FV, and all you have to tell "FV" is how much you put in each period, what the assumed interest rate is and for how long, how many periods, and it will tell you what the future value would be. Now notice, right here it's 25 and seven percent, but over here it's 25 and ten percent. Right here, its 40 years and seven percent, so every single cell has a different formula input that comes from the column header and the row header. Now this one is used in every single cell. So, when we create our formula, our Future Value, we're going to need to put this in, the actual amount you put in each period, the number of periods, and the rate. Alright.
You ready? Let's start up in this B5. Equals, FV, returns the Future Value of an investment based on a periodic constant payment and a constant interest rate. Right, you know, over 40 years, maybe you have a CD or something with a constant rate, and you always put this in. But most of the time, this is an estimate. Right? You're looking in the future, so you're estimating. So we're just estimating. Here's the time, here's the period. That's why you do a table like this, so you can see all of the possibilities from various formula inputs. Alright, it says "Hey, give me the rate." Now, I'm just going to fill this out first and then we'll come back and talk about the cell references. Oh, that's the six percent, Comma, NPER, now if you don't know what these are, you can actually click on this blue link and go to help or you can open up the dialogue box by clicking right here, and each one of these arguments, as you click through, has an explanations down here. Now, I'm going to go ahead and read it from the screen tip, but that is a useful trick. The function argument dialogue box is great because it tells you what this payment should be, and it tells you that NPER means number of periods. So now I'm going to clock Escape, Equals, FV,
"Rate", I'm going to put that in, Comma, NPER, that means number of periods, Payment, that means the payment you put in every period. Now this payment, the future value, and there's some other financial functions in Excel, they know cash flow. And when you take the $3000 from your wallet and put it into the bank, that's considered a negative cash flow. It means it's out Now, it's still yours, you put it into the bank, but in terms of your actual wallet, which direction is the cash flow going? It's going out of your wallet, so you have to put a minus. By the way, if you don't put a minus here, the future value would be minus at the end. Because you want to get the money in retirement as a positive, you put it as a negative here. Alright, now, the last two arguments are in square brackets, When you see a square bracket, it means if you know the default, you can leave it out, and for a simple pension calculation, you don't have to do these last two arguments. PV would be if you had money in the bank before we started making these deposits, and TYPE is whether it's at the end of the period or the beginning of the period. Since we're always putting it in at the end of the year, that's the default. So we just leave it out. Close parenthesis. Really, for this example here, for this future value function, we're just interested in these cell references. Now, when I CTRL + Enter, there is the answer. If I put in $3000 for five years at six percent, I would have $16,911.28 But I want to copy this down and over, and of course that won't work unless we fix the cell reference, so F2. Alright, we're going to start with the easy one first, the PMT. Now when you copy this down and over, across the rows and columns, it needs to be locked. Every single cell needs that formula input. So, guess what? F4. Oh, dollar sign in front of the B and the one. Column reference and the row reference. That means, no matter where you copy it, down or over, it's always locked. Alright, now I'm going to touch the NPER. There it is right there. Now, when I copy this formula to the side, does it need to be locked on the five years? You betcha. Now, if I'm copying it, it needs to be locked, but I ask myself, "Where am I copying it? Which direction am I copying it across, the letters or the numbers?" I'm copying it across the letters, so the dollar sign needs to be in front of the letter, so I'm going to hit F4, F4, F4. Now, when I copy it down, do I want it locked on five, or do I want it to move relatively, to 10, and then 15, and then 20? I want it to move relatively, so no more dollar signs. Now I go to B4. Alright, and by the way, you can pick, when you're asking the two questions of the cell reference, you can ask whichever one first you want. Let's do this one first. When I copy it to the side, do I want it locked on six percent? When this whole formula gets over to the eight percent column, do I want it still locked on six percent? No, I want it to move. So, in essence, I want the B to move to C, that would be the seven percent, and then D, that would be the eight percent. Alright, so relative this direction. Alright now, what about when I copy it down? Do I want it locked on the six percent or do I want it to move relatively? I want it locked. So I ask myself "Which direction am I copying it, across the rows or numbers? Or columns?" Oh yeah, they're numbers. So that's my memorization cue, so I need to put a dollar sign in front of the number. F4, F4. Dollar sign in front of the number but not the column. This is going to be awesome. Alright, ready? CTRL + Enter. Double click and send it down. Two steps. Copy it over, go to the diagonally furthest one away, either with your mouse or CTRL + Period, Period, and F2. You got to be kidding me. Look at that cell reference magic, totally locked down, totally has the interest rate, which is the column header, and the years, which is the row header. Absolutely magic. Again, why do we do this? Because, if we know cell references, we can create a bunch of formulas like this much more quickly. So, the minimum, if we didn't use mixed cell references, right, we have to get one, two, three, four, five, six, six different formulas. Now, later in the class... Here we saw the main advantage of mixed cell references that we can create a lot of formulas quickly. Again, later in the class we'll see examples of features, like conditional formatting, where if you don't know mixed cell reference, you just can't do the cool advanced thing. Alright, so in this video we reminded ourselves about relative and absolute and then we jumped in and talked about mixed cell references. In our next video we'll talk about assumption tables and scenarios. Alright, see you next video.
