Hi, this is Wayne again with a topic “Beginner’s Guide to Excel Functions and Formulas”.
This is the beginner’s guide to Excel functions and formulas in this article I’ll show you six of the most commonly used and basic Excel functions and how they work. So here I have a pretty big financial data spreadsheet. Let’S imagine that I’m an accountant at a pretty large business and this information shows how the business is doing here at the left. There’S different market segments, different countries, different products all laid out and let’s say I’m interested in figuring out some information about the profit.
That’S being generated from each of these segments of the customer base, so in this article I’m mainly concerned about the profit. So what I’d like to do is go down to the very bottom of this spreadsheet, but it’s such a big spreadsheet. How can I quickly do that? Well, if I hold ctrl on the keyboard and tap the down arrow, it takes me all the way down to the bottom of this group of data, but you’ll notice.
I can still see row number 1, which has my column headings. If you’d like to learn how to do that, please watch my video on freeze panes, but anyway. Here I would like to calculate some important information about the profit. That’S been generated.
First, I would like the total amount of profit throughout the whole company. To do this. I would just click where I want that total to be shown and then type the equals key on the keyboard. When I first learned Excel, I was kind of confused as to why I would start with an equal sign, but now I understand it’s basically like saying this cell equals and then I type in a formula. So I want this cell to be equal to and I’ll use my first function and the function I’ll use is sum, as I start typing, the word sum notice that I get a guide that pops up with some suggested other functions.
This is the one I want just some, but Excel is recommending also to look into some if and some ifs, some product, etc and notice that I do get a description of each of these additional functions. This is really the one I want some. It adds all the numbers in a range of cells so now that I’ve typed in I can just put in a left parenthesis and next I just need to establish what is the range of numbers that I would like to add up. I can do that. A couple of different ways, one way, is to click and drag to highlight what you want to add.
The problem, as you can see, though, is with 700 records with so much data. It’S gon na take me a while to get all the way up to the top. If I wanted to do it that way, though I could, let’s pretend like, I went all the way to the top. I could tap enter on the keyboard and it adds up everything that I selected.
Another option that is going to be better in this case would be instead of clicking and dragging to simply type in L to the L is for the column, and the number two is for the row that I would like to add so L to through and The way you type through in Excel is with a colon, so L to through L 701 notice that that entire column now is highlighted – and I can tap enter on the keyboard and now Excel – adds everything in the entire range. Now, because sum is such a popular and common function Excel actually has a built-in auto sum. So in many cases the best and easiest way to use sum is to click underneath the data that you want to add up and then go here to the Home, tab home ribbon and in the editing group. You can click on this symbol here to do an auto sum and then tap enter on the keyboard. Now, to help me remember that this data is different, it’s not really a regular record. It’S rather some data about those records.
I just selected it and I clicked bold to make it stand out. I might also want to go here to the left and type in total profit and again I might want to bold that and now it’s clear what that information means. The next function, I’d like to show is average. If I want the average profit for each of those segments of the customer base, all I have to do is go to a new cell type equals average. Again, I get some good suggestions and then left parenthesis and once again I’ll put in l2 through use L 7:01. Now you probably saw what just happened there. I was trying to type my formula here and I accidentally brushed my trackpad and suddenly I was typing in a different cell. That’S a good illustration of why sometimes when you’re, working with formulas and functions instead of just typing here in a particular cell, it’s often better to use the formula bar here at the top of the screen.
So I can click and type in the formula bar and I’ll fix the mistake that was made and everything that I type here in the formula bar is reflected here in the cell. Now I should finish this formula with another parentheses: it’s actually not necessary to do that, you can tap enter and it works without the right parentheses, but it is proper to put it in. So it may be a good idea to get in that habit. Okay, so now I have the average profit.
The next common and basic function that you need to know is Max and the max function. What it does is, it returns the highest number in a range, so this cell is equal to the max of this column, and I do want to go up here and use the formula bar right parenthesis tap enter, and that is the highest number in the range. Next, let’s look at min: this cell is equal to min, left parentheses and I’ll put in my range tap enter on the keyboard.
Wow we had negative profit, so just like you would expect min produces the smallest number in a range so in this range of everything in column he’ll. This is the smallest number. Our next function is count, and what count does is it looks in a range and counts up how many cells have numbers in them.
It skips every cell that is blank and every cell that has letters instead of numbers or words instead of numbers. So, let’s try it out this cell equals count left parenthesis and going up to the formula bar I’m gon na type l1, this time not l2 through L 701. So it’s gon na try to count l1 where the word profit is.
But let’s see if the total comes to 701, I tap enter on the keyboard and instead of being 701, it’s simply 700. Why? Because it does not count. The word profit, that’s not a number, so it skips it.
So this can be very useful when you’re only interested in the numbers, I’ll call that number of profit categories and then our final example is similar to count. But it’s count a the difference between count and count. A is count. A includes any cell that has a keyboard character in it and it also counts cells that have spaces in them.
So if a cell simply has a blank space by someone tapping the spacebar, that would count toward the total. So here count a going up to the formula bar left. Parenthesis l1 through L cap enter because I used count a this time. The count is different. It included the word profit in the total count. I’M not sure what to call this, how about not very useful, at least in this scenario, but there are scenarios in which you would want to know how many items make up a list and include both text-based and also numerical items.
So now I could bold. All of this and I’ve generated a list of important data, important information from the profit details in my spreadsheet, thanks for watching this beginner’s guide to Excel functions and formulas. If you like this tutorial and would like me to create an intermediate guide to excel functions and formulas, I will do that and I’ll show you some more functions that you’ll find to be helpful and useful in Excel thanks for watching. I hope you found this tutorial to be helpful.
If you did please like follow and subscribe and when you do subscribe, click the bell next to the subscribe button, so that you’ll be notified. Whenever I post another video, if you’d like to support my channel, you can do that through my patreon account and you’ll see a link to that in the description below .