WEBVTT Kind: captions Language: en 00:00:09.530 --> 00:00:14.990 There are two main types of cell references in Excel: relative and absolute. Both behave 00:00:14.990 --> 00:00:20.210 differently when copied to other cells, either as part of a formula or a function. 00:00:20.210 --> 00:00:24.240 Relative references are the default (and probably the type you're going to use for most of your 00:00:24.240 --> 00:00:30.320 formulas). In this example, I need to know the total cost of each menu item on the invoice. 00:00:30.320 --> 00:00:35.219 To do this, I'll need to multiply the values in these two cells. 00:00:35.219 --> 00:00:41.030 I'll just enter my formula here. Start by typing the equal sign… and then the formula 00:00:41.030 --> 00:00:50.520 will be B4*C4. When you're done, press Enter on your keyboard. 00:00:50.520 --> 00:00:56.780 Now if I use the fill handle to copy this formula to all of these cells… we can double-click 00:00:56.780 --> 00:01:02.969 each copy, and see that the cell references have changed automatically. So we have B5… 00:01:02.969 --> 00:01:04.430 B6… 00:01:04.430 --> 00:01:08.460 B7, and so on. 00:01:08.460 --> 00:01:13.510 That's because these are relative references. In other words, when Excel looks at our original 00:01:13.510 --> 00:01:18.460 formula, what it actually sees is the location of the cells relative to the location of the 00:01:18.460 --> 00:01:23.380 formula. That means in this case, it's looking at the two cells to the left of our current 00:01:23.380 --> 00:01:28.369 location, and those just happen to be B4 and C4. 00:01:28.369 --> 00:01:32.960 Each copy is also looking at the two cells to the left of the formula. This technique 00:01:32.960 --> 00:01:37.450 is useful because it means we can copy the formula (as we did here), and the cell references 00:01:37.450 --> 00:01:39.890 will sort of move along with it. 00:01:39.890 --> 00:01:46.329 The references don't even have to be in the same row—they can be anywhere in the sheet. 00:01:46.329 --> 00:01:50.479 There may be times when you don't want the cell references to change. Take this version 00:01:50.479 --> 00:01:52.119 of the invoice. 00:01:52.119 --> 00:01:56.659 I've added a column where we can list the sales tax for each item. We should be able 00:01:56.659 --> 00:02:01.500 to enter our formula here… then copy it to the rest of the list. 00:02:01.500 --> 00:02:09.259 To get started, I'm going to enter B4*C4 in parenthesis. Then I'm going to multiply by 00:02:09.259 --> 00:02:15.690 cell E2, which contains our tax rate at 7.5% (which is the same as .075). 00:02:15.690 --> 00:02:23.129 Next, I'll use the fill handle to copy the formula to the rest of these cells... but 00:02:23.129 --> 00:02:27.319 wait, that doesn't look right at all. It's even affecting the formulas in the rest of 00:02:27.319 --> 00:02:29.120 the worksheet. 00:02:29.120 --> 00:02:33.800 The first copy of our formula is correct, because it's multiplying by E2, which contains 00:02:33.800 --> 00:02:38.610 our tax rate… but as we go down the list, we can see that the reference is moving in 00:02:38.610 --> 00:02:44.290 relation to the cells, which isn't what we want. We want it to stay frozen on E2 for 00:02:44.290 --> 00:02:51.590 each copy of the formula—and for that, we'll need to use an absolute reference. 00:02:51.590 --> 00:02:56.580 Absolute references have a dollar sign placed before the column, row, or both, to keep them 00:02:56.580 --> 00:03:00.400 from changing when you copy the formula to another cell. 00:03:00.400 --> 00:03:05.860 To keep the column and the row from changing, place a dollar sign in front of both. 00:03:05.860 --> 00:03:11.250 To keep only the row from changing, place a dollar sign in front of the row only. 00:03:11.250 --> 00:03:16.310 To keep only the column from changing, place a dollar sign in front of the column. 00:03:16.310 --> 00:03:20.110 Most of the time, you'll be using the first type of absolute reference—the one where 00:03:20.110 --> 00:03:25.540 there's a dollar sign before the column and the row. 00:03:25.540 --> 00:03:30.040 Let's go back to our example, and add dollar signs before the column and the row to make 00:03:30.040 --> 00:03:34.040 E2 an absolute reference. 00:03:34.040 --> 00:03:39.860 Now if we copy the formula to the rest of this list… the errors go away, and we're 00:03:39.860 --> 00:03:44.620 left with the correct sales tax for each item. The relative reference is different in each 00:03:44.620 --> 00:03:48.860 cell, but the E2 reference (our absolute reference) stays the same. 00:03:48.860 --> 00:03:55.099 Finally, I'd like to show you how to create cell references across different worksheets 00:03:55.099 --> 00:03:59.930 in the same workbook. This can be useful if you want to create a formula on one worksheet 00:03:59.930 --> 00:04:02.689 that references a value from another. 00:04:02.689 --> 00:04:06.680 It works the same as a normal reference—you just have to include the name of the worksheet 00:04:06.680 --> 00:04:12.470 along with the cell address. So to get started, figure out what cell you want to use… then 00:04:12.470 --> 00:04:18.239 take note of its location. In this example, we're going to use cell E14 on the worksheet 00:04:18.239 --> 00:04:19.239 Menu Order. 00:04:19.239 --> 00:04:26.199 Next, switch to the worksheet where you want to use the reference. As you can see, I already 00:04:26.199 --> 00:04:30.690 have a formula that's calculating the total of all these services. I just need to add 00:04:30.690 --> 00:04:34.919 the total for the menu order in this cell here. 00:04:34.919 --> 00:04:42.000 To reference the cell, type the name of the worksheet first… then an exclamation point… 00:04:42.000 --> 00:04:48.020 and finally the cell address. (If the worksheet name includes a space, like in this example, 00:04:48.020 --> 00:04:51.889 you'll need to add single quotation marks around the name.) 00:04:51.889 --> 00:04:57.550 When you're done, press Enter… and Excel will pull the value from the other worksheet. 00:04:57.550 --> 00:05:02.419 That means, if cell E14 ever changes on the Menu Order worksheet, it'll be updated here 00:05:02.419 --> 00:05:04.490 too. 00:05:04.490 --> 00:05:09.580 Mastering cell references is the key to creating formulas that work for you and your data. 00:05:09.580 --> 00:05:14.360 Now you know how to use relative references, absolute references, and references across 00:05:14.360 --> 00:05:15.650 multiple worksheets.