WEBVTT Kind: captions Language: en 00:00:11.070 --> 00:00:15.539 Cell references are one of the most important concepts in Excel, and there are a couple 00:00:15.539 --> 00:00:16.539 of 00:00:16.539 --> 00:00:22.250 different types that you need to know about: Relative references and absolute references. 00:00:22.250 --> 00:00:24.490 Let's start with relative. 00:00:24.490 --> 00:00:27.480 Relative references are the default type of reference, 00:00:27.480 --> 00:00:30.980 and they're probably the type that you'll use most often. 00:00:30.980 --> 00:00:32.769 In this example, I want to know the amount of 00:00:32.769 --> 00:00:35.250 money spent on each item, 00:00:35.250 --> 00:00:40.280 and to do this I'll need to multiply the price by the quantity. 00:00:40.280 --> 00:00:44.280 I'll enter my formula here. 00:00:44.280 --> 00:00:54.200 Start by typing the equals sign, and the formula will be D4*E4. 00:00:54.200 --> 00:00:56.920 And then just press the Enter key. 00:00:56.920 --> 00:00:59.280 If I use the fill handle to copy this formula to 00:00:59.280 --> 00:01:03.379 all of these cells, you can see that in each one, 00:01:03.379 --> 00:01:05.080 the cell references are actually going to be 00:01:05.080 --> 00:01:06.810 different. 00:01:06.810 --> 00:01:13.770 So we have D7, D8, D9, and so on. 00:01:13.770 --> 00:01:17.460 That's because these are relative references. 00:01:17.460 --> 00:01:19.920 In the original formula, when we enter the cell 00:01:19.920 --> 00:01:22.960 references, what Excel actually sees is just the 00:01:22.960 --> 00:01:26.100 location relative to the cell that contains the 00:01:26.100 --> 00:01:27.729 formula. 00:01:27.729 --> 00:01:30.970 In this case, it's looking two cells to the left 00:01:30.970 --> 00:01:33.310 and also one cell to the left, 00:01:33.310 --> 00:01:38.670 and those just happen to be D4 and E4 in this case. 00:01:38.670 --> 00:01:41.360 And each copy is also looking two cells to the 00:01:41.360 --> 00:01:45.600 left and one cell to the left. 00:01:45.600 --> 00:01:47.579 This is really useful because it means we can 00:01:47.579 --> 00:01:49.479 easily copy the formula, 00:01:49.479 --> 00:01:53.180 and the cell references will sort of move with the formula. 00:01:53.180 --> 00:01:56.740 And that doesn't necessarily mean that the references have to be on the same row, 00:01:56.740 --> 00:02:00.329 like they are here - they could be anywhere in this sheet - 00:02:00.329 --> 00:02:02.630 it just means that when the formula is copied to a 00:02:02.630 --> 00:02:03.969 different cell, 00:02:03.969 --> 00:02:11.000 the references will move along with the formula. 00:02:11.000 --> 00:02:13.220 There are other times where you don't want the 00:02:13.220 --> 00:02:15.700 cell reference to change. 00:02:15.700 --> 00:02:17.720 In this example, we have different varieties of 00:02:17.720 --> 00:02:19.080 roses, 00:02:19.080 --> 00:02:23.230 and we want to find the percent of the total sold for each type. 00:02:23.230 --> 00:02:26.669 So for Red Delight, we know that we sold 28 units, 00:02:26.669 --> 00:02:30.120 but what percentage of the total is that? 00:02:30.120 --> 00:02:33.420 We can find the percentage by dividing. 00:02:33.420 --> 00:02:41.790 I'll type the equals sign, then C3 divided by C12. 00:02:41.790 --> 00:02:49.889 Then, I'll use the fill handle to copy this formula to all of these cells, 00:02:49.889 --> 00:02:54.040 but wait a minute, that doesn't look right at all. 00:02:54.040 --> 00:02:59.090 The first one's right, because it's dividing by C12, which is our total, 00:02:59.090 --> 00:03:01.900 but as we go down the list, you can see that this cell reference 00:03:01.900 --> 00:03:06.440 is moving into these empty cells, which is not what we want. 00:03:06.440 --> 00:03:10.449 We want it to stay frozen on C12 for all of these 00:03:10.449 --> 00:03:11.470 formulas, 00:03:11.470 --> 00:03:17.229 and to do that, we'll need to use an absolute reference. 00:03:17.229 --> 00:03:19.650 Absolute references will have a dollar sign placed before 00:03:19.650 --> 00:03:25.350 the column, row, or both, to keep them from changing. 00:03:25.350 --> 00:03:27.380 If you put dollar signs before the column and the 00:03:27.380 --> 00:03:28.380 row, then 00:03:28.380 --> 00:03:32.670 neither of them will change, no matter where you copy the formula to. 00:03:32.670 --> 00:03:35.370 If you just put the dollar sign before the row, 00:03:35.370 --> 00:03:37.070 then the row will not change, 00:03:37.070 --> 00:03:41.850 but the column will change if you copy the formula to a different column. 00:03:41.850 --> 00:03:43.340 And if you just put the dollar sign before the 00:03:43.340 --> 00:03:44.340 column, 00:03:44.340 --> 00:03:47.410 then the column won't change, but the row can change. 00:03:47.410 --> 00:03:51.320 Most of the time, you're just going to be using the first one, where there is 00:03:51.320 --> 00:03:57.570 a dollar sign before the column and the row. 00:03:57.570 --> 00:04:03.040 We can go back to this example and add dollar signs before the column and the row 00:04:03.040 --> 00:04:10.070 to make C12 an absolute reference. 00:04:10.070 --> 00:04:15.750 And now if we copy this formula, these numbers look a lot better. 00:04:15.750 --> 00:04:18.720 If you double-click on some of them, you can see that the relative reference 00:04:18.720 --> 00:04:25.090 is different in each one, but the C12 reference stays constant. 00:04:25.090 --> 00:04:29.010 The type of cell references that you use in your 00:04:29.010 --> 00:04:31.220 formulas are very important. 00:04:31.220 --> 00:04:33.730 So whenever you're creating a formula, just ask 00:04:33.730 --> 00:04:34.730 yourself 00:04:34.730 --> 00:05:01.060 whether you want to use relative references, absolute references, or both.