When you use Excel it’s important to use formulas and functions correctly. 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.
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 article.