# Excel Functions

## Overview

Excel is more than just columns and rows of data. Excel can perform calculations from simple addition to complex formulas with its vast library of named functions. In this series, we cover everything you need to know about crunching numbers in Excel. There are multiple sections ranging from the very basics to more advanced data analysis to a reference guide of the different Excel functions by category. This series is for beginners as well as advanced users of Excel. Functions are demonstrated on locally installed Excel 2016 on Windows operating system.

Excel Functions Overview

In this episode, Daniel and Vonne start the Excel Functions series by laying down some foundational knowledge and terminology. Specific concepts/definitions discussed here include: formulas, functions, relative reference, absolute reference, and named ranges.

0h 25m

[MUSIC]
All right, greetings everyone and
welcome to another exciting
episode of OFFICEPRO.TV.
I'm your host Daniel Lowrie, and
in today's episode we're actually
starting our series on Excel functions.
Kind of taking it to the next level
with what you can do inside of Excel.
Joining us in the studio as always,
our resident expert on Excel,
Miss Vonne Smith.
Vonne, welcome back, how's it going today?
It's going swimmingly.
Swimmingly, mm.
Yes, I know.
Do you have to put your pinky
finger up when you say that?
Swimmingly.
Swimmingly.
Yes, lovely, lovely, so.
[LAUGH]
We do have the means.
[LAUGH] Yes, we are starting
a new series on Excel functions.
It's gonna be a lot of fun.
And we're gonna start
today at super basics.
We're like the basics of basics.
Introduction to Excel functions and
really, this is where we're
actually going back a little bit.
Really.
Yeah, and
I purpose didn't tell you what we're going
to be talking about in this episode.
Yes.
I'm kind of like set
back a little bit on that.
[LAUGH]
She's looking at me like,
I have plans for you.
I know.
I mean Excel.
[LAUGH]
It's gonna be fun.
Yeah, on several.
We're gonna go back, back in time.
Imagine if you will.
Yes.
Picture this.
Back.
Sicily, 1922.
[LAUGH]
Yes, Stewart Florida,
Stewart Middle School.
[LAUGH] Mrs. Levi's math class.
And this is where I actually
remember this from.
No.
[LAUGH]
You said the horrible word.
I did.
Out comes the flop sweat.
[LAUGH] Because, of course,
when we're talking about Excel functions,
functions are really formulas, and
we're doing formulas, we're calculating.
Are you gonna put
the fun back in functions?
We're gonna try.
Okay.
[LAUGH] So
I have a PowerPoint presentation that
we're gonna start to kinda help us along.
So I didn't do any major themes and
stuff like this, just-
Keep it simple, right?
Yeah.
[LAUGH]
[LAUGH]
Wait, this is black on white.
Yes, there we go.
Black and white.
So we have Calculation Basics,
all right, ready?
Go for it.
Do you remember this?
Please excuse my dear Aunt Sally.
You know it that way too.
I do.
Yes, that's how I learned it.
My gosh, okay.
[LAUGH]
Must be a Florida thing.
It totally is.
[LAUGH]
Cuz I've had people in class,
I've been teaching Excel for a long time.
Yeah.
We always start with this
particular one.
I write it on the board,
and people are like.
I have a dear Aunt Sally, and
you have to excuse her a lot.
[LAUGH] Yes, and
then every once in awhile somebody,
usually around my age,
Yeah.
And they would say that's
Please Excuse My Dear Aunt Sally.
And the other people would go, what?
And then I said this is
orders of operations.
Right.
And that's where they're like-
It's a little pneumonic for
remembering the order of operations.
Yes.
And that's where it's,
okay Please Excuse My Dear Aunt Sally, and
then it was parentheses, exponents.
Multiplication, division.
Division and subtraction, yes.
And then they're like, yeah, that.
So they never learned the little-
[SOUND] That.
-Dear Aunt Sally thing.
And that's where I always like,
well, where'd you go to school?
I thought that was almost universal,
man.
It was like where did you go to school?
When did you go to school?
So I think it was more.
Something that came out
when we were in school?
Correct.
And it's a Florida thing.
Cuz I've had people my same age,
that didn't go to school in Florida,
like we weren't taught that.
I don't know what you're talking about.
Yeah.
So that's where I thought this was my
experiment and you got it right away.
And I knew.
I got real excited.
[LAUGH] So but yes,
this is the whole order of operations.
And this is where we said okay, this is
how you have to perform calculations.
And the whole idea was
that multiplication and
division and addition and subtraction,
they're on the same level.
And that was like the whole right to left.
So, I have some examples,
so this right here.
We have.
If we did not do the order
operations we would say okay,
8 minus 3 is 5, times 2 is 10,
minus 4 is 6 divided by-
Divided by 2 is 3-
Yeah I'm not good at math in my head.
Neither am I don't feel bad
But that's not right.
No.
Because then I have a slide here of
this is where if we did it the wrong way,
that's why I have the little numbers, 5,
10, 6, 3.
I assume they're behind your head.
[LAUGH]
Yeah,
the answer's 5 right
there off to the right.
I made these slides to where it's
centered for me not centered for them.
Cuz it's just like my brain wrong side.
[LAUGH]
I can't put it over to the left even
though my box is gonna be, [CROSSTALK]
You will learn.
I know, right?
[LAUGH]
But the correct answer is 2 because
the whole Please Excuse-
Right.
-My Dear, and that's just where when we
had our formulas in math class you'd have
to put the PE, do that whole thing and
then you put like a little check box,
okay there's no parenthesis.
Okay, so Please Excuse,
there's no exponents.
There's, okay division and multiplication.
In this sense you would underline it,
right?
Okay 3 times 2 and then 4 divided by 2.
We would have to perform those, and
it really didn't matter which one we did
first because, but-
Cuz both of ' are kind of broken apart.
Yes.
Yeah.
Remember the whole show your
work.
Yep.
Goodness.
Yeah, all those steps.
And so then we'd say now that we're on
the addition and subtraction now okay,
8 minus 6 minus 2 plus 2 is 2.
It's funny Justin at
Death Row is a math major and
he was talking to me about this last week,
I think.
And he said, in math when you do
math as like your job the answer is
not what's important it's the formula and
how do you prove it?
And that's why they put such
emphasis on show your work.
Show your work.
Yes and that was the ones like,
cuz you wanted to combine multiple steps.
Yeah, no can't do that.
And they're like no every single line.
You've gotta prove what you've done.
Yeah.
So we have a fun one, ready?
Yeah.
So we're gonna come to our
fun sky cam view.
Sky cam?
Yes.
In an Excel show?
I know.
My goodness.
It's almost like we're a tech show.
I'm gonna throw up.
[LAUGH] And I have my trusty little
pen with all the different colors on it.
Okay.
A friend of mine got this from Ireland,
from Belfast.
That's cool.
Yes, and
this is also talking about middle school.
I figured we'd make a whole
middle school reference.
Cuz these pens, and
I don't know about anybody else.
I do, we had them when I was a kid.
My gosh, I had one that had-
Very popular.
-like 16 colors on it.
Yeah.
And they had all,
the thing was like this big,
it was massive.
Every color under the rainbow.
Because we also had back in
the day before we had cellphones, so
we would write notes to each other.
Correct.
And you always had to write you notes
in at least 15 different colors.
Yeah, yeah, it's how you roll.
So, [LAUGH].
So many a girls note, so.
[LAUGH]
So we would have to come up here, wow,
okay, Please Excuse My Dear Aunt Sally.
So we'd put that across the top, right?
Yep.
And we'd say, all right, so
we have to go through and
parentheses first.
All right, so let's do the parentheses,
we'll make those blue.
So everything in here, all right?
There we go.
Is gonna go first.
Is gonna go first.
But then there's a parentheses
within the parentheses.
It's inside of the parentheses.
So, you've got to do
the inside parentheses first.
You've got to do that first.
So, 5 minus 2 is what?
It's 3.
3, and
we're gonna combine some steps,
just because.
All right so, now we have 3.
Positive three.
Yes.
And then we have times 3 plus 7.
I'm going backwards.
Okay, so now we don't do 7 plus 3.
What do we do?
We do 3 times 3, which is 9.
Yes and that's 9.
7 + 9 is?
16.
16, all right, so
we've got all of that out of the way, so
now we're gonna go ahead and to
the second, times 7 divided by 4 times 2.
All right, so now and
then we do this, remember, right?
Yup.
Parenthesis are done,
so now we do exponents.
What's 2 to 2?
2 to the 2 is 4.
4,
all right, times 7,
plus 16 divided by, woops that's 4.
This is why I should write in pencil.
Yeah.
All right, so now we got exponents.
Now we need multiplication and division
and that's where we have to come in.
All right, so here is 4 times 2, but
then there's also 16 divided by 4.
But PEMDAS says multiplication first.
But remember, multiplication and
division are on the same level.
They are on the same level.
Yeah, so then it goes left to right.
That's right, so
you do 7 x4 first, right?
Yes, there's one here too,
I forgot that one, yes, okay.
See, this is why my handwriting is so
horrible and why it's so
good that we have computers now.
[LAUGH]
All right, so 7x4 is?
28.
Yes.
No, right?
Yeah, 28.
Yeah, [LAUGH] we're so
reliant on calculators though.
Yeah, I know, I told you, I heard math,
and I was like, I'm gonna cry.
[LAUGH] And so now I changed my colors
in the middle, so now 16/4 is-
Is 4.
4 x
is?
8.
8+ 28, the answer is?
36, right?
And remember, you have to circle it.
Yeah, right,
you did very well.
Right?
This was like being in
middle school again.
I know.
I made an F.
[LAUGH]
Yeah, [LAUGH] I got the pen,
I got the paper, yes.
You were there.
All you're missing is
Madonna bracelets and-
And
a lot of crying.
Yeah,
that's true.
[LAUGH]
[LAUGH]
I make it sound like middle school
was the worst thing in the world.
It was pretty bad, but I mean,
[LAUGH] it's okay.
It wasn't that bad.
It wasn't that bad, I survived.
So [LAUGH] you can see that this was our
formula that we did on the piece of paper.
Now we know what's wrong with you.
[LAUGH]
You got that right, so [LAUGH].
Badoom shh, thank you, thank you.
[LAUGH]
So I didn't wanna have to try to do
that on the computer, so
that's why this guy can't.
It was fun though, right?
Yeah, it was fun, it was a good time.
Yeah, not bad.
So now that we all remember
our order of operations,
how does that transfer into Excel land?
Because if you're gonna be
doing calculations in Excel,
you need to follow these rules.
If I want to add up all of these and
then add up all of these, but
then take the result of this and
divide it by this, well then,
you're gonna have to put parentheses
around things, aren't you?
Yeah, so do this first, do this first, and
then do what's ever in between it, or so
on and so forth.
So we're using numbers on
this particular example.
And actually, let's go back over here to
my PowerPoint presentation cuz I just had
it on the slide now.
If we were doing this,
I mean, this is a formula.
This is where I need to make this result.
I have to do this,
then this, da, da, da, da.
All right, well,
this is the end of the PEMDAS.
Cuz now, we're getting into how is
this correlated into Excel, and
like I just said,
it works on the same things.
So, what we have here when we
start getting into functions and
formulas in Excel.
We do have some vocabulary that's gonna
pop up throughout these episodes here.
And I just wanted to take this
moment to just, well, identify it.
I'm not gonna show you how to do
calculations in Excel right now.
I'm just showing you the end results, so
we can identify,
that's what that thing is.
That's what that is.
That's what that colon means.
It's a true introduction
into functions in Excel.
Correct.
Start from scratch.
Nobody knows nothing,
everybody here is a newborn babe
when it comes to Excel functions.
Let's start there.
And the one thing that we did talk
about before this episode was the whole
idea of just getting that foundational
terminology to then not use it right
the whole time.
[LAUGH]
[LAUGH] Yeah,
now that I've told you exactly what
that is and the correct terminology,
forget I even said it.
[LAUGH] I don't say that.
Because I'm gonna use another
word that, to me, that's what it is,
but it's not technically right, yeah.
So that's why we're gonna be technical,
and then we'll-
We'll have the editors go back in and
be like, function.
[LAUGH] Vonne's mouth is
doing something different.
[LAUGH]
So your formula's functions cell
reference, now cell reference, we already
kind of talked about in other episodes.
It's like if we say cell A2,
that means the column reference and
the row reference,
that's the cell reference, okay?
But then there's something known as
relative reference, absolute reference,
and there's also name ranges.
These are just basic stuff.
There will be other terms
that come out through here,
because this Excel functions series, this
show, this is gonna be a pretty big one.
Cuz there's gonna be lots of
different topics in here.
You probably already see that over in the
course library, where it's this episode,
it shows our different topics.
And we're gonna have a reference
guide with everything.
This is gonna be like
the never ending series.
We're just gonna keep
adding to this forever.
Two years from now, Excel 2018 just
came out, we're gonna scrap all that.
[LAUGH]
Well, that's the other thing,
is like we're not doing this platform
specific or version specific.
Because if you think about-
Math doesn't change.
Exactly, and there's very few changes
that happen throughout them with
functions, specifically.
And just to start clarifying,
a function is just a named calculation,
okay, it has a name.
Which it really is a formula,
so to me, I'm like, okay,
what's the difference between
a formula and a function?
Why don't we jump over here?
Let's go ahead and cancel this cuz that
was the end of my PowerPoint presentation,
and I have my Excel file.
And this is just my introduction examples,
and we have our basics, all right.
So I just have some numbers,
they don't mean anything right now.
They're just what we've got here.
So if I have, so let me look at my notes,
E3, okay, so E3, look at the formula bar.
Now remember where the formula bar is,
right?
Yes.
The formula bar is up at the top.
It's up where the formulas go.
Yes, exactly.
So we need to make sure that in the
formula bar, every time that you're going
to perform calculations in Excel,
it always starts off with an equal sign.
So, see right up there that the cell,
the information says 7, right?
That's what we see, but it's a result of
adding up C2, this cell right here, + C3.
It really is 3 + 4 is 7, but we didn't-
Last time I checked.
Yeah, [LAUGH] but
we didn't code it as 3 + 4, this is-
Right, you just did the cells.
Yes, and the =, when you put the =
on the front, it's telling Excel,
do this calculation.
I would say this would be a formula.
It is just taking this
cell plus this cell.
Right, cuz you haven't given it any
name, you haven't said this is something
that I want to always be, and
I just referenced that named function.
It's not a named function.
Right.
So that's my clarification.
So I think of it in program, I do a
little bit of programming, dabble in that.
And sometimes you have a little piece of
code that you're like, you know what?
I can reuse this in a bunch
of different programs, so
you create a function, and it has a name.
And all I have to do
is call that function,
and it does whatever's inside of it.
Yeah, [LAUGH] I'm like-
Yeah, they're doing it here.
That's exactly it.
[LAUGH] Yeah, so yeah,
you can totally correlate it into
other things in computers, perfect.
So if I come here to this cell,
now the answer that I see is 27,
but if you look at it, it's saying,
remember what we talked about,
the Please Excuse My Dear Aunt Sally?
Yes, ma'am.
Here it is in the flesh.
We're saying add up 3 + 4 + 6 + 5,
which is C2.
Here, let me zoom out just a little bit,
so we can kind of move over.
Here, let me zoom out.
There we go, so you can see, C2 + that
one, + that, + that are cell references.
Do all of that first, and
then divide it by B6,
which is 2, and then times C8.
So 7 + 6 is 11, + 5 is-
You're getting it wrong now.
[LAUGH]
Wait, 3+4 is 7, + 6 is 13,
+ 5 is 18, x 2 is 36.
No, divided, 18, 9 x3 is 27 [LAUGH].
Wow, you can see-
Yeah, buddy.
I'm pretty bad at this in my head,
[LAUGH] calculator.
That's what we made them for.
Yes, exactly, but I mean,
these are just numbers.
But you can think about this and like this
is what I want my information to say.
So these are all formulas now,
but look at this one.
Look, it's E5, cuz I move my, there we go,
perfect, I just move that mouse over.
[LAUGH]
That's how it works.
[LAUGH]
So
this is saying that this is a function.
Okay, it still starts off with the equals,
so it's performing a calculation.
But SUM, hm, what do you think SUM means?
Last time I checked,
it's the aggregation of two Numbers,
the [CROSSTALK]
Whoa, you just went too far for me.
[LAUGH] Aggregation, what?
I can take two numbers.
I got a three, I got a five.
I put them together I got eight,
that's the sum.
Add, yes, there you go.
We add them together, exactly.
So now what we have is
that's the function name.
So SUM is going to go ahead and
add up our numbers.
Then the four that you see
what's in parentheses here.
You could see that this is
open parentheses C2 colon C5.
Alright so you're basically saying
in the parentheses do that function.
Add up C2 through,
that's what the colon means-
Mm-hm.
The colon means, seat this right there.
Right, so
it's some sort of range you give it.
Exactly, yes and so if I actually
come into here to start to edit it,
you can see there it is, that's my sum,
that's what I want you to add.
I'm glad you really pointed that out,
because a colon in my brain.
And says this and this right?
And not a range a range is
usually a dash this through this.
That is interesting.
Yes.
So, that is what
it means in this syntax.
Yes.
It is funny you say that because it is
almost like, I know I did not give
you any notes for this episode.
Zero notes.
[LAUGH]
For this show.
Because well look at this one,
this one is the result of eight that is
adding up C2 and C5, the comma.
Okay, the comma makes sense,
I totally get that, cuz Excel loves
those comma separated values.
Yeah.
So I get that,
I've been there enough to go, okay, okay.
But if I see a dash come up, and
it's not a range, I'm gonna freak out.
Yeah!
[LAUGH] Well, now you know it's a colon.
So there you go, so this is saying C2,
which is three plus C5 which is five,
three plus C5 is eight.
Yes.
Okay so there's our result.
So you could see, formula,
just cells doing some type of calculation,
using the cell reference.
Yeah.
A function with a named function, and
the parameters that go
into the parenthesis
Whatever is in those parenthesis, do that.
Right.
And then colon versus like a comma
that you might see from time to time.
So those are some basic
identification of what you might see
when you're looking at formulas
within the formula bar.
Let's come over here.
Now, what I have here is the result is 18.
Okay, 18 of, well, what does that mean?
[LAUGH]
Look at the function,
out the formula bar.
[LAUGH]
Equal sum, functioning,
basics exclamation point.
Whenever you see that,
that means the sheet name.
Look at the name of
the worksheet down here.
Basics okay.
So it's saying on that worksheet
take the cell range of C2 to C5.
It's just these cells again so right here.
Here are my cells and
that's what i'm naming so looking
that different syntax exclamation always
going to be referring to sheet names.
When we get into where you can actually
perform calculations across multiple work
books, multiple files.
Yeah.
Then your file name is in,
I wanna say they're in brackets.
Angle brackets.
I don't remember.
It's one of those.
But they're some types of syntax.
We'll know by the time we get there.
[LAUGH]
When you get
into where it's gonna use
the name of the file.
It's in brackets, okay or
something along that.
This is so
similar to a basic programming language.
It's so funny.
No.
So if you have any programming
experience you're gonna
be going man this is,
I get it now,
let's totally put it together.
We're telling it to do something.
We're making it do commands.
So that's where it all comes,
It all comes from one source, right?
[CROSSTALK]
Full circle [INAUDIBLE]
Full circular.
Yes.
It goes up and
down like a carousel [LAUGH]
Yes [LAUGH], wow okay, no secret.
[LAUGH] We're gonna have some name ranges,
this is one you would also
see in the [INAUDIBLE] cuz we have a whole
Episode on this,
to where look at the result.
We have sum, we know what that is,
number underscore name, what?
Yeah, that's just,
if I double-click it, it's just that.
Because what you can do is intstead
of giving cell references.
You can actually give a cell range a name.
Mm-hm.
Okay and so if you think about this and
look, if I select this,
there it is number, underscore, name.
I believe we did this
in the excel advancer,
there someone else in some of these
excel shows, we've done this before, but
you can give it a name and
if that means something to you.
You could say some of January
sales plus February sales,
it's not just like C2 through C5 doesn't
mean as much to me as January sales.
So I'm just giving it like a alias,
like a name that I want to use.
Well we would call a variable.
[LAUGH]
My gosh, wow you're totally pulling.
I told you.
And I do enough to not know anything,
to where I'm like I know this too.
Very cool.
So named ranges.
And then some other concepts that we
have over here, relative reference and
absolute reference.
This is when you get into some fun stuff.
Remember, we're just demonstrating.
We have here some sales, right?
And we have Fuego and
Bishop and everybody here.
And you can see this is Sales.
This one we've already done, right?
So take our range of doing this
function on B2 through D2,
so January, February and March, perfect.
Well, when I do it once, do you think I
went through and did every single one of
these, and if I had fifty rows I'm
not doing each one individually.
That would be a lot of work.
That would.
And you're real work adverse!
[LAUGH] Yeah.
Yeah here's the thing
I'm not down with that.
No.
Remember the fill handle?
Yeah.
Yes filling in series?
Yes.
Well the whole idea about
doing this when you do it.
Functions or formulas.
If I take this and drag it down.
One, two, three, it's already done.
But what I'm essentially doing is I'm
saying take this first one, copy it down,
but I'm not copying B2 through D2,
it will relatively change its
reference based on the position.
So if I move down one row,
the reference has change down.
You got it, right?
Very cool.
And then you go down to from where
you started, that goes down two.
You go down three, it goes down three.
So as you're filling down,
it's gonna copy and use
the relative reference so then change the-
I'm vaguely remembering this from
one of our previous Excel shows in another
series where we have to work with that cuz
it was like doing something like,
it's because it's a relative reference or
an absolute-
Yeah.
That.
Yeah, that.
I remember that, yes.
It's bringing it back, right?
Yes.
Okay, relative meaning we're relative,
it's moving relative
to where I'm going and
it doesn't just
You can go across here,
because the file going to go ahead and
perform clarifications here, do want drag
it over the reference would change,
it doesn't matter which way you go.
But absolute reference they got wider
versus absolute, absolute doesn't move.
Okay, so
if I was doing a commission right?
This is my commission rate of 10%.
Well, to find the commission of my sales
that would mean take this times this.
And that's what I get.
So right over here I've got this.
Well, now notice here.
I'm already gonna just point it out.
Look at that, you see there?
That is an absolute reference.
I am telling it, don't,
when I the whole copy down thing,
I don't want this reference
to change when I copy it.
This one's relative,
cuz there's no dollar sign.
You put a dollar sign in front of the row
reference, a dollar sign in front of
the column reference, or
well flip those column then row.
[LAUGH]
And then you're saying don't ever
move from this position.
So when this gets copied down,
let me escape that.
So here's the first one I copied it
to here, F3 relative, G8 absolute.
Move down one more, F4 relative,
G8 Absolutes, so don't move from here.
Now, you can have absolute references on
just the row or just the column, or both.
So if you think about go ahead and
relatively move the row reference but
not the column.
We'll show examples, but
you can have it to where.
It's locking down one or the other.
And then anything else would change
relatively to where it goes.
So, that's what we're going to be doing
a lot of throughout all of these episodes,
but again starting at
the basics get that foundation
It's in our central topic so
this is one of those.
[LAUGH] And this is where it's funny,
I just created another series in
CreativePro with video editing.
And we named this file or
this episode, Read Me.
[LAUGH]
That was the title of it.
That's a good idea.
Right?
Yeah.
You wanna always
read the read me files.
So don't skip over this introduction.
Well if you're watching it, good.
Good on you.
[LAUGH]
Tell your friends don't skip over this
introduction.
[LAUGH]
Yeah,
it goes right
Right on back to what we said in
the beginning.
If I would have just read
the introduction files.
But that takes reading!
[LAUGH]
I know.
Who wants to read?
[LAUGH] But again,
that's why you have us.
So that is our introduction
to Excel functions.
Well, Vonne, it as very cool,
and like you said,
very important, crucial for us to
understand, have the same vocabulary.
So then when we're speaking about
something everyone is on the same page,
no one is going, what are they talking
about, I have no idea what that means.
Defining those terms and
definitions right out of the gate that is
going to help you and help us help you.
So, good on you Von for
helping us do that.
Good on you for watching this
episode as we said before but
it looks like we are at that
point in time for us to sign off.
We're Office Pro TV,
I've been your host Daniel Lowrie.
And I'm Vonne Smith.
We'll see you next time.
[MUSIC]

#### Learning Style

On Demand

#### Length of course

15h 54m

38 Episodes

## Here are the topics we'll cover

- Essentials
- Advanced

- Function Library

Learning Options

## Options for this course

Train your team

Stay ahead of the curve and future-proof your business with training programs designed for you.

Channel & Reseller

Transform your experience and integrate with our unique evolving library of Audit, Cybersecurity, and Information Technology courses.

Individual learners

Learn at your own pace and get your certification training.