Time
6 hours 3 minutes
Difficulty
Beginner
CEU/CPE
6

Video Transcription

00:00
Now that you have learned about the data types and custom how to write your custom data types, let's talk about giving names to cells and ranges and excel. So, uh, let's again go to excel
00:16
and let's talk about names. So here we have, for example,
00:21
a cell that says that the currency or exchange rate for currency that is euro is in Book One and in Selby, too.
00:32
And let's delete this for a moment
00:35
and gold here.
00:37
And we have this cell here which represents the exchange rate for euro between your and dollar.
00:45
And here you have a name of this so we can go here
00:49
and we can type here euro rate.
00:55
And uh,
00:58
now, when you click on this cell, it's called your rate.
01:03
And, uh, when you go back to this sheet, you can click here equals
01:08
euro rate,
01:12
and you get it
01:14
back here. So you get to the exact value that is in that cell.
01:19
Why is this important? Because you can have this Ah, some of the important cells on some sheets and you can have
01:27
I don't know, 20 sheets or 30 sheets in the Orem in your Excel file, and you can have a situation in which you have to put in one formula four or five standardized the cells that you frequently use. Ah,
01:48
and they're all in different sheets. So,
01:51
uh, you can make the life was much simpler for you if you give the names to these cells. For example, if you have your a rate you give If you have ah, pound rate,
02:02
you can
02:04
or you can have Canadian dollar rate or Australian dollar rate.
02:08
You can make it much simpler because then you know how you name these cells and you don't have to say, Okay, let's go to this sheet and so like to sell. You just type its name.
02:20
Now if you give too much
02:23
names to to your, um,
02:27
to your two too many cells, you might get in
02:34
in in some kind of problem because you have to remember them all.
02:38
So in that case, you go to Formulas Tab
02:43
and you have here a name manager
02:46
and you click unnamed manager, and it lists you all the names you have given in the cheat. So we have just one here. It's called your eight
02:55
and it's ah It's simple, so it gives you the value of that cell, and it gives you the reference in in standard format. So it gives you a reference to book one,
03:07
and then the door sign bead are signed to meaning that whatever you do with this formula, it will always refer to this cell. And what actually excellent does is when it makes Ric electives formula. It looks for this name.
03:24
It goes for its reference and put
03:28
there before recalculation. You don't see it. Your formula always stays with the name in it. But when Excel calculates, it does it
03:39
in that way.
03:42
So let's just keep this and let's just use one more way to define a name. So except except clicking in these books
03:52
and, for example, hearing picking dollar Well, you can go to define name
03:58
and now you can
04:00
you see that excel automatically copies
04:03
from the header of that sells says, Well, you it didn't put dollar value because you cannot have dollar signing in
04:11
in your name. But you What you can put here is, for example,
04:16
dollar well, you
04:19
Ah, and remember that this is not a case sensitive, so you don't have to put capital or uppercase letters wherever. Then you have here a scope. So it means that this name can be used in entire world Brooke or Entitle file.
04:36
Or it can be used just in this sheet or that sheet or whatever. So we leave. It'd
04:42
workbook. And then we have a reference here, and we can do it like this as well, and we click here. And so this is it. And now if you make formula and we say this cell is equal
04:58
25 times
05:00
dollar,
05:02
well, you,
05:04
you see gives you from the list of the names and you click and you get this
05:12
also here
05:14
If, uh
05:15
if let's go to the name manager
05:19
and let's click in your rate s So you have this reference here, which is book one. String the string,
05:29
too. Let's copy it is,
05:30
and you don't want to use the
05:35
name. You just want to refer to the cell as well.
05:39
You get the same results. So by naming a cell, you didn't exclude its reference in the standard XL way of referencing cells from the possibility when when you want to use that cell. So if you want to use the name, you're right, you can use it or you can use
05:58
actual reference of that cell.
06:00
It doesn't matter.
06:01
So let's get back now to our fuel sheet.
06:06
And let's say that we have entered
06:11
a couple of more off these purchases and I've been to enter all of the data, just
06:16
se Cupp more or three more. And now we have all these purchases
06:23
and I want to
06:26
Ah,
06:27
let's see
06:29
named this range as volume
06:32
purchased and percenter.
06:36
And I want to calculate with some of these cells No, I can do it like in the standard of a quick note, the sam and get this. But this is not what I want to dio. I want to say son off. Let's let's say I want to do it here so I can adjust.
06:55
So to sum it, or I can type summoned in select sales. But I can also say
07:00
some off
07:03
and I get here automatically. When I take a couple of characters, I get the name because it's not on the list off defined names, custom names, some bullion purchased. Let's close it
07:15
and we get this amount here, which is equal. If you see when start, their cells will get automatically. Here at the bottom
07:24
the sum of these cells. It says 102 to 3.69 which is exactly the same here. So we are. Instead of typing this range or selecting this range whenever type of cell, we can just
07:36
type of volume purchased or just type B A well, and then it will give us the list of names that begin with you. Well, if you don't have any other will, just select this and do this. And if we go to the name manager, we now see that William purchased is on the list off.
07:56
Uh, do you find names or custom names that we have created an XL?
08:01
So this is all about naming cells and rangers in Excel.

Up Next

Intro to Excel

This basic Excel training course will give you knowledge of Excel and the ability to use this software to do elementary calculations, file manipulation, data manipulation, to create charts, pivot tables and templates.

Instructed By

Instructor Profile Image
Milan Cetic
IT Security Consultant
Instructor