00:00:00
When you use Excel
00:00:01
it's important to use formulas
and functions correctly.
00:00:04
In this video, I'm going
to cover common functions
00:00:07
that you're going to need.
00:00:08
It's great if you're in Excel beginner,
00:00:10
or if you generally aren't comfortable
00:00:12
using functions in Excel.
00:00:14
I'll show you a trick that makes it easier
00:00:17
to use any type of function.
00:00:19
Let's get to it.
00:00:20
(playful upbeat music)
00:00:24
We're going to be using this
sample data set to practice on,
00:00:27
we have name, department and salary.
00:00:29
Now in Excel when you
want to type in a formula,
00:00:31
you start with the equal sign.
00:00:33
Then using your mouse or the arrow keys,
00:00:36
you can move over to the
cell you want to select.
00:00:39
So let's see, I just
want to add two numbers,
00:00:42
I'm going to select this
cell, type in a plus sign
00:00:45
then with my arrow keys
on the keyboard, go
00:00:47
and select another cell
00:00:49
and then when I'm done, I can press enter.
00:00:51
Now you can use the typical
mathematical operations
00:00:55
that you're used to.
00:00:56
So here I could go ahead
00:00:58
and add a minus and then
go and select this cell
00:01:01
and when I press enter
I get the final results.
00:01:04
And all of this is dynamic,
so if something changes here
00:01:08
my result is going to
update automatically.
00:01:10
I'm just going to press
Control + Z to undo this.
00:01:14
Now, in addition to plus and minus,
00:01:16
you can also divide and multiply.
00:01:18
So let's say I want to remove
this part of the formula
00:01:22
and then I just want to divide
this cell with the other cell.
00:01:26
Type in a slash and press enter.
00:01:28
To multiply two values together,
00:01:31
you have to use the asterisk sign.
00:01:33
Now you can also use brackets.
00:01:36
So let's put these inside brackets
00:01:39
and then let's divide the results
00:01:41
with let's say this
number and press enter.
00:01:44
Okay, so your typical
mathematical operations work
00:01:48
in Excel formulas as well.
00:01:50
But if you wanted to add
all of these numbers here,
00:01:53
you don't want to go in and
do a plus for each number.
00:01:57
This is where Excel
functions come into play.
00:02:01
So there are pre-programmed functions
00:02:03
that do the plus for you.
00:02:05
So for example, if I want to
add all the salaries together,
00:02:09
I can use the sum function.
00:02:11
So just start typing sum
00:02:13
and you can see the list of
functions popping up here.
00:02:16
You can select it with your mouse
00:02:18
or you can use the tab key
to select the first one.
00:02:22
And then here you can add different cells
00:02:25
but in this case, we don't
want to do different cells,
00:02:28
we want the entire range.
00:02:30
So with your mouse, you can select this
00:02:32
you can also go with the arrow keys
00:02:34
and hold on to shift key
and select this range.
00:02:37
And then you close the bracket
and when you press enter,
00:02:40
you have to sum of this entire range.
00:02:44
So functions in Excel
are programmed machines
00:02:47
that do a certain task.
00:02:49
The sum function sums up different values,
00:02:52
then we have the count function
00:02:54
which is going to count different values.
00:02:56
Now we're going to see this in a second
00:02:58
but I want to show you one
thing before we get there.
00:03:02
How do you know what a function is called?
00:03:04
If I'm new to Excel,
00:03:05
I'm not going to know about this function.
00:03:08
I might be typing in add
and there is no add function
00:03:12
you can get help from the formulas tab.
00:03:15
Now talking about being new to Excel,
00:03:17
if you're also new to this channel welcome
00:03:20
and consider subscribing
00:03:21
so you can find these tutorials easier.
00:03:24
So under formulas tab,
you have insert function,
00:03:28
this is something you can use
to find a specific function.
00:03:32
So let's say I want to add values.
00:03:36
I'm going to press enter
and I get suggestions here
00:03:39
and when I click on them, I
can read about this function.
00:03:42
I see the SUM function here,
00:03:43
it adds all the numbers
in a range of cells.
00:03:46
Now we're going to get back to
insert function in a second,
00:03:49
I'll just press cancel on this now.
00:03:52
Let me show you another
way of summing values
00:03:55
and that's using AutoSum.
00:03:57
When you click on this drop down,
00:03:58
you have the option to select
the type of function you want.
00:04:01
So in this case, we want to sum
00:04:03
so I'm just going to select this
00:04:05
and it does the whole work for me.
00:04:06
All I have to do is check
00:04:08
whether the range it
selected is what I want,
00:04:11
in this case it is, so all
I need to do is press enter.
00:04:15
Now, there is also a
shortcut key for this.
00:04:17
You might have caught it
when I clicked on AutoSum
00:04:21
it's Alt and the equal sign.
00:04:23
So when you're here, just
type in Alt and equal,
00:04:26
press enter and you have your sum.
00:04:29
Now, this also works horizontally.
00:04:31
So if I have a number here, I can go
00:04:34
and do Alt + Equals and it
picks up the correct range,
00:04:38
and all I have to do is press enter.
00:04:40
Because summing values or
getting to average of values is
00:04:44
such a common task.
00:04:46
You don't just find it
under the formulas tab here,
00:04:49
you have AutoSum
available in the home tab.
00:04:52
So directly from here on the
editing side, you have AutoSum
00:04:57
and you can select the
function that you need.
00:05:00
So now let's take a look
00:05:01
at getting the average of our values.
00:05:04
Now, on the side here, I'm
just going to type in sum
00:05:07
so we know what each function represents.
00:05:10
Let's get the average here
and now again I can start
00:05:14
by either typing in the function directly
00:05:17
and then selecting it with
tab, highlighting my range,
00:05:20
closing the bracket and pressing enter.
00:05:23
Or I just go to the AutoSum options here
00:05:26
and select AVERAGE, but
now take a look at this,
00:05:29
it picked up this value as
well so I need to correct this.
00:05:33
So instead of C11, I want
C10, I'm going to press enter.
00:05:38
Next let's count numbers, so
I'm going to type in count,
00:05:42
on the side here I get some information
00:05:44
about what this function does.
00:05:46
So it counts the number
of cells in a range
00:05:48
that contain numbers.
00:05:50
That's what I want so I'm
going to open the bracket
00:05:52
or press tab and it
opens the bracket for you
00:05:56
then select this range,
close bracket, press enter
00:05:59
and that's the count of
cells that have numbers.
00:06:03
So if one salary information is missing
00:06:07
my count is going to be reduced.
00:06:09
So I'm going to press
Control + Z to go back,
00:06:11
now just to know what we're
doing this was count salary.
00:06:16
Can I use this to count names?
00:06:19
Well, let's try it.
00:06:21
I'm going to use the COUNT function,
00:06:22
this time I'm going to press tab.
00:06:25
Let's select this, close bracket
press enter and I get zero.
00:06:29
So the COUNT function only counts numbers,
00:06:32
it doesn't count texts.
00:06:33
If you want to count texts
00:06:35
you need to use a different function
00:06:38
and that's the COUNTa function.
00:06:41
This function counts the
number of cells in a range
00:06:44
that are not empty.
00:06:45
So it doesn't care whether
it's texts or it's a number,
00:06:49
it counts both of them.
00:06:51
Now I'm going to press
enter and I get eight.
00:06:54
So if one of these happens to be a number
00:06:57
I'm going to put a zero,
it's going to count it,
00:06:59
it only doesn't count it if it's empty,
00:07:03
just press Control + Z
a few times to go back.
00:07:06
Another two super useful functions are
00:07:08
the MIN and the MAX functions.
00:07:12
So let's say I want to get the
minimum salary in the range.
00:07:16
I need the MIN function, select the range,
00:07:19
close bracket, press enter
00:07:21
and it's going to give me the
smallest number in that range.
00:07:24
In a similar way, we can
get the maximum salary
00:07:28
from this range, close
bracket, press enter
00:07:32
and that's our number.
00:07:33
Now, what if you wanted to
do something more complex
00:07:36
like getting the average salary of people
00:07:40
who are in the sales department,
00:07:42
how would you go about this?
00:07:44
Well, we can start off
by typing in average
00:07:48
to see the different options we have.
00:07:50
And then we can check this description
00:07:52
to see if it does what we want it to do.
00:07:55
So here we have AVERAGEIF which looks good
00:07:59
because it takes into
account a given condition.
00:08:02
And we have something called AVERAGEIFS
00:08:05
which is a given set of conditions.
00:08:08
To be keen include more than
one condition in this formula.
00:08:12
Now let's say I'm interested in this
00:08:14
because I might add in other columns here
00:08:17
and I might want to have
different conditions.
00:08:20
So I'm going to double click
this, or you can press tab
00:08:23
and then let's go to the formula
bar and see what we need.
00:08:26
It says we need average
range criteria range one,
00:08:29
criteria one.
00:08:31
Now all of this doesn't really mean
00:08:32
much to me if I'm new to Excel.
00:08:35
So here's what you can do.
00:08:36
Click on this button, that's
the insert function button
00:08:40
and it takes you to this dialog box
00:08:43
which gives you a bit more information.
00:08:45
So for average range here
this is the actual cells
00:08:49
to be used to find the average.
00:08:52
So this means these are my number cells
00:08:55
that's the range I need to select.
00:08:57
I can get a preview of
what I selected right here
00:09:00
then what this criteria range one mean,
00:09:02
this is the range of
cells you want evaluated
00:09:05
for the particular condition.
00:09:08
That's my range.
00:09:11
Next criteria one.
00:09:13
This is the condition or criteria
00:09:15
in the form of a number
expression or texts
00:09:18
that defines which cells will
be used to find the average.
00:09:22
So that's sales.
00:09:24
Now I can do a cell reference here
00:09:26
if I had sales sitting
in a separate cell here
00:09:29
or I can just directly type it in.
00:09:32
And notice something
the moment I click away,
00:09:35
it's going to put my
texts in quotation marks,
00:09:37
because if you're using
texts inside a formula,
00:09:41
you need to put quotation marks.
00:09:43
Now take a look at this,
00:09:44
I already see the answer here.
00:09:46
So I have a good idea that my
function is working properly
00:09:50
and then all I have to do is press on okay
00:09:53
and the function is inserted here.
00:09:55
Okay, so this is a great feature to use
00:09:58
when you're new to Excel
00:09:59
and don't really understand
what Excel wants from you
00:10:02
for each of these different requirements.
00:10:05
So that ends our introduction
00:10:07
to Excel formulas and functions.
00:10:09
I have many videos
00:10:10
on the channel covering
different functions in Excel.
00:10:13
So make sure you check them out
00:10:15
if you ever need to write
more complex functions
00:10:19
to get your analysis done.
00:10:21
If you like this video don't
forget to hit that thumbs up
00:10:24
and do consider subscribing
if you aren't a subscriber,
00:10:28
it would be great to have you
as a part of our community.
00:10:32
Thank you for watching and
I'll see you in the next video.
00:10:35
(upbeat music)
![[BREAKING NEWS] Persiapan Peringatan HUT Bhayangkara Ke-79 | tvOne](https://ruhnrawpgxrzdrgaohnf.supabase.co/storage/v1/object/public/images/video/Wigt50eAngM/thumbnail.jpg)
