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