Welcome to Excel Basics Video Number 18.
In this video, we got to talk about the Defined Names feature
and use defined names and Excel formulas, functions,
and for jump go to.
Now, we're going to learn how to name ranges and then
use those names in formulas.
We'll see our list of names in the name manager,
and we'll also see how to use defined names
to jump to various ledger accounts in your Excel
workbook.
Now, let's go over to the sheet Define Names.
Now, defined names sometimes are great.
We're going to use the PMT function to calculate
our monthly payment.
But inside our formula, it would be nice,
instead of seeing B3, B4, B5, it would be nice to see
the name of the variable.
Now, defined names-- you can actually
select a range or a single cell and name it anything you want.
I'm going to click in B3 and up here in the formula bar
is the name box.
If I click, I can type any name, and now that cell
will have that name instead of B3.
I'm going to type Loan_Amount.
But guess what?
Spaces will not work.
Now if you want a space, you have to use an underscore.
Now when I hit Enter, that cell is named--
I click over here, I see C5.
When I click back there, I see Loan_Amount.
If I click in the cell and click the dropdown
and select Loan_Amount, it jumps to that cell.
Now, I'm going to show you where your list of defined names are.
We go up to Formula, Defined Names.
And there it is--
Name Manager.
There is a keyboard, and there it is, control F 3.
Now if I click this, it will show me
that we only have one name.
There's the name, there's the value,
there's the range it refers to, and scope.
If you have the same name in multiple places
in your workbook, when you insert the name,
it'll ask you whether you want to use
the one from the workbook or the particular worksheet.
All right.
I'm going to delete this.
By the way, you can edit, create a new one here.
But I'm going to delete it because I
got to show you a better trick than naming them manually.
Click OK.
Click Close.
Almost all times that I've used Defined Names,
I have the actual name directly to the left.
And so there's a feature that will automatically name these.
Not only that, but when we get down here,
this whole column has the name at the top.
So there's a great feature--
you highlight the names and any numbers or any cells
that you want to contain those names, go up to Defined Names,
and there it is--
Create from Selection.
There is a keyboard, there it is, control shift F 3.
I'm going to use Create from Selection.
You got to be careful.
You got to read this, because sometimes they'll
check the wrong box.
But this has no problem interpreting number number
number, and this is text.
Now the polite thing about Create Names from Selection
is, notice, space, space, it'll automatically
put an underscore.
Right there, I didn't put a space,
so it will be exactly that name.
When I click OK, now up in the dropdown,
there is my lone underscore Amount,
there is the total months.
Now, I can come down here and use those
defined names in a formula.
Equals PMT.
We learned this function earlier in the class.
Great for calculating a period loan.
The rate-- I'm simply going to click and look at that,
it puts monthly rate.
Comma-- that's the total number of periods,
I click on total months--
comma, and I click Loan_Amount.
And that's a pretty awesome formula.
If I click parentheses, someone looking at this
can literally read it left to right
and understand what's going on.
When I hit Enter, there is the monthly payment.
Now, one other cool thing about defined names--
I can come up to the Name Manager,
and I want to be consistent with an underscore,
so I'm going to click on TotalMonths, edit, e
and now when I come up and add an underscore,
click OK, click Close, I don't get an error there in F2.
It'll totally update.
Now, let's come down here.
I want to name these three columns,
so I'm highlighting the names at the top and all of the data
below.
It will interpret the whole column
as Date, the whole column as Sales.
Create from Selection or you can use your keyboard,
control shift F 3.
Now be careful.
It definitely checks the wrong thing sometimes.
I do not want the left column--
meaning, each one of these dates,
to be the name of the row.
So I simply make sure top row is selected and click OK.
Now I can come over to my dropdown and sure enough,
there is Sales.
Now, there is a drawback to defined names.
They're not dynamic like the Excel table feature.
If I add new records, it won't automatically update.
You can go edit it, but it won't update like an Excel table.
If I'm using datasets like this, I
tend to use the Excel table feature now.
But I got to show you defined names,
because this example up here is a great use that we still
use all the time.
And this one-- you may bump into spreadsheets where people use
defined names.
So it's good to know what they are and how to use them.
All right.
Our goal here is to add with the SUMIFS function.
I need to add not all the sales, but the sales just
for Joe, Gigi and Chin.
In the sum range, I'm either going to highlight the column--
and notice, it will automatically
put the defined name--
or comma, criteria range--
I know I want sales.
I can start typing, and from my dropdown,
I can see the f of x, that means function, gold dog tag, that
means defined name.
You also will see the table icon and table names
in this dropdown, as we saw in an earlier video.
Now I'm going to down arrow and tab to get SalesRep.
Now I type a comma and criteria one,
I left arrow to get the criteria for adding.
Close parentheses.
And there it is.
Defined names in formulas.
Not only that, but they're totally locked
and absolute by default. So when I control enter and copy it
down, go to the last cell and hit F2,
they are totally locked, pointing to the correct Sales
and SalesRep column.
Enter.
Another great use for defined names,
and for that matter, the table feature with its table formula
nomenclature, is I'm going to use this column over and over
again.
So instead of always having to do my different formulas
and highlight the range, I can simply use our defined name.
Alt equals-- keyboard for the sum function--
I type the letter S. I see Sales highlighted in my dropdown tab.
And Enter.
Equals Count.
I want to count how many sales we have.
S. I see it there, Sales.
So I hit Tab.
Now here's a great trick.
This is a simple function with a single argument.
We actually don't have to type the close parentheses.
If I Control Enter and look up in the formula bar,
you can see it totally put it in for us.
Enter.
Equals, I need to find the biggest one, so I use Max.
S tab.
I'm going to hit Enter and it'll put that parentheses in for me.
And sure enough, it got the max.
Equals Min tab.
S tab.
And Enter.
So I've used defined names to quickly enter a range when
I'm doing various calculations.
Now, our last example.
I love this example.
I use it all the time in large workbooks.
Here, we want to use defined names
to jump to a certain ledger account.
Over here, I have the cash account, AR, sales.
And for each one of these, I want
to give whatever sale a particular name
so I can use the dropdown in the defined names box
to jump to a particular range.
Now, I'm going to select J1 on each one of these sheets.
Click up in the name box.
And I'm going to call this one Cache and Enter.
Now I'm going to jump over to AR, click in J1,
click up in the name box.
AR Enter.
Come over to Sales.
I already have a range called Sales,
so I'm going to come over here, click on the name box
and call this Sales400, because that's
the number on this account, and Enter.
Now, notice, I'm on the Sales sheet.
If I want to jump to the cash account, I simply select it
and instantly I jump over to cash.
Jump to AR.
There I am on AR.
If I want to jump back to that defined names sheet
where we started and I want to jump to the SalesRep column,
I simply click it and there I have
jumped to this defined name.
All right.
We saw how to create defined names in formulas,
using the awesome Create from Selection.
We saw that it's sometimes convenient to have
our cells named in our formula.
We also saw how to name each one of these columns
and then use it in SUMIFs.
We also saw that either defined names or table formulas
nomenclature from the table feature
is create when you're using the same range over and over.
We also talked about the limitation of defined names.
They're not dynamic.
Although you can go up and edit them if you have to.
And then we saw the defined names
to jump to a certain ledger account.
All right.
If you like that video, be sure to click that thumbs up.
Leave a comment.
And sub, because there's always lots more videos
to come from Excel is Fun.
We'll see you next Excel Basics video.