1 00:00:01,050 --> 00:00:06,630 Let's talk about how you can use range names for improved clarity in your formulas. 2 00:00:06,660 --> 00:00:12,960 You might also come across range names when you work with your colleagues' files and you see some 3 00:00:12,960 --> 00:00:18,630 names inside their formulas. And you're wondering, where they're coming from? 4 00:00:18,630 --> 00:00:24,870 After this lecture, you're going to know. The default in Excel is that the name of the cell is its address. 5 00:00:24,930 --> 00:00:29,640 If you take a look at the Name Box here the name of the cell is E3. 6 00:00:29,640 --> 00:00:30,580 Here's the thing. 7 00:00:30,750 --> 00:00:31,980 You can change that. 8 00:00:32,070 --> 00:00:37,910 We can just click there delete what's in there and call this "Discount" and press enter. 9 00:00:37,920 --> 00:00:41,180 Now this cell is also called discount. 10 00:00:41,190 --> 00:00:43,480 There are some naming conventions here. 11 00:00:43,560 --> 00:00:46,220 So you have to start with a letter or an underscore. 12 00:00:46,230 --> 00:00:47,690 You can't start with a number. 13 00:00:47,730 --> 00:00:53,250 You also can't have special characters in the name and you also can't have a space there. 14 00:00:53,490 --> 00:00:57,060 So you can use an underscore instead of the space. 15 00:00:57,060 --> 00:00:58,560 Now the moment this is called discount. 16 00:00:58,620 --> 00:01:01,320 I don't need to reference the cell 17 00:01:01,350 --> 00:01:03,420 by saying "=E3". 18 00:01:03,450 --> 00:01:11,040 I can also reference it by saying "=discount". Type in "=discount" I actually see my name cell 19 00:01:11,190 --> 00:01:13,820 in the dropdown here and I press enter. 20 00:01:13,890 --> 00:01:15,930 It's my 10 percent. 21 00:01:15,930 --> 00:01:23,390 You might find it easier to read formulas if you are using a name that's describing what is in the cell. 22 00:01:23,490 --> 00:01:25,390 Instead of using a cell reference. 23 00:01:25,470 --> 00:01:34,260 So for example here, if we want to calculate our final price. I can do price times one minus discount (=B5*(1-Discount)) 24 00:01:34,620 --> 00:01:38,040 close bracket press enter and send this down. 25 00:01:38,040 --> 00:01:44,700 Now here's the other thing, I don't have to worry about absolute cell referencing. That name is fixed 26 00:01:44,790 --> 00:01:45,760 to this cell. 27 00:01:45,840 --> 00:01:50,140 The other great thing is that I can use it from any other worksheet. 28 00:01:50,150 --> 00:01:54,410 So I don't have to come and reference back to this sheet and this cell. 29 00:01:54,420 --> 00:02:02,580 For example, if I go to the referencing tab here type in "=discount" and press enter, I see my discount 30 00:02:02,580 --> 00:02:03,180 value. 31 00:02:03,390 --> 00:02:05,790 I can also jump quickly to that cell. 32 00:02:05,820 --> 00:02:14,640 So let's say I'm in this sheet I'm going to go and type in discount in here and press enter, I jump directly 33 00:02:14,640 --> 00:02:19,100 to that discount cell. In addition to naming individual cells 34 00:02:19,140 --> 00:02:21,920 you can also name an entire range. 35 00:02:21,930 --> 00:02:28,990 For example, this entire price list I could just call this "pricelist" and press enter. 36 00:02:29,420 --> 00:02:33,720 So the entire range is named "pricelist". 37 00:02:33,730 --> 00:02:41,550 Now if I want to get the average price, I can do SUM and let's do "pricelist" - you can see it right here - 38 00:02:41,880 --> 00:02:49,320 divided by 4. Or, I can directly use the average function which is a function we're going to cover 39 00:02:49,320 --> 00:02:54,210 in detail in the next section when we take a look at most useful Excel functions. 40 00:02:54,210 --> 00:03:00,670 So here I would just have to type in my pricelist, close bracket, press enter and it's there. 41 00:03:00,840 --> 00:03:08,340 And this is kept in memory so I can do this from any worksheet. If my numbers change here they're automatically 42 00:03:08,390 --> 00:03:09,490 going to pull through the list. 43 00:03:09,530 --> 00:03:11,370 So let's change 80 to 10. 44 00:03:11,370 --> 00:03:13,850 Keep your eye on these values here. 45 00:03:13,890 --> 00:03:18,840 Press enter, they changed. Ok, Control+z to go back. 46 00:03:18,870 --> 00:03:23,960 What if you wanted to delete one of these existing names or update the name? 47 00:03:24,090 --> 00:03:26,470 You can do that from Name Manager. 48 00:03:26,580 --> 00:03:30,200 Name Manager is in the formulas tab, you see it right here. 49 00:03:30,240 --> 00:03:35,370 You can define names also directly through the name manager if you don't want to do it from the name box 50 00:03:35,370 --> 00:03:35,730 here. 51 00:03:35,880 --> 00:03:41,670 But when you click on Name Manager you see the existing names in your workbook. You can just click on 52 00:03:41,670 --> 00:03:49,050 one and press delete or click on edit and adjust the name and then click on OK. You see by default that 53 00:03:49,050 --> 00:03:52,880 the scope of these names are for the workbook. 54 00:03:52,950 --> 00:03:58,230 That's why you can use them from any sheet without referencing the sheet name. 55 00:03:58,230 --> 00:04:02,730 You can also create names which their scope is just for that worksheet. 56 00:04:02,880 --> 00:04:09,300 From the dropdown list you can select the sheet and then create your formula reference. Let's create 57 00:04:09,330 --> 00:04:12,260 one name directly from the name manager here. 58 00:04:12,290 --> 00:04:13,670 I'm going to click on new. 59 00:04:13,740 --> 00:04:16,079 I want to call this back 60 00:04:16,350 --> 00:04:22,290 and that name should apply for the whole workbook and it should be this cell here. 61 00:04:22,290 --> 00:04:29,460 So this is kind of like my bookmark to always go back to the index sheet and then click on OK and close. 62 00:04:29,460 --> 00:04:35,950 Now every time we click on this dropdown and we select back, we always go back to our index sheet. 63 00:04:36,150 --> 00:04:42,400 OK so this is how you can use range names as bookmarks but also for clarity in your formulas. 64 00:04:42,420 --> 00:04:48,930 Now I don't advise you to go overboard by naming everything. Just name the few important things that 65 00:04:48,930 --> 00:04:55,010 you're going to be using throughout your Excel report or your Excel model but leave the other ones as 66 00:04:55,010 --> 00:04:55,980 cell references. 67 00:04:56,010 --> 00:05:01,790 So for this report if I was planning to use the discount rate in different places I would give this 68 00:05:01,790 --> 00:05:06,410 a name but I will not give this range a name and use that in the formulas. 69 00:05:06,410 --> 00:05:09,080 I'll just use the usual cell referencing.