2017-02-28 - Excel - Formulas and Functions

okay now I'll do my official welcome hello everybody that is attending tonight tonight Excel formulas and functions kind of an excel refresher possibly for some of you possibly hopefully you'll also learn a few new things that you never knew before I have found over many years of teaching this type of stuff that Excel kind of tends to be that product that we all got kind of dumped into and we muddled our way through for a while and sort of figured out how to do things and we're adequate with it but there may be a variety of things that you just never really learned to do the right way years ago when I first got into training I would have some people like you know back when I would teach a full Excel introduction class for a full day I'd have people that would claim they knew look so really well they'd want to skip they want to skip right to the intermediate or advanced class and after chatting with them a little bit it was usually pretty obvious that they were thinking more highly of their skills and they really actually had when I came to the product and I said come to the intro class and I guarantee you would let you learn something and most of the times that was that was the case so let's hope that's the case tonight with you all attending tonight's session I've got a pretty ambitious and agenda of things that I would like to cover I've got a lot I've made almost 50 slides here this kind of largest presentation I've made for one of these one of these training sessions but I wanted you all to have these notes and stuff for later so again you can download this presentation you've got the slides there if you access the onedrive you can download all these slides and and take away a lot of the tidbits that we're talking about so I've got as you can see here are rather ambitious this these are just the main topics we've got a rather ambitious agenda for tonight of things that I'd like to get through and hopefully kind of reeducate you or just educate you in general when it does come to excel so the first thing I want to clarify you have called this this class Excel formulas and functions it's going to be a lot more than that there's a a lot of things that we're going to go into just about the basics of using Excel but there's something I need to clear up and this has been a pet peeve of mine for as long as I've been in the training business and that is people using the word formula and function interchangeably or they just kind of blurt it out they don't really know what they're talking about and I'm a stickler for accuracy when it comes to these things and especially in software I like people and want people to know the correct terms for you things and to understand what that correct term is and here the key point I want to make is that the terms formula and function are not interchangeable terms they have very they have very specific definitions in Excel basically a function is a mathematical operation and they're always used in a formula okay conversely you may have a formula which does some sort of math and it may not have any sort of function in it okay and again when we get to the func of the formula rating later this is going to be more obvious what we're kind of getting at here so the terms are not interchangeable so you know this little I just did this quick little graphic to kind of illustrate what we're talking about here is that functions are always part of some sort of formula but a formula itself in Excel does not necessarily have to have a function in it to do what it needs to do okay we'll go into this a lot more detail later but this is a terminology thing that I think is important to keep straight as we go through the material here all right now before we really kind of get too much into working with formulas and functions and like getting Excel started here while we're going through the slides there's a few things about just kind of some basics of operating in Excel we want to talk about and that is understanding the difference between text and values in cells what does the formula bar do for us when we're in Excel how do we edit text and values that are already in cells how we just do basic things like selecting ourselves as we're moving around and then really importantly and this is something that again I find causes confusion with a lot of people oh excuse me grab a little water there deleting and clearing cells as we're working in Excel so the first thing let me jump over to my spreadsheet here and I just got a big blank spreadsheet here by the way as a note I will be demonstrating things in office 2016 that is the most current version of Office but everything I show you tonight basically goes all the way on down to like many many previous versions of Excel this is the basics this stuff this stuff has not changed in a long time okay so if I go into Excel here and I just start to type some basic text you know like just the names of some animals okay you'll notice that the text that's going in there in Excel is left aligned in the cells automatically which is kind of what we would expect and the other thing to note here is is that is that very long text looks like this okay that really I type of just a long sentence there and it kind of looks like it's taking up a bunch of cells but the reality is is that I was on cell a5 when I was typing that so technically speaking all of that text that I typed there is in cell a5 there is technically nothing in b5 c 5d v e5 f5 and g5 even though it kind of looks like there's something there they're technically empty and this will get to what we're talking about with the formula bar in a few minutes okay now numbers if I type them in a cell and they get some numbers here okay when I type numbers in a cell they're always going to be right aligned okay no I haven't I haven't applied any alignment to the cells excels doing this automatically and you know what this just goes back to what we learned with addition and subtraction and whatnot back in what first grade second grade your teacher said all your ones places line up while your tenth place is line up by your hundreds and so on and so forth line up now of course I could do something with a decimal here that's going to kind of throw things off a little bit yeah but the reality is numbers are going to be right aligned now the other interesting thing with numbers though is that if I type a really long number it will not overlap into other cells Excel is going to do everything it can to try to make that number fit into that cell which includes if you look here in cell a 15 it includes converting the number into something called scientific notation okay that one point two three six five eight e to the 24 that's that's basically something called scientific notation okay so a quick going over this slide when we're putting in text and values into a spreadsheet text is always going to default to left alignment now we can change that if you want text to be centered or right you can change that but the default what Excel is always going to do when you type text is it's always going to align it to the left to start secondly text is allowed to appear to appear to occupy other cells that goes back to here yes this sentence here the other thing to note here blah blah blah looks like it's on several cells the reality is this is not okay numbers or values always default to right alignment and in the case of values Excel will always try to confine that to the cell so there's no overflow you may have also sometimes seen something that looks like this where you see all pound signs okay that means the number is just too long to display and we make this even smaller there we go the number is just too big to display within the cell so you see some people like to call them railroad tracks you see all pound signs on number signs so Excel tells you there's a number there but it can't display it because there's not enough room because again numbers aren't allowed to overlap into other cell okay it's just one of the basic rules of Excel okay so let's talk about the formula bar that's this thing up here right next to where it says FX okay this is what's called the formula bar the formula bar is where you could kind of write out formulas but really the thing about the formula bar is that it's always going to tell you what is actually in a cell okay when I click on cell b5 right here going back to this example here or so there was all this extra text yeah if I look at the spreadsheet it looks like there stuff in cell b5 it looks like it's occupied but if I actually click on cell b5 and then look at the formula bar you'll notice my formula bar is completely empty which means cell b5 is empty okay now on the flip side and we'll see this later sometimes you'll have cells that down here look like they're empty but in reality there's a formula hiding in that cell okay so the big takeaway here is the formula bar is always going to tell you what is really in a cell and just because a cell looks empty doesn't mean it's actually empty and just to just mention that a formula could be in that cell on the converse side or the flip side just because a cell looks like it as content doesn't mean it actually has content texts can overflow and look like it's in a cell okay again coming back here it looks like there's something in these cells but the reality is you know in b5 through g5 it looks like there's something here the reality is there's nothing there they're empty because when I look at the formula bar up top they're complete completely empty okay all right now once I've got stuff in a cell how do I edit it how do I change it you might be tempted to maybe press delete or start typing something else on a cell if I come up to here where it says dog and type something else like person the problem is that erases what was there well I want to erase it I just want I just want to edit what was there there's actually three ways that you can get into editing a cell you can just double click the cell to activate the cursor you could click on the cell and then click in the formula bar and make changes or my favorite keyboard shortcut for this is f2 on the keyboard in any of these cases for instance if I want to change this dog to doggy I could double click okay that was one way to do it I could simply come up here to the formula bar look that cell is selected but I could come up to the formula bar and change it to the word dog okay or if you're a keyboard shortcut person like myself you could click on the cell press f2 and that will also activate the cell dogs and dogs and dogs okay so that's three very that's how you those are three ways that you can edit a cell when it already has content the cells empty you can just go ahead and start typing that's not going to hurt anything but if there's already content in the cell you can either double click the cell you can click in the formula bar or you can press f2 on the keyboard any of those methods are going to work just fine now let's talk about selecting cells again these are a lot of basics that I like to go over because we're going to you're going to use this stuff later so there's a method to my madness here I'm not just going over this stuff to waste your time I'm going over it because later on when we get into doing some certain things with formulas and with functions you're going to need to know how this all works and hopefully you'll learn some shortcuts that you never knew before when we want to work with more than one cell okay there are a variety of ways that we can do that you can use the mouse to select cells can use your keyboard to select cells and then there are a few tricks that are a combination of both using the mouse and using the keyboard to select cells either of those work I've created these charts I'm not going to go through every shortcut but you can look at these later I'm going to go over a few of these right now let's talk about selecting cells with a mouse well with the mouse it's pretty simple I'm just going to click and drag over everything that I want to select and that's as simple as that you always want to you always you don't have to but you generally want to start in the top left and drag to the bottom right but you know the reality is I can drag in any direction and it doesn't really matter okay now if I want to select an entire column or an entire row simply click on that header so if I want all of columns see I would come up here to where it has the letter C and just click and that selects the entire column if I want all of row 5 I'm going to put my mouse right on the number 5 and click and that's going to highlight the entire row or select I should say it's going to select the entire row ok if you need multiple columns or multiple rows you're going to drag over the letters so if I want from column B all the way to column G I'm going to put my mouse right on top of the B I'm going to keep holding it down and I'm just going to drag all the way over to H or whatever letter you need to drag - now rows be careful with rows because things are a little bit tighter here so make sure you're right on top of the number and if I want to select rows 3 through 10 I'm just going to drag over those right but be careful because grabbing in between the rows does something different not going to get into all that stuff with formatting tonight as another class so just be careful when you're selecting so those are some tricks for selecting with the mouse if I want a group of cells a single or multiple columns a single or multiple rows now I happen to be a keyboard fanatic especially when it comes to Excel because quite honestly I worked with Lotus 1-2-3 many many many eons ago and the only way to navigate back in those days was with a keyboard mice just really didn't exist and because I really learned way back in the day to use a computer without a mouse I like I use the keyboard for a lot of stuff but don't get me wrong a mouse is really darn convenient there's a lot of things you just can't do without a mouse but the nice thing is a lot of those keyboard shortcuts are still in the products and some of them are pretty darn convenient and especially here in Excel when it comes to selecting cells I love some of these tricks first off in cell selection you know if you just want to select two cells yeah you can do that with the mouse and drag but quite honestly to me that's too much work if I just need to select these three cells that's a catfish and bird to me it's faster to hold down the shift key and then just press the arrow key twice and now those three cells are selected okay and this works in any direction if I want to select you know these ten cells there you go and as long as I'm holding down shift okay and hitting the arrow keys I can make this block bigger or smaller I can go up and down and I can select any cells that I want so I like this trick if I'm just doing a couple of cells now if I was doing more really if I was doing 10 cells I'm going to pick up the mouse and do it because it's quicker but if I just need to select look I just need these two cells it's to me it's much faster to keep my hands on the keyboard and just do shift down arrow now there are some other interesting tricks that we can do with the shift and other keys you'll notice that all of these selection tricks except the last one involve the shift key so the little mnemonic here is that shift is the selection helper okay when you want to select stuff in Excel and you're using the keyboard shift plus something generally does something and you can see on this chart there's a lot of these things that will happen for instance all content in a direction okay if I go up here to a1 and I want to select all the way down through a 5 if I go shift control down arrow it's going to select those 5 cells all right now the interesting trick here is that if I do that again it from the same spot is it's going to go down to the next group of content so I'm going to hold down ctrl shift down arrow now I'm still holding ctrl and shift I'm going to hit it again and notice it jumps to that 12 because it's going to go to the next start of content if I hit it again I'm still holding ctrl and shift down notice it goes all the way down to row 15 now there's nothing in the way from here on so if I hit ctrl down arrow it's going to go and it has all the way down to row 1 million whatever it is down there and I've pretty much selected the entire column now that's kind of a little excessive but that's the way it works ok um let's see here there are a bunch of other these similar tricks I'm not going to go through each and every one of these however I do want to talk about the last one and that's all cells on the worksheet if you need to select everything on a particular sheet gate like the entire sheet okay you can go ctrl a and that selects the entire sheet yes it does start with ctrl it does not start with shift however what I'll tell you is is that control a is a standard selection trick for almost all Microsoft products and that fact not even Microsoft products it's kind of one of those standards across everything so ctrl-a is almost always select all inward in PowerPoint in here in Excel if you're in your web browser and just about any other product they all pretty much support the idea that control a is to select all things okay now there are set of mixed tricks here and that is it involves using the mouse and the keyboard and generally we use these tricks when we're trying to select multiple things for instance let's say I do want to select column B through n now I talked earlier that you could just drag across there but there's another way I'm going to highlight column B just by clicking it I'm going to come over here to column n I haven't clicked anything and I'm not dragging I'm going to hold down the shift key and then click and notice that selects all those columns I can do this with rows 2 by 1 rows 10 through 20 yes I could click and drag over those 10 rows that's possible or I could click row 10 move my mouse down to 20 hold down the shift key and click and voila I've selected those 10 rows this also works for groups of cells basically you'll select the top left and bend the bottom right so if I start here at a1 okay that's we're going to set the first cell I'm going to click and if I come down here to about n 30 and shift click notice that it selects that entire rectangle so I could do D 1 through D 35 is by using the shift click trick I could do a 1 through m 40 M 15 okay so again that's pretty much the same as dragging but sometimes it's a little more accurate just depends the final trick here is if you want to select things that aren't together and this is something you almost always have to do with the keyboard and the mouse okay because the mouse is always going to drag let's say I wanted columns B E and G for some reason there's no way to do that only with the mouse because as soon as I click on the other column it unselect the first one I can't drag from B to E because it's going to do all of them so if I want B and E and H what I'm going to do is hold down the ctrl key and click those other columns and now I can select as many columns as I want as long as I'm holding down the ctrl key so if I want a d e H and L I can simply hold down the shift key and select those columns if I want rows 2 6 12 19 and 27 again all I'm doing is holding down the ctrl key as I click those headings the other thing I can do is just individual cells if I want this cell this cell this cell this cell I can just sit here and hold ctrl all day this looks like super breakout from back in the day right and I can just sit here and just keep highlighting cells as long as I continue to hold down control I'm selecting those cells and then I can do something to all of those cells at once okay all right last thing in this unit before we finally move on to this stuff with formulas is erasing cells let me do this I'm going to type my name right here but I'm also going to add some formatting to it let's make it red let's make it 24 points and let's make it bold okay now we have a habit in most products that when we want to erase something we press the Delete key or the backspace key and you know what that works okay you know backspace will erase this cell okay delete will also do the same thing in that cell but there's an interesting quirk here in Excel and that is that although I did delete the content that fell technically is not empty right now because if I type something else oh for instance maybe Jason's name notice it came out with the same formatting as the or and I'm like wait no no I just wanted to type I just wanted to look like it did before but no matter what I type oh you know hello world everything I keep typing is going to look like that even if I press delete if I come back here and press delete like four or five times it's still it still has the same formatting what's going on here well when you want to erase or delete within a cell you have to take into consideration how Excel classifies or treats the information that's stored in a cell and that is that a cell stores separately two main things one of those is the content of the cell and then the other one is the formatting of that cell okay now content is anything the content of the cell is basically what you wrote in it okay so that's any text any values and numbers any formulas anything that you basically write or type into a cell is something we call content that's the cells content so again in layman's terms it's what you wrote okay now the other thing that a cell stores is formatting information what's it going to look like the font the font color the font size there's there's a million things I just picked I just picked the popular ones here but you know there's a variety of things about how does the cell look okay so we've got the content and we got the formatting that are stored in cells now here's that now here's the big kicker oops let me get back over here to PowerPoint there we go here here is where the heavens open for you and this is the thing when you press Delete on the keyboard in Excel the only thing Excel is going to erase is the content not the formatting okay so coming back over here to this cell and I see raylene has joined so she's going to be my victim now even though this cell even though cell g8 looks completely empty and I've pressed delete three or four times here the thing is is that every time I type something it still has the same format what's going on here okay well the thing is is the Delete key is only erasing the content all that formatting information the font the font color the font size all that stuff that I did is still a part of that cell how do I get rid of it how do I really reset a cell back to normal and that's where we need the clear command and you'll find the clear command up here on the Home tab over in the editing group it looks just like an eraser and when I click that I'm not going to go into all the possibilities that you have here but I want you to notice the first three options clear all clear formats and clear contents it's possible to clear the the content without clearing the formatting it's possible to clear the formatting without clearing the content or if I really need to empty out a cell I'm going to select clear all so let's look at this again we type my name there now let's say okay I still want my name there I just don't want all the formatting how can I fix that well I can go up to clear and say you know what clear the format's and look the content stayed the same the formatting disappeared okay let me go back let's do the opposite let's just say clear contents well that's the same as pressing the Delete key because now no matter what I type it you know there's the situation that we had before if I select the first option clear all that erases all the formatting that erases all the content and now we're back to normal okay so that's an important command to understand because people and this is again there may be hidden minefields in your Excel spreadsheet maybe from a previous user where there was formatting on a cell and you type something and all of a sudden it comes out of looking a certain way and like what the heck happened here you're running to this a lot when it comes to numbers and I could spend a good fifteen minutes on this but I can't afford it right now but let me just tell you in a nutshell if you type some sort of number into a cell and it does something funky it's usually a format issue and that the wrong numeric format has been applied to the cell and you just need to apply the correct format or erase all the formatting in general and start over okay alright so that gets us through that first stuff let's move on to another fun feature here and then I think we'll get into our formulas and that's autofill and again I want to spend some time on this one because we're going to use it with formulas here in a few minutes and for this one I'm going to open up another spreadsheet there we go autofill is an amazing feature of Excel and if you happen to have Excel 2016 there's they've amped up the autofill into something called flash fill but I'm not going to spend any time on that because I'm not sure what feature what version everybody has but I want to talk about autofill and what it does oops slides where'd you go there we go okay autofill is a feature that allows us to quickly fill up cells with text values dates formulas and other special sequences depending on what you're trying to fill autofill will work differently basically autofill is we're going to drag the bottom corner of the cell now again depending on what's in the cell and what you've selected this is my selection was important depending on within the cell and what you've selected you'll get different results so let's talk about text first I've got a single word here in this cell it's just the word kitten and now what I'm going to do is I'm going to put my mouse right on the bottom right corner and you'll know you've done it right when you're when your cursor changes to a thin plus sign I'm going to take that word kitten put my mouse right on the bottom right hand corner and just drag down and you'll notice it's going to repeat the same word all the way down so rather than having to write but word kitten 500 times or even copying and pasting all I had to do was drag it down alright that's if I do a sync a single word oops redo that leave it there now what about a sequence of words I've got the words dog catfish and bird what I'm going to do is I'm going to highlight or select all four of those cells okay again you could use your mouse you could use your keyboard based on the tricks that I showed you earlier now again I'm going to put my mouse right in the bottom right corner and I'm going to drag down Excel basically is just going to repeat that sequence over and over until I until I stop so I've got dog catfish bird dog catfish bird over and over and over it's just going to keep doing that until I decide to stop okay so with text if I do if I autofill with a single cell it's going to repeat the text if I select multiple cells Excel is going to repeat the sequence of text in the selected cells okay now let's take numbers numbers sort of behave like text and sort of don't notice here in this cell I've got the number 1 if I drag that down it's just going to repeat the number 1 all the way down but if I have more than one number Excel is going to try to is going to analyze the sequence and try to guess or predict what I'm doing which is different than what we had with text with text you know it's not going to go to four two four two four two four two four here it's going to go hey it looks like you're counting by twos and if I grab this and drag it down you'll notice I now have two four six eight ten twelve so on and so forth okay look at the next column here I've got the number five and then the number ten okay looks like I'm counting by fives so if I highlight those two cells and drag it down well gosh golly yes I was counting by fives if I wanted to count by ones I would go one two and drag on down this works in any order look I've got 100 and then I've got 99 so I'll highlight both of those cells grab the autofill drag down works perfectly fine now if you have something weird if you have a weird sequence like let me do this too for I'm just going to do doubles because it's pretty obvious to me looking at this that this is a doubling sequence okay Excel doesn't always figure it out so here's a sequence where to me as a human it's very obvious that each one of these values is double the previous two four eight sixteen thirty-two blah blah blah okay but if I highlight that sequence and then drag it down it doesn't work I'm not even sure what Excel is trying to do here I wouldn't be able to make heads or tails of it okay so if it it doesn't always get it right but 99% of the time it does as long as you're not doing something weird you know if I put in the Fibonacci sequence or a series of primes that's not going to work excels I'm going to figure that out okay all right so a single cell is going to repeat the number multiple cells Excel is going to attempt to analyze the sequence and predict what's next now let's talk about dates because dates are a little weird date technically are a type of numeric numeric value but they're treated differently when it comes to autofill let's take a look at this in this cell here I've got a single date it's 1 1 2017 now based on what we saw previously if I was to take one cell and drag it down it would repeat the value but Microsoft's pretty smart here and they go hmm you know what generally if you have a date and you want to fill you you want a sequence okay so if I take this single date and drag it down it literally just goes through the entire sequence automatically it just automatically assumes that I want a sequence of dates now I'll show you how you can override that in a few minutes okay so as long as I drag down and I've got a single date it's just going to start with that date and go now if I do want a sequence of some sort just like before I needed I need to give it the first two dates and then it's going to try to figure it out once again sometimes Excel gets it right and sometimes doesn't so for instance here I've done the first day of two months so if I highlight that and drag it down yep it got it pretty well it even works for end of the month so let me go Oh 1 3 1 2017 Oh 2 2 8 2017 which happens to be today right fortunately Excel is pretty smart here and it knows that I'm trying to do the last day so you notice it gets the last day of every month there correctly other sequences it's not as good at doing let's see here let me try I don't know if this one works or not let me do a one let's do let's say I want the first and the 15th of every month I remember right I don't think that works ok let's go to oh one I need to give it a few days sometimes you got to give it a few so it can hopefully figure out the pattern ok I think let's see here what's going to happen now yeah it it's not smart enough sometimes to figure out that pattern you're like look I gave you four dates just give me the first and the 15th of every month alright so it didn't pick up on it that's what I thought was going to happen it just starts repeating the sequence okay so still pretty handy now in any of these sequences that I've done there's this little hint here you can always override what's happening with with a right drag this is a this is something you rarely do with your mouse always it's almost always the case when you drag with the mouse that you're dragging with the left mouse button in Excel however a right drag actually does something let me go back to this example here with a single date let's say that I actually want to repeat 1 1 2017 all the way down I'm going to put my mouse on the corner of the cell at the bottom right corner but instead of left dragging I'm going to right drag all the way down now when I let go I'm going to get a little pop-up and I'm going to actually say copy cells and now that gives me 1 1 2017 all the way down ok so you can always override the default action with a right drag okay you can also this trick also works with with text dates and by this I mean things like the days of the days of the week and the months of the year so for instance here is Monday all the way I do is drag it down and it just automatically does the sequence okay if I have here Monday Wednesday just like with other stuff it's just going to it's going to go every two days okay now it's notice it didn't go Monday Wednesday Monday Wednesday Monday Wednesday Monday Wednesday because it assumed to my sequences that I'm going every two days so it's got Monday Wednesday friday-sunday Tuesday Thursday Saturday and so on and so forth if however I really did want Monday Wednesday Monday Wednesday all the way down do the right do the right drag trick I just talked about right drag down and say copy cells and now I can override what I just what just happened there okay months work the same way if I drag much January down it's going to go through the month names I took a little extra time here because we're going to use this trick when it comes to formulas and functions a little bit later okay all right so I'm going to skip over custom lists you can look at that slide later because I'm getting a little behind here so let's talk about formulas let's get to the meat potatoes what we're doing here formulas basically or how excel does math and when I want to create a formula in Excel we always always always asterisk start with the equal sign that is what indicates to Excel that I'm writing some sort of formula that I want to perform some sort of math what's interesting though is that the equals sign is the first thing that you type in the cell you know when we learned math all those years ago you always put the equal sign at the end in Excel we start with the equal sign why well it's easier for the product to find it okay because because some formulas can get really long so if I was typing a really long formula and then put the equals at the end and it takes more for the product to find it so they just put the equal sign at the beginning and that just says hey you got a formula here now there is one exception to the formulas always start with the equal sign and that's something called an array formula and we're not even gonna touch it it's like that's like advanced advanced excel so let's not even worry about what array formulas are okay so if I want to create a formula in my spreadsheet to add things up or subtract or whatever math I'm doing we just we just do the standard mathematical operations that that we've always learned from years and years ago the only slight differences we have to do some different characters you know multiplication yeah you know most of us back in the day we learned multiplication with the lowercase X the reality is is that in computers it doesn't work so well because they really can't tell the difference between a lowercase X and multiplication so in a computer we use the asterisk for multiplication division it's a forward slash because we don't have that funky symbol that we learned I don't know what the technical name of that thing is all right so let me bring up another spreadsheet let's go to this one if this is the one I wanted yep that's the one I wanted and if I just want to add up some numbers all they need to do is write a formula and use the right operators so if I want to add up these four quarters of numbers I literally just come to cell g5 and I okay I want to write a formula so I'm going to start with the equals sign and I want to add up c5 d v e5 and f5 so I literally just go c5 plus d v + e5 + f5 now some of you are going to go Scott why are you doing the long way I'm doing a long way for a reason okay we'll get to the functions later there so all I needed to do and then work perfectly fine I've got the result 23,000 442 okay and that added up c5 d v e5 and f5 now let's talk autofill we just learned it right well I basically need that same formula down the next two cells so all's I got to do is grab the corner of the cell drag down boom done you don't need to write that formula over and over again especially after writing all those this Plus this Plus this Plus this easy enough just drag it down so this is why I spend some time on autofill because you can use it here for your formulas it's really convenient okay we'll show you some other tricks with that later but notice the formula here is really simple it's the same math we learned back in third grade or second grade or whatever cell plus L plus L plus L if I wanted to subtract them I could pay c5 - d5 - II five minus f5 that's not going to work very well here because of the numbers that I got but still the same math nothing's different it's exactly what we learned way back when okay oops let me close autofill I don't need this spreadsheet anymore we'll go ahead and save that and yeah all right we're good however this same math that we learned for quite a while took a big change when we got into algebra okay and we started to encounter things that had more mass in the same sequence right for the longest time was always like all right if we're adding stuff up it's just addition and if we're subtracting stuff that's just kind of all together but then we hit algebra and an algebra we like oh well now we've got subtraction and addition and division and multiplication all in the same all the same little problem here how do I solve this and that's where something very important comes up called the order of operations in that when I've got mathematics that involve more than one type of operation I need to do them in a specific order and that specific order is here on this slide parentheses powers multiply divide add and subtract you also made some of you may have learned it as parenthesis exponents multiply divide add subtract and I think most of us probably learn this demonic for remembering it and that is please pardon my dear Aunt Sally or again if you learn to Eve you the eat version you got the please excuse my dear Aunt Sally this is the order that we must solve things in if I had an X half an hour I would go through the Excel math tests that are uploaded for you take a look at those and apply these rules and for each math test I've pasted the link there to one of my onedrive go ahead and download those and I want you to look at those and practice that later and and then practice using the you know first off do the test and then look at the answers and you'll see on the answer sheets I've actually shown you how people get the wrong answer and then how you get the right answer okay because people if they don't know there are order of operations you'll see that that's the right way why is this important in Excel because we're doing math and if you don't know the order of operations and and the right way to solve things then Excel is then you're going to get the wrong thing in your spreadsheet okay if I need Excel to add some stuff before it multiplies then I've got to use parentheses because parentheses come first okay because if I didn't use the parentheses well then Excel would multiply first and then do the addition and maybe I needed to do the addition first and then the multiplication and that's where we use parentheses because parentheses takes precedence over everything else just to further clarify the rules so you got it if you have multiple sets of parentheses in a problem or if you have them inside of each other you solve from the inside of the outside now yes the mnemonic goes multiply divide add subtract but the reality is that multiplication and division are the same thing so if you've got multiple if you've got multiplication and division in the same formula basically just solve them left to right regardless of type addition and subtraction are the same thing think about it a subtraction is literally just adding a negative number again that's something we learned in algebra and so since subtraction is just a form of addition if you've got multiples in in a formula you just solve them left to right look at those math tests that I uploaded I do not have time to go over those here and that really will go that's a good practice for making sure you uh stand order of operations when you're writing your formulas we're not going to get this detailed with the formulas that we write tonight but in what you start to do I think this is going to be important okay all right now when we copy formulas and I just did this with autofill there is some interesting magic that happens here when I copy a formula whether I and whether you use standard copy and paste or you use autofill there's some interesting magic that happens here just like text and numbers as we've said formulas can be copied and I already did it but the magic here is that Excel will adjust the formula so basically that it does the same thing just in a different location so for instance looking at my example here where I had C 5 plus D 5 + e 5 + f5 when I autofill that down to the next cell Excel just basically adjusted everything automatically for me so I don't have to rewrite it Excel smart enough to go OK you need this formula you need it to be basically the same thing just in this spot so I'll fix all the references for you so now it's C 6 d 6e 6 F 6 and one more cell down c 7 t 77 f 7 so whenever we take a formula and copy it to a new location Excel is just going to automatically adjust everything for you that's fantastic we love that otherwise we'd have a lot more work to do right and we all hate extra work nobody likes to do extra work okay so and I already talked about this autofill can be used to quickly copy a formula to multiple cells and it works in any direction which by the way that's the thing about autofill I didn't spend a bunch of time on autofill works up down left and right just drag in whatever direction you need to go and autofill will work fine okay oops Oh Oh crud let me get back into my presentation here I got a hit the wrong thing where was I sorry about that folks and let's go into reading view from here nope I got to skim all the way through it hold on my mistake right edit that out in post right numbers autofill entering formulas order of operations copying okay so copying our formulas even here let's say I wanted the totals along the bottom again just to show you with autofill if I want to do equals this is C 5 plus C 6 plus C 7 ok I can drag that over and voila it works fantastic everything gets adjusted for me ok and we love that about excel ok all right now while that's fantastic sometimes there is a problem ok let's talk about the relative reference problem excels ability to adjust cell references when you copy a formula as we said is amazing it saves us time we love it and what we've been writing or what I've written in our formulas so far are something called relative references because basically it's relative to the position so that excuse me if I copy the formula somewhere else it relatively changes it alright well that wasn't the right word but it just uses the relational references the problem is sometimes that's not what we want to have happen in a spreadsheet so let's do an example I'm going to open up a different file here and let's look at this similar spreadsheet but a little bit different going on let's look at this I've got a bunch of sales people and again there's some formulas here they're already look we've got the C fixes it's already been done here I've got the totals and now I need to figure out the amount of Commission that each of these people made ok and basically I'm going to take the amount that they made multiply it by their Commission rate and come up with some sort of number now using what I've learned so far you would think it'd be something like this ok I need to take you need to type the equal sign because I'm doing a formula I'm going to take then g5 and multiply it by what we see up here okay that the commission rate is 4% so let's sell h2 okay and there we go that worked great i kendra gets nine hundred and thirty seven dollars and sixty eight cents so okay i need the same thing for pamela and julie right so let's do the tricks that we learned and let's do autofill so you go to the cell you know we go to cell h5 here and we drag down and then something weird happens first off it says pamela gets nothing and Julie gets some sort of weird error that says value and you're like what the heck's going on here I thought I was supposed to be able to autofill things well here's the problem I used only relative references you're like what well let's take a look at what happened okay the formula and in H five is correct notice it says G five times h2 that's exactly what we wanted the problem is look at the next cell down when I move down to h6 notice what happened to the formula well everything adjusted so now the formula is G 6 times H 3 and H 3 happens to be an empty cell like no I wanted it to stay here but the problem is how does Excel know that you used a relative reference and relative references are the the default all the time so Excel went okay I'm just going to move this and even worse down here now we've got G seven times H 4 so I'm literally trying to take some number and multiply it by the word calm and Excel is really confused now it's like I don't know what to do here so the problem is with this formula I need part of it to adjust and part of it to not and the part that we need to stay fixed is the h2 right we need to keep referring back to cell h2 but I want the other ones to change well how do I do that well oops PowerPoint that is where we have something called an absolute reference and that is sometimes we need Excel in a formula to stay locked on to a cell no matter where we copy the formula to and these are called absolute references okay remember the default that we've used to this point is called a relative reference which which means when we copy it it's going to change relative to where we moved it but sometimes I need Excel to not change part of a formula or not change a reference and that's called an absolute reference and we designate that with dollar signs okay so what I really need to do here let's erase this formula and start over I'm going to say equals g5 now that can stay relative because that part needs to keep changing because for the next two rows that needs to say g6 I'm sorry yeah g6 and then H of g6 and then G seven times now this is the part that I need it to always stay the same I needed to stay on h2 all the time now there's two ways to do this I tend to be hardcore and I type it so I said you need to type dollar signs so I'm going to do dollar sign H dollar sign too you need both of those think of the dollar signs as anchors because I'm going to talk about mixed references in a second the dollar sign H says lock yourself to column H the dollar sign to says lock yourself to Row two so you need an anchor for each I need to anchor two wrote to column H and I need to anchor to row two which in this case generates a cell now I get the same number that I did for but this time when I drag it down now notice it does exactly what I needed it to do now I've got here g6 times h2 g7 times h2 okay now why not just write 4% into each formula because that's too much work and what's also really cool about this is that now I could come up to sell h2 and say you know what let's give him a 5% Commission well if I had written the 4% into every formula I would have to rewrite all the formulas but in this case because I pulled that number out and I'm referring to it by way of a formula I can put anything I want there hey the Commission's are 12% this time I can just keep typing numbers here and everything down there just keeps adjusting this is part of the beauty of Excel okay now I'm not going to go into into any examples on the next part here but remember I said you needed both dollar signs that's because there's another type of reference called mixed and in a mixed reference it contains both part relative and part absolute these aren't as common but they can be useful in more common spreadsheets complex spreadsheets I should say basically what it means is if I went dollar sign a for the column is going to stay fixed but the row number is going to change and if I went a dollar sign for the a is going to change but the four is going to stay fixed so again think of the dollar signs as anchors for a boat or anchors for references and you need to anchor both parts okay if you anchor a boat if you only have one anchor you know if the boat can still move around but with two anchors it stays locked okay anybody who's ever used a boat knows that right you need a front anchor and a back anchor to keep the boat in one spot the same deal in Excel if you need to stay lock to the same column and row you need both anchors but sometimes you only want one anchor right now you notice I did it the hard core ways that I actually typed all that stuff into these cells I actually typed the dollar signs myself there is for you keyboard folks a way to do that and that is with f4 notice this I'm going through the g5 x h2 and I didn't type a dollar sign you can just press f4 and it will cycle through all of the variations from here's relative absolute mixed mixed relative absolute mixed next so just by pressing f4 after I write a reference will cycle through the 1 through it will cycle through all of the variations okay I don't tend as much as I love keyboard shortcuts I tend to not do that because I already typed the dollar signs is just as much work okay I'm going to skip over inserting and deleting columns and rows because I'm a little bit behind where I want it to be so you guys can look at this stuff later you've got the slides for it I want to jump right to entering functions I want to spend our last about 25 ish minutes on working with the function part so we've covered a lot of the navigation basics we've covered autofill we've covered the order of operations we talked about writing some basic formulas okay with using plus signs but most of you all went wait a minute why did you write all those plus signs when you can just use the sum function yeah everybody always says that in class but there was a method to my madness because I needed to talk about formulas first now let's talk about actually using functions remember functions and formulas are two different words they mean two different things functions are always part of a formula but a formula doesn't necessarily have to have a function in it okay now what is a function okay functions in Excel are basically shortcuts for mathematical operations some functions pardon the pun are very simple okay the sum function in excel simply sums up or adds up the numbers it's pretty straightforward it just adds other functions can be extremely technical and complicated like PMT or SG dev I mean there's hundreds of things in the product and especially like in the financial ones and other stuff there's a lot of stuff in there I don't understand what it does but it's not my business do I don't need to at this point in time basically a function in Excel is always some sort of single word okay now they're always listed in all caps but the reality is you don't have to type them that way if I am in a spreadsheet and I want to use the sum function you can just type sum however you want to type it you do not have to type in an all capital that does not matter and as I mentioned Excel literally does have hundreds and hundreds and hundreds of built-in functions I didn't bother to look up the number because it varies from version to version but they're all in there there's tons of these now again just to reiterate functions are always used in some sort of formula okay remember the words are not interchangeable all formulas like we said before start with an equal sign okay a function if you have it in a formula is then generally the first thing in that formula okay and once you invoke a function you always follow it with a left parenthesis no space just immediately type the left parenthesis we're not going to go through this type of example today but a formula may contain multiple or nested functions basically what I want you to know here is that the order of operations still applies when it comes to nested function and functions consult solve inside out now here's an example of a formula that uses a function this is kind of our standard syntax so the first part the equal sign that invokes a formula that tells Excel if this is some sort of formula then the next thing is that we pick our function so that's the name of the function now I typed it in all capitals so you could read it but you can type however you want after you type the name of a function it is always always always followed by the left parenthesis there is no space here so left parenthesis that's the third part inside the parenthesis can be a bunch of what we call arguments some of them some functions have a single argument some of them have multiple arguments okay so that's why we do argument 1 comma argument 2 comma dot that means there could be lots of these when you look in the help it will identify what the arguments are then lastly to close out the function you type the right parenthesis again depending on the function that you're using this may look different now within a function how do we refer to cells let's look at let's look at our sum function again actually let me go back to this spreadsheet yeah because we're going to redo what we did here okay what we're going to use from we're going to use functions now when you want to refer to several cells that aren't next to each other you have to use commas in a function now some I'm going to use some as the example but there are lots of there are lots of cases where you may want to use different things if you want to add up three or four cells and you want to use the sum function for that you can separate each cell reference with a comma to be perfectly honest though with as simple as summing and adding is to me it's just a lot easier to use the plus signs okay now if I want to add up a bunch of cells that are in a contiguous range of some sort you must designate that with a colon okay we don't use a dash because a dash looks like a minus sign so that's why we use a colon and the syntax here is that you're going to express this as the top left to the bottom right because basically you're always making a rectangle of some sort so in the case of this sum function right here it's a four we've got a four through C ten again we use the colon here a four is the top left C ten is the bottom right and all cells contain in the resulting rectangle will be summed up okay so let's look at the sum function because that's really the common one so let's say I want to sum up some numbers here I'm going to come here to this to cell c8 and I want to use a function instead of a formula remember formula if I was just using a strict formula here I would do C 5 plus C 6 plus C 7 okay which that works perfectly fine but maybe you don't want to type all that if I want to use a function I would go equals sum now the next thing I have to do is a left parenthesis and now I need to identify from where to where now don't do this this is an amateur mistake and I've seen as some people go okay C 5 plus C 6 plus C 7 know that you're already invoking addition by using the word sum okay now this could work C 6 comma C 7 that works but that's a lot of extra typing what you really want to do is C 5 colon c7 right parenthesis and then press ENTER and there you go there's my sum function c5 to c7 and now kicking it back to autofill I just drag this over and tada all done okay so I use the sum function to do the addition I could come up here and do the same thing equals sum and I want c5 through f5 worked great drag it down and voila and I'm going to ignore this error right now okay easy peasy okay I only had to write two formulas and then I use the autofill to drag the formulas out and I'm all done okay we're going to come back to summing here in a minute but just to let you know when it comes to functions again there are hundreds of these things in Excel and you can find them on the formulas tab here and they've got it kind of all spelled out here in this function library section okay they're classified by groups you can click the dropdowns here and you know you may or may not know what these are again there are tons of these things in here there's even more functions hiding under under these ones my goodness there is just an insane amount of stuff here if you use the insert function button here that has also go also basically the same sort of interface and you can go by category here which is basically the same of buttons the nice thing about here is that when you come through here and look at these it will give you a kind of a description of what each one of them is additionally with all of this there's help everywhere with functions and if you're not quite sure what it does you can always click on a function here and say help on this function and you'll get all the help that you need the reality is is that for 95% of us you use about three or four functions at the most and then you're good okay not the wrong with that I'm will probably be doing some classes specifically later on things like date and time functions and text functions further on down the road but I'm not going to go into those tonight okay a couple of helpful things just so you know if you make formula mistakes syntax errors Excel will prompt you and say hey I think you did this wrong and it also does something called error checking and that is if you're actually manually typing formulas and Excel thinks you made a mistake or maybe a formula doesn't look like the others it may suggest that you fixed that all right you can look at these slides a little more later because I need to get on to Auto summing here okay so autosum all right let's be realistic the sum function really is one of the most used functions in Excel and as a matter of fact it's used so much that some people when you say okay write me a formula they start to just write the word sum without even thinking about it and it's like no no maybe you're not summing things up maybe I need you to multiply okay so think about what you're doing here and that's why my next line here says autosum sorry exclamation point equals is computer yz4 does not equal there's a couple of ways to express that but Auto sum does not equal all formulas and functions basically what I'm trying to say there is is that just because somebody tells you to write a formula doesn't mean that you're going to use autosum or that you're getting even use some in the first place okay it's just a form of whatever that's going on here but basically because the sum function is used so much in Excel Microsoft built a button for it and it's called Auto sum so that you don't even have to write equals sum you can just click a button and it's going to do it hooray we love time savers okay now there's a weird thing about what they added here and I'll talk about that in a second but let's see what this does all right so let me erase all this stuff that I did already and let me come to right here in cell c8 now yes I could type equal sum blah blah blah but you know what that's a really common thing why bother with that I can literally click on the cell come up here to the autosum button which is just the Greek letter Sigma okay for some click on that now it's going to show me here this is what I think you want to do if that's right all I need to do is press ENTER and I'm done so literally a click with the mouse and a key and I'm done I'd have to write anything enough to type anything tech I barely had to think and I got a sum all right let's do it again now I could use autofill here too but let me do the same thing on cell just to repeat on cell d8 autosum hey that looks right that's great now be careful about autosum because sometimes it's not always smart let's look at this for instance I'm going to come here to these cells alright and I'm going to say autosum but look it's guessing wrong I happen to have a column here that says employee number and it's trying to include that and you're like no no that's not right now there's two ways to solve this one way is to simply just correct it by highlighting the actual cells that you want you're like no I don't want b5 few f5 I just want c5 through f5 so I can just drag with the mouse and press ENTER and that fixes it I'm going to show you another trick on how to fix that in a few minutes here's the other thing to be careful of when you Auto sum let me do another one of these okay that's still wrong now watch what happens here when I get to this cell watch what happens when I click autosum notice the first two that I did it went left and right which is exactly what I wanted but now when I get to here and click autosum look what happens when Excel has a choice of going up and down or left and right for Auto sum it's going to default to up-down and in this case look here this cell g7 has two cells above it that could possibly be added together so because or we could go left right so if I click autosum here Excel is always going to default to going up down and that's the wrong direction now I could correct that with the mouse simply by saying no I want these cells I'm going to show you a different trick though for all of this here in a few minutes alright let's come back come back to our slides now the weird thing about the autosum button is it actually does do more than some I don't agree with Microsoft's choice here but the autosum button if you look at the right side of it and click down you can actually kind of do auto average auto count Auto Max and auto min the problem is they cram those all under the same button called Auto sum and to me that just for new users I think that just causes confusion about what the heck's actually going on here but I could do an auto average and auto count and Auto Max and an auto min if I click the right side of that button instead of clicking the left okay now let's go back to that little problem I talked about before Auto sum is pretty good at figuring out what you're trying to sum up but sometimes it guesses wrong or it guesses wrong about the direction or maybe you want to let's just call it do more with Auto sum so there's a way to kind of force this so that it doesn't make mistakes now again sometimes it's pretty obvious if I click here in cell c8 and I press Auto sum it's pretty darn obvious what I want but again we have that confusion here where in this case with Auto sum it was picking up an incorrect cell here at the beginning and then once I got down to cell g7 here was completely going the wrong direction overall so how can we keep Auto sum from quote-unquote misbehaving and doing exactly what I want it to do and in that case what you're going to do is you're going to highlight the cell that you want to add up but you're going to add on one extra blank cell and then click autosum so here's what we're talking about so for the across here I already know there's a problem so what I'm going to do is I'm going to highlight c5q f5 and I'm going to add on the empty cell here at the end so I'm going to highlight those cells okay so I've got the four cells I want to add plus one blank quote/unquote at the end where I want the auto sum to appear so now I'm going to come up here and click autosum and look at that it worked perfectly now you can do this for multiple rows at the same time so for instance I can grab all of these cells go across and say ok I want to add up a cross on each one of these so I'm going to highlight the cells that I want plus the extra cell at the end where I want the sums to appear so now I'm going to click autosum and look at that it's just like magic boom there they are because you've defined exactly what you want to add up you've defined exactly where it needs to go so excels I okay here you go now this also works up and down okay I could highlight these cells plus this one and say Auto sum works great I can also do it on these four columns at the same time and say okay I want to sum up each one of these and put the totals down here at the bottom voila that's actually faster than the autofill right because memory earlier we were doing this I was saying okay give me this cell auto summit okay now let's click and drag that across all right that works that's more work I can eliminate a lot of the work just by doing it this way and summing and you know what I'm going to take it a step further well I was going to so it's not a surprise the reality here with this little trick is that it can work in both directions at the same time so look at what I've got here I've got totals that I need to go across and totals that I need to go down so I can do this I'm going to highlight all the cell's plus empty cell at the end and empty cell at the end so an empty cell at the bottom an empty cell on the right okay now I'm going to click out a sum and watch this magic look at that filled out the entire thing for me so it did every sum up and down it did every sum across and then it did a sum of the sums and gave me the total basically for everything that's cool I literally just did one tutor I did all those formulas and all that summing in what two seconds if that if I was to write all that stuff out by hand it might take me five minutes but I can highlight a bunch of stuff click one button BAM done faster than microwaving popcorn which you don't want to burn but you see here the autosum works great okay so a lot of ways that we can exploit that and make it do other fun tricks I like the cells you want to add up with one extra blank cell click autosum and you're good to go the last section here is just a quick overview of some of the other common functions you might run into again we've picked on some quite a bit but does some other common ones you might use our averages max and min and count okay I think I have another spreadsheet here for this example where most stuff is done yep there we go and let's say I wanted an average of a bunch of numbers well okay equals average there's the function left parentheses always have to have a left parenthesis then the range that I want to average in this case it is C 5 : c 9 right parenthesis done and then I can just autofill that across and get my averages okay Max and min work the same way max I'm sorry let me zoom in and again C 5 to see nine that just gives me the smallest number in each one in each area okay equals max left parenthesis C 5 - c 9 drag that over and there you go very quickly I just did a bunch of computations you know by writing it once and dragging it across okay now back to PowerPoint you can write these functions and have them actually show up in your spreadsheet but there's one little fun little detail here also you can also see quick results of all of these functions without actually writing them it's a great way to verify are often this is a great way to verify that you wrote your function correctly let me grab these cells right here alright I just highlighted these five cells look down on my status bar down here when I highlight those five cells Excel does a quick calculation and gives me some results without even having to write a formula it already tells me that the average of these five cells is 79 79 it tells me that I've selected five cells that's the count or I should say and how many cells have content and even as a sum down there 39 895 hey that's pretty much right I could also highlight all of this and get the same information information so without even writing a function or a formula that contains a function I can do some quick calculations you can actually display up to six of these if you right-click on your status bar you'll see your options here right in this section right kind of bucked 3/4 of the way down on this menu you can do you can display an average account a numerical count minimum maximum and some average count and some are on by default you can turn on the other ones if you want simply by selecting them so this is kind of like a quick calculation without even without even having to write a function or a formula or it's a great way to check like you know did I write this average correctly well if I highlight these cells and look down here okay yeah that's right 79 79 okay I did that perfectly correctly yay all right um okay highlight the cells there's the ones enable right click so there's the instructions okay last five minutes well thank you all for attending now before you run away let me let me paste the survey link all right now this survey I really preached if you guys take a few minutes go to that site that it's called metrics that matter and fill out those surveys don't worry you can be anonymous you will not be added to any email list but this goes towards my Microsoft certification so I would really appreciate if you took a few minutes after this class to fill out that survey hopefully also you can still see the onedrive link you not only pasted it again just in case some of you came in maybe raylene can't see it so the second link I just pasted there is to my onedrive and that's where you can download all of the sample files for tonight after the training tonight I will then upload this to YouTube and I will paste that link in the Facebook invite and you can you can watch this training later as well as everybody who missed it which everybody did miss this tonight there was like 15 some people that said they were coming and I don't know what happened I guess everyone forgot there was something more important or I don't know whatever they had other things to do but hey I am very glad the three of you are here um hey you know what send me your suggestions for future sessions I love doing this looks like we're getting some good responses from people I just need to find nights that don't have a lot of conflicts and get people interested invite your friends you're welcome to share the links to these trainings with your friends anybody have any questions I haven't seen a lot of chat I've not seen a lot of a stuff in the chat window tonight so either I was answering all your questions or you're just absorbing but if you've got any questions you can go ahead and type them in the chat window and I'll be more than happy to uh spend a few minutes and answer those questions for you otherwise I go back to the one where you did the audicence in what respect Jason that total was not right what do you mean that total was not right which one where where I did the Auto sums right here what totals not right it was 40k ish oh yeah there's a total down here that's not an autosum that's something from a previous example I didn't write this they were just if you look at the formula they're just adding up g5 and g6 that this file was used for some other training stuff and I just didn't bother to erase it so yeah good I I was just ignoring that yeah the real the real total here is seventy one thousand this was just some sort of other total that was there yeah good good catch on that I should have I should have removed that from the spreadsheet to not be cannot be confusing any other questions for any of you all right well if there's no further questions again thank you all so much for attending tonight I hope it was worth the hour or hour and a half that you were here would love to hear more suggestions for classes that you want me to do online tell your friends tell your family I'm also available for private tutoring and other training engagements if you are interested or know somebody who is interested in that sort of thing please send them my way otherwise again please take a few minutes to fill out that survey I'd appreciate it otherwise thank you everyone and enjoy the rest of your evening

Channel: Scott Skip Concilla

Views: 93,865

Rating: 4.8864627 out of 5

Keywords: Microsoft, Excel, Training, Formulas, Functions

Id: cyHA99_J1tQ

Length: 81min 59sec (4919 seconds)

Published: Tue Mar 07 2017