Basics of Formulas and Functions in Microsoft Excel 2016
During this Microsoft Excel 2016 training tutorial video, discover the essentials for using formulas and functions. Your instructor will demonstrate the general principles of doing calculations in Excel 2016.
Video Transcript
This is the Business Expenses worksheet so far and how you found out quite a bit about formatting a worksheet and making sure that everything is starting to look pretty good. What we’re now going to do is to turn to what is probably Excel’s greatest strength and probably the reason that most people use Excel which is to do some kind of calculation or analysis. Whether your strength is in calculation or whether you’re doing it somewhat reluctantly I think you’ll find that many of the things that you need to do in Excel are pretty straightforward and we’re going to start with one or two really straightforward examples of calculations in Excel. And in doing that I’m going to try to demonstrate the general principles of doing calculations in Excel 2016.
So the first thing I’m going to do is to apply a little bit of Zoom. Now let’s do some work on the total amount of Toby’s Expenses over the period that is in that table of values.
What I’m going to do is to put the total in this cell here, F9. And first of all I’m going to do this whole thing pretty badly or at least you’ll see in a few minutes time that it was done pretty badly. And that is I’m just going to put a simple formula in here to add up the figures in column F. Generally speaking if in a cell rather than putting a number like an amount of currency or some text like the word Travel or a date you want to put a formula that something that involves a calculation you put an equal sign and that’s how you always begin cell contents where there’s a calculation to do, so equals. Now you actually enter the formula. Now in this case what I want to do is this. I want the contents of F4, so that’s F4, and I just type F4 and then I say plus and then I’ll want F5 and then I want and so on. So let me just put the others in and that’s a Formula.
Now at the moment in the cell you can see the formula but if I enter that value now either by hitting the Enter key or using the Enter tick mark by the Formula Bar you’ll see what happens. I actually get the total amount of the expense claim over that period.
Want More? Get Started With a Free Excel 2016 Course! Click Here
Now before we go any farther there’s one very important to recognize here and that is that cell F9 is now unlike the other cells on this sheet because it has both a value, it’s value is $305.20 and it has a Formula. And its formula is the formula in the bar. So when you click in the Formula Bar, you see in the Formula Bar the formula which you also actually see in the cell in that situation. But if you select the cell it’s the value that you see.
So cell F9 has got a formula and a value. And closely associated with that is the fact that without changing the Formula the value may change. So let’s suppose that I spot an error in my expenses. Let’s suppose that the error I spot is that that Annual Subscription $64.50 should actually be $84.50. So there was a mistake of $20 right there. Let me change it to $84.5, tick. Now automatically the value in F9 is increased by $20. And when you make a change to a cell in Excel any consequential changes are automatically carried out. So in this case when I change the value in F7, F7 is part of the Formula for F9 so the value of F9 is recalculated as well. So we haven’t actually changed the Formula in F9 but the value changed because of a change in the value in the cell F7.
Now the Formula you can see here in F9 is a pretty straightforward Formula. It’s a Formula to add the contents of five cells together. And of course in Excel formulae can become extremely complex. And if you look at say the length of the Formula Bar there right across my screen you could have that Formula Bar full and more than full in terms of the complexity of the formulae that are possible in Excel. But apart from writing out a sum like this, =F4+F5+F6, etcetera there are some other ways of doing things like totaling a column of figures that are actually much more efficient ways of doing things. And I’m going to demonstrate one of those to you next. But just before I do I want to point out one other thing and this relates to something that I said earlier as well.
If I were to put an additional expense in here. So let’s suppose that I realize that there was another expense for the 10th of March, March the 10th but I’d forgotten to include. Let me select row 7 and Insert an additional row. Let’s go back and see what the formula is in what is now F10. Look at the formula. It’s F4+F5+F6+F8+F9. What Excel can do is to compensate for things like inserting and deleting rows and columns. So not only do you not need to worry about that because Excel takes care of it but obviously you can use that to your advantage.
Now let me just Undo that and I’m actually going to Delete F9. So don’t forget when I’ve got that cell selected I can either just press the Delete key or I could say Clear All because clearing all will clear formulas and it will clear contents and it will clear formats. It’ll clear absolutely everything. So that’s all cleared. And now I’m going to take a different approach to adding up that column of figures.
And the approach I’m going to take is this. I’m going to select F4, keep the mouse key down, drag down to the bottom, and just include F9, then I’m going to release the mouse key. Now I mentioned this little icon down here before. Ignore that for the moment, the Quick Analysis icon. Don’t worry about that for the moment. What we’re going to do is to go up to the Editing Group on the Ribbon and next to the AutoSum button we’re going to choose Sum. And unsurprisingly the total we get is back to $325.20, the same as before. And we have a Formula in F9 but it’s a very different formula to the one we had before because it now uses the Excel Function of Sum.
And the general format for a Function is that you’ll have the name of the function, in this case Sum, then you’ll normally have a set of brackets and then in this case there is what’s called a Range of Cells. So it’s a range from F4 to F8 and the ranges indicated by a starting cell, then a colon, then the ending cell in the range. Now in this case those two cells are in the same column but in fact they could be in different rows and columns. So a range is actually a rectangle of cells. And in this case the top left of the rectangle is F4 and the bottom right of the rectangle is F8. So we have a little sort of sub-column of cells. So in that formula we’re using the function Sum.
Now one of the big advantages of using the Sum function there is you haven’t got to type in the list of cell names. But there are some other advantages to it as well and let me do what I did just now and insert a row corresponding to a missing expense. So let me select row 7 again, do an Insert and let’s now look at that formula. And the formula is still just =Sum but in this case not only does it cover the correct range from F4 to now F9 but if I put a value in here, let’s say I put a value in there of say $17.30, it would be included in the calculation because by using that function I didn’t have to specifically mention the new F7. All I needed to say was the range and F7 is automatically included because it is in that range. So that’s a big advantage of using a function here.
Now let me just Undo that last couple of operations. And the other thing that is particularly useful about this approach, let’s suppose that instead of adding up those expense amounts I wanted for some reason to find which the largest was. If I click within the Formula Bar and delete the word Sum and instead change it to the word Max what I’ll see there is the Max figure in that column. So the maximum amount is $127.50. Or what about the average figure? Now as I start typing that function name a whole list of the functions that begin with the letter A appears. And at this point if I wanted to just scroll down, find the one I want, Average, that’s the one I want. Let’s take that, tick that. The average expense amount is $65.04. In Excel 2016 there is a very, very large number of Functions available.
Want More? Get Started With a Free Excel 2016 Course! Click Here
So in this section I’ve introduced you to the basics of Formulas and Functions. In the next section we’ll start looking at some more complex calculations and how to set those up efficiently and effectively in Excel 2016. So I’ll see you then.
Simon Calder
Chris “Simon” Calder was working as a Project Manager in IT for one of Los Angeles’ most prestigious cultural institutions, LACMA. He taught himself to use Microsoft Project from a giant textbook and hated every moment of it. Online learning was in its infancy then, but he spotted an opportunity and made an online MS Project course - the rest, as they say, is history!