1 00:00:01,300 --> 00:00:07,180 Absolute and relative cell referencing is one of the most important concepts when you work with Excel 2 00:00:07,180 --> 00:00:08,530 formulas. 3 00:00:08,530 --> 00:00:16,000 It's important that you master this. Until now you've seen me use relative cell referencing. Here for example 4 00:00:16,000 --> 00:00:22,450 where we have our list of items price and we want to apply this discount rate we can use this formula. 5 00:00:22,540 --> 00:00:27,510 We take the price and we deduct the full price of the discount from it. 6 00:00:27,510 --> 00:00:34,090 Now when I pull this formula down, the references shift down with the final result so they're always 7 00:00:34,090 --> 00:00:36,040 in relation to the formula cell. 8 00:00:36,040 --> 00:00:39,330 Now I can also write this differently. 9 00:00:39,370 --> 00:00:50,420 I can say =B5*(1-D5). Regardless of the way I write this 10 00:00:50,440 --> 00:00:57,430 in both cases I'm using relative cell referencing. I want both cells to shift when I pull the formula 11 00:00:57,430 --> 00:00:58,010 down. 12 00:00:58,090 --> 00:01:00,090 But what if my setup was different? 13 00:01:00,100 --> 00:01:06,380 What if I had a global discount rate on top here that I wanted to apply to all of these? 14 00:01:06,430 --> 00:01:08,530 How would I write this formula? 15 00:01:08,770 --> 00:01:15,730 Let's just hide these and see if we can use the same concept we've used before. 16 00:01:15,730 --> 00:01:22,630 I'm going to take the price multiplied with 1 minus the discount rate. Close bracket, 17 00:01:22,650 --> 00:01:25,300 press enter. Can I pull this down? 18 00:01:25,310 --> 00:01:26,950 Well, let's try it. 19 00:01:26,950 --> 00:01:28,300 I get problems. 20 00:01:28,300 --> 00:01:28,810 Why? 21 00:01:28,960 --> 00:01:33,130 Because I'm using relative cell referencing for both sides. 22 00:01:33,160 --> 00:01:39,730 This means when I pull down the formula this reference for my global discount is going to come down. 23 00:01:39,760 --> 00:01:43,120 That's not something I want. I want to fix this reference. 24 00:01:43,240 --> 00:01:46,390 I don't want it to move when I pull down the formula. 25 00:01:46,390 --> 00:01:53,260 This is where you need to use absolute cell referencing. Which means you need to add dollar signs to 26 00:01:53,260 --> 00:01:57,130 the column as well as to the row argument. 27 00:01:57,130 --> 00:02:02,340 Now you can do this manually by looking for the dollar sign on your keyboard and adding it in. 28 00:02:02,560 --> 00:02:07,630 But there is a key that helps you do this in one go and that's the F4 key. 29 00:02:07,660 --> 00:02:13,810 If you go to the cell reference you want to fix, click on F4, it puts both dollar signs there. 30 00:02:13,810 --> 00:02:20,230 If you click it again it puts a dollar sign under row which means only the row is fixed. Click it again 31 00:02:20,530 --> 00:02:22,240 only the column is fixed. 32 00:02:22,240 --> 00:02:26,330 Click it one more time you go back to relative cell referencing. 33 00:02:26,350 --> 00:02:29,140 So this is a toggle. We want it fully fixed. 34 00:02:29,140 --> 00:02:34,810 I'm going to click it once to get the dollar sign for both the row as well as the column. 35 00:02:34,810 --> 00:02:38,050 This means that this entire cell reference is fixed. 36 00:02:38,050 --> 00:02:42,880 If I pull down this formula, H3 will stay H3. 37 00:02:42,910 --> 00:02:43,470 Let's try it. 38 00:02:43,480 --> 00:02:50,370 Let's just double click on the side. Go to the bottom and check, H3 stayed H3. 39 00:02:50,470 --> 00:02:54,040 Okay so now let's do another example here. 40 00:02:54,040 --> 00:03:01,360 I also want to calculate the price for February. But I want to use the same price here, the same global 41 00:03:01,360 --> 00:03:02,080 discount. 42 00:03:02,170 --> 00:03:05,500 But I want it applied to both Jan and Feb. 43 00:03:05,500 --> 00:03:12,940 I know that I need to fully fix the global discount. But now what do I have to do with my prices? 44 00:03:12,970 --> 00:03:18,490 Let's check what happens if I keep the same formula as I did in the last example. 45 00:03:18,580 --> 00:03:27,340 So I'm gonna go with price multiplied with 1 minus the global discount. Press F4 to fix it close 46 00:03:27,340 --> 00:03:29,020 bracket and press enter. 47 00:03:29,060 --> 00:03:33,700 Pulling this down, so let's just double click, is gonna work right? 48 00:03:33,700 --> 00:03:40,210 Everything has the correct cell referencing. But now I'm gonna highlight this and pull this also for 49 00:03:40,210 --> 00:03:41,240 February. 50 00:03:41,290 --> 00:03:43,470 I want to see the same numbers here. 51 00:03:43,480 --> 00:03:47,720 I want my formula to still use these prices. 52 00:03:47,740 --> 00:03:55,090 Why doesn't it work? It's because I'm using relative cell referencing. It's shifting the B5 to C5. 53 00:03:55,450 --> 00:04:02,920 How do I have to adjust my first formula to get it right so that if I pull this down and across my 54 00:04:02,920 --> 00:04:07,560 referencing works correctly? What do I have to fix for B5? 55 00:04:07,570 --> 00:04:11,050 Do I have to fix the row or the column? 56 00:04:11,050 --> 00:04:15,090 I don't want to column to shift right? So I have to just fix the B. 57 00:04:15,100 --> 00:04:20,850 I'm going to press F4, once, twice, three times to get the B fixed, press enter. 58 00:04:20,860 --> 00:04:22,150 Now let's double click. 59 00:04:22,150 --> 00:04:27,130 Push it down and pull to the side and everything works well. 60 00:04:27,130 --> 00:04:33,800 So my tip for you is always go to the bottom right hand corner of your data range, go to edit mode and 61 00:04:33,840 --> 00:04:36,980 make sure that your references are correct. 62 00:04:37,000 --> 00:04:43,270 Let's do one more example. This time I want to apply different discounts for each month. 63 00:04:43,330 --> 00:04:47,160 I want to use the same prices but in January we're going to have a big discount. 64 00:04:47,230 --> 00:04:49,870 In February we're going to have a 10 percent discount. 65 00:04:50,290 --> 00:04:58,030 So try writing this one or think about how you would write this one before you watch me write it. 66 00:04:58,030 --> 00:04:58,350 Now this time. 67 00:04:58,360 --> 00:05:00,700 Let's try to get it right from scratch. 68 00:05:01,150 --> 00:05:02,440 We're going to take the prize. 69 00:05:02,590 --> 00:05:04,090 What do we have to fix here? 70 00:05:04,150 --> 00:05:10,390 The column or the row? The column, right? Because we don't want the column to shift when we pull our formula 71 00:05:10,420 --> 00:05:11,290 across. 72 00:05:11,290 --> 00:05:16,750 But we do want the row to shift when we pull the formula down because it should go to the next price 73 00:05:16,780 --> 00:05:22,690 and the next price. So I'm going to click it for once, twice, three times to get the column fixed. 74 00:05:23,020 --> 00:05:26,200 Let's do one minus the monthly discount. 75 00:05:26,200 --> 00:05:27,750 What do I need to fix here? 76 00:05:27,970 --> 00:05:34,920 The column or the row? I need to get the row fixed, right? Because when I pull this formula down I don't 77 00:05:34,920 --> 00:05:41,760 want the row three to go to row four. But when I pull the formula across I want the column to shift. 78 00:05:42,210 --> 00:05:48,900 So I can't fix the column but I have to fix the row. Press F4 once, twice, to get the row fixed, 79 00:05:49,400 --> 00:05:55,480 close bracket, press enter, and let's pull down and across. 80 00:05:55,620 --> 00:05:59,120 Click on the right hand corner, go to edit mode, 81 00:05:59,130 --> 00:06:00,950 It's 40 times 10. 82 00:06:01,050 --> 00:06:02,260 That looks great. 83 00:06:02,280 --> 00:06:04,150 Let's just check January as well. 84 00:06:04,170 --> 00:06:05,530 40 times 50. 85 00:06:05,910 --> 00:06:07,120 Perfect. 86 00:06:07,140 --> 00:06:14,730 Okay so that's how you can use absolute and relative cell referencing to get your formulas correct. 87 00:06:14,730 --> 00:06:17,360 No matter how your data is setup.