WEBVTT Kind: captions Language: en 00:00:11.360 --> 00:00:16.270 There are two main types of cell references in Excel: relative and absolute. 00:00:16.270 --> 00:00:22.439 Both behave differently when copied to other cells, either as part of a formula or a function. 00:00:22.439 --> 00:00:26.430 Relative references are the default (and probably the type you're going to use for most of your 00:00:26.430 --> 00:00:27.520 formulas). 00:00:27.520 --> 00:00:32.210 In this example, I need to know the total cost of each menu item on the invoice. 00:00:32.210 --> 00:00:37.070 To do this, I'll need to multiply the values in these two cells. 00:00:37.070 --> 00:00:39.590 I'll just enter my formula here. 00:00:39.590 --> 00:00:48.120 Start by typing the equal sign... and then the formula will be B4... times C4. 00:00:48.120 --> 00:00:52.120 When you're done, press Enter on your keyboard. 00:00:52.120 --> 00:00:58.250 Now if I use the fill handle to copy this formula to all of these cells... we can double-click 00:00:58.250 --> 00:01:02.650 each copy, and see that the cell references have changed automatically. 00:01:02.650 --> 00:01:04.300 So we have B5... 00:01:04.300 --> 00:01:05.489 B6... 00:01:05.489 --> 00:01:10.250 B7, and so on. 00:01:10.250 --> 00:01:12.930 That's because these are relative references. 00:01:12.930 --> 00:01:17.860 In other words, when Excel looks at our original formula, what it actually sees is the location 00:01:17.860 --> 00:01:21.570 of the cells relative to the location of the formula. 00:01:21.570 --> 00:01:25.970 That means in this case, it's looking at the two cells to the left of our current location, 00:01:25.970 --> 00:01:30.080 and those just happen to be B4 and C4. 00:01:30.080 --> 00:01:34.060 Each copy is also looking at the two cells to the left of the formula. 00:01:34.060 --> 00:01:38.130 This technique is useful because it means we can copy the formula (as we did here), 00:01:38.130 --> 00:01:41.369 and the cell references will sort of move along with it. 00:01:41.369 --> 00:01:47.770 The references don't even have to be in the same row—they can be anywhere in the sheet. 00:01:47.770 --> 00:01:51.310 There may be times when you don't want the cell references to change. 00:01:51.310 --> 00:01:53.030 Take this version of the invoice. 00:01:53.030 --> 00:01:57.490 I've added a column where we can list the sales tax for each item. 00:01:57.490 --> 00:02:02.979 We should be able to enter our formula here... then copy it to the rest of the list. 00:02:02.979 --> 00:02:09.489 To get started, I'm going to enter B4*C4 in parenthesis. 00:02:09.489 --> 00:02:15.340 Then I'm going to multiply by cell E2, which contains our tax rate at 7.5% (which is the 00:02:15.340 --> 00:02:17.840 same as .075). 00:02:17.840 --> 00:02:24.110 Next, I'll use the fill handle to copy the formula to the rest of these cells. 00:02:24.110 --> 00:02:27.370 But wait, that doesn't look right at all. 00:02:27.370 --> 00:02:31.040 It's even affecting the formulas in the rest of the worksheet. 00:02:31.040 --> 00:02:35.620 The first copy of our formula is correct, because it's multiplying by E2, which contains 00:02:35.620 --> 00:02:40.420 our tax rate... but as we go down the list, we can see that the reference is moving in 00:02:40.420 --> 00:02:44.030 relation to the cells, which isn't what we want. 00:02:44.030 --> 00:02:48.810 We want it to stay frozen on E2 for each copy of the formula—and for that, we'll need 00:02:48.810 --> 00:02:53.900 to use an absolute reference. 00:02:53.900 --> 00:02:58.390 Absolute references have a dollar sign placed before the column, row, or both, to keep them 00:02:58.390 --> 00:03:02.300 from changing when you copy the formula to another cell. 00:03:02.300 --> 00:03:07.770 To keep the column and the row from changing, place a dollar sign in front of both. 00:03:07.770 --> 00:03:13.150 To keep only the row from changing, place a dollar sign in front of the row only. 00:03:13.150 --> 00:03:18.420 To keep only the column from changing, place a dollar sign in front of the column. 00:03:18.420 --> 00:03:21.950 Most of the time, you'll be using the first type of absolute reference—the one where 00:03:21.950 --> 00:03:27.470 there's a dollar sign before the column and the row. 00:03:27.470 --> 00:03:31.870 Let's go back to our example, and add dollar signs before the column and the row to make 00:03:31.870 --> 00:03:36.720 E2 an absolute reference. 00:03:36.720 --> 00:03:42.470 Now if we copy the formula to the rest of this list... the errors go away, and we're 00:03:42.470 --> 00:03:45.630 left with the correct sales tax for each item. 00:03:45.630 --> 00:03:50.610 The relative reference is different in each cell, but the E2 reference (our absolute reference) 00:03:50.610 --> 00:03:52.500 stays the same. 00:03:52.500 --> 00:03:57.480 Finally, I'd like to show you how to create cell references across different worksheets 00:03:57.480 --> 00:03:59.270 in the same workbook. 00:03:59.270 --> 00:04:02.990 This can be useful if you want to create a formula on one worksheet that references a 00:04:02.990 --> 00:04:05.180 value from another. 00:04:05.180 --> 00:04:09.090 It works the same as a normal reference—you just have to include the name of the worksheet 00:04:09.090 --> 00:04:11.180 along with the cell address. 00:04:11.180 --> 00:04:16.699 So to get started, figure out what cell you want to use... then take note of its location. 00:04:16.699 --> 00:04:22.079 In this example, we're going to use cell E14 on the worksheet Menu Order. 00:04:22.079 --> 00:04:27.050 Next, switch to the worksheet where you want to use the reference. 00:04:27.050 --> 00:04:32.189 As you can see, I already have a formula that's calculating the total of all these services... 00:04:32.189 --> 00:04:37.110 I just need to add the total for the menu order in this cell here. 00:04:37.110 --> 00:04:44.259 To reference the cell, type the name of the worksheet first... then an exclamation point... 00:04:44.259 --> 00:04:46.479 and finally the cell address. 00:04:46.479 --> 00:04:51.119 (If the worksheet name includes a space, like in this example, you'll need to add single 00:04:51.119 --> 00:04:54.189 quotation marks around the name.) 00:04:54.189 --> 00:04:59.629 When you're done, press Enter... and Excel will pull the value from the other worksheet. 00:04:59.629 --> 00:05:04.419 That means, if cell E14 ever changes on the Menu Order worksheet, it'll be updated here 00:05:04.419 --> 00:05:06.370 too. 00:05:06.370 --> 00:05:11.439 Mastering cell references is the key to creating formulas that work for you and your data. 00:05:11.439 --> 00:05:16.150 Now you know how to use relative references, absolute references, and references across 00:05:16.150 --> 00:05:17.330 multiple worksheets.