Welcome to Office 2013 Class Video Number 36.
If you want to download this workbook,
and this is for the videos 35 to 39, click on the link
below the video, or go to our class website.
Hey, we got to talk about Defined Names, a great feature.
Now, earlier in the class, we talked
about Tables, the Excel table feature which
allowed dynamic ranges, and we used
Table Names in our formulas.
now Defined Names are a little bit easier.
They're not dynamic like the tables,
but if you have a situation like this, we have a huge data set,
and we're actually going to be making calculations
on the same column.
So sales, total sales, number of sales, max sales, min sales,
we're going to repeatedly be using this column over
and over.
If you have a case like that, we can use Defined Names,
and what that means is, we actually
will highlight the entire column, give it a name,
and instead of using the range in our formula,
we'll actually use the name.
Then we'll give it a smart name, the field name,
so we'll know that's for the sales column.
The three benefits we're going to gain
from using Defined Names is, well we won't have to highlight
the whole column each time, which takes extra time,
we'll actually avoid errors because if we're highlighting
the same column over and over, maybe one time
we forget a cell, and there'll actually
be the name, sales, in our formula.
So it'll directly tell us which column
that the formula's looking at.
All right, so to create names, there's
a couple of ways we can do that, but let's look
at the easiest way.
I'm going to click in the top cell, not
the field name, the actual data, and use our keyboard
to highlight the column, Control, Shift, down arrow.
Wow, that's a lot of rows.
That's like 2000 records.
That's going to save a lot of time
repeatedly highlighting 2000 rows.
Now, with the range highlighted, up here in the formula bar
is the name box.
If you hover your cursor, you can see that name box screen
tip.
All you have to do is click inside the name box
and type a name.
You can't have any spaces, there's
some characters you can't use, and you
can't start with a number.
So, I'm going to call this, sales, and when I hit Enter,
just like that the range is now called sales.
Let's test it.
I'm going to click somewhere else.
Notice the name box says G29, but when we use our drop down,
and click on the define name sales,
instantly it highlights that whole range.
Now for naming conventions, I do have some notes
up here, that's like some of the characters you can use.
A couple more things about Defined Names that
are important before we use them, hey, let's
go up to the Formulas ribbon, and there's
a whole group just on Defined Names.
We want to look at Name Manager, now notice
the keyboard shortcut, Control, F3.
If you click here, you'll see your list of names.
This dialogue box is important because you
can delete, and edit.
Sometimes your range changes, or you made an error,
so you can click Edit, you can edit the name,
and notice Refers To.
If I point to this lower corner, and click and drag, Refers To,
this is actually a formula that has the sheet name,
and the range of cells.
Click OK, so you can edit that and change it.
I'm going to click Close.
Also, in the Formula's ribbon, define groups,
there is Use in Formula, if you forget your name
there'll be the list of names, and we'll
see this one, Create from Selection,
which is really awesome.
We'll see that one in just a second.
Notice there's keyboards for all of those.
Create from selection is Control, Shift, F3.
And Use in Formula, it doesn't tell you the keyboard,
but I'll show you in just a second.
Now let's try this.
For Total Sales I'm going to do Alt, equals,
that's the keyboard shortcut for the AutoSum,
and if you forget your names, hit the F3.
F3 opens up Paste Name, and if you have a lot of names,
it's just a great feature.
All right, so you can double click it, and it puts it in.
That's one way to put the name in.
How about Number of Sales?
Wow, that's a lot of total sales.
I'm going to format this, control 1, currency,
and no decimals, click OK.
All right, so number of sales, well these are numbers,
so we use Count, and watch.
When we have our function, and it's asking for value,
usually we put a range, we type and s, and in the drop down,
we see Function Names, earlier we saw that we had table names.
That dog tag icon there means it's a defined name.
As soon as I see it highlighted, I hit Tab.
I love that.
Enter, now remember our goal is to create our formulas more
quickly, and more accurately.
So by using Defined Names equals MAX, for max, s, Tab, Enter.
Equals MIN for minimum, Tab, s, when I see my Defined Name,
even if I have to type a few letters,
or arrow down as soon as it's blue, Tab, Enter.
I absolutely love Defined Names.
How quick and easy is that?
Now, notice this is a proper data set with field names
at the top.
Lots of times you're absolutely using every single column.
So, over here we're going to use the sales column,
and we're going to use the region column, because we're
going to be adding for each one of the region's, using
the Sum If's function.
And then up here, I want to use some functions
on the date column.
So, check this out, we can actually
highlight the entire data set including field names.
Our keyboard is Control, Astrix's.
Now, the key to this trick, you would go up to Formula
and it's called, Create from Selection.
The key is that you have a name, some text, above each column,
just like most proper data sets.
That's why they made this feature.
So, now you just go up to Formulas,
Create Names from Selection.
Now, I do this all the time, so I'm
going to do the keyboard shortcut Control, Shift, F3,
ready?
Control, Shift, F3, and just like that it's polite.
It says, where are your names?
Now, some data sets have names on the left, and the top,
so it's trying to be polite.
Uncheck left because do we have field names over here,
or names for each one of these rows?
We do not.
We have field names only, that's the top row.
Click, OK, and you're not going to believe this, watch this.
A whole Defined Names now has the customers
we're testing use in our little drop down date, just like that.
And notice it's not including the field
names, the actual field names were
given as names for each one of these columns.
Formulas, Name Manager, or Control, F3,
that is absolutely beautiful.
So, if you get a big data set, and you're just
constantly using the columns, and functions and formulas,
this is amazing.
Now, let's go do a couple of calculations.
How would you find the first date, or the last date?
Well, it depends on whether you know dates are serial numbers,
or not, because they're numbers, guess what?
We could just use Min, for the first date, and Max,
for the last date.
This is important because sometimes you're
going to go do some analysis, and you
need to know, what is the range for your date?
So, for first date, that equals Min.
D, A, and check this out, there's a date function,
and a date Defined Name.
That's why those icons are so important.
I want the dog tag, so Tab, Enter.
Oh, what's happening, here?
We're not fooled at all, we know that it
doesn't have the date number format.
So, I'm going to highlight both of these cells, Control, One.
And under date, there's all sorts of amazing things.
We actually didn't even look at this back in our number
formatting.
Look at this one, if you wanted to see the actual day,
you could select that, or any one of these.
So, even though there's a serial number underneath,
we can display it in many different ways.
I'm going to select the one at the top.
By the way, try this one, it's cool.
It tells you the actual full day name.
All right, so now both of these cells
have the date number format, so 6/7/2004,
is the first date, equals Max, Tab, d,
I'm making sure I see my dog tag, and not the function icon.
Tab, Control, Enter, so it looks like the dates
are from 6/17/2004 to 6/16/2006.
Now, let's do one last format here,
and we'll see an added benefit for Defined Names.
What do we need to do here?
Well, we're adding, I want to add from the sales column, only
the sales for southeast, west, north.
Remember, this isn't adding all of the sales,
it's adding just some of them.
So, we can use the Sum If's, equals Sums If's.
The screen tips are polite.
The argument I'm looking for a Sum Range,
so that means the numbers I'm adding.
So sales, s, oh, and look there's our sales rep.
Tab comma criteria range, well, I need region.
How about REG, Tab, comma, and then the criteria,
one cell to my left.
Now here's another benefit, when you create
Defined Names, that's absolute.
So, I don't have to worry about locking them.
That, of course, is not because when I copies this formula
down, I need to look relatively at each one of the one cells
to my lab.
All right, close parentheses, Control,
Enter, and double click and send it down.
Go to the last one in F2, that is amazing,
look at that, Defined Name.
So, It's accurate, it's fast, it even is absolute.
Now a couple more important things, actually,
Highlight, Control, 1, Currency, Zero Decimal Places, click, OK.
Couple more things, you probably may
have noticed in the drop down over here,
what happened to sales rep?
Because we had a space right here,
you're not allowed to have spaces in Defined Names.
Let's go to Formulas, Name Manager, or Control, F3.
And sure enough, you can see it there, too.
If I click Edit, it was really polite,
it put an underscore for us.
And so, that's what we'll do.
If you have to have a space in your field name,
it'll be polite, and put an underscore.
Also, notice here, we can see, oh, it is absolute.
Now, it is extremely rare that you ever change it
to anything besides absolute, but if you ever had,
guess what?
This is totally editable.
You could change it using your F4 key.
All right, I'm going to click Escape, Escape.
Defined Names, quick and easy, instead of highlighting,
accurate, because we're not making selection errors.
You can actually see the field names,
or whatever names you decided to call your columns .
And then the fourth benefit, absolute if we're copying.
All right, we'll see you next video,
and next video, we'll talk more about SUMIFs and COUNTIFs.
And we'll finally see some examples where
we have used these criteria, too,
so we have more than one condition, or criteria.
All right, we'll see you next video.