Excel Formulas and Functions Tips 1 - 5

welcome to the course 25 must know formulas and functions in microsoft excel my name is tom fergale and i'm a microsoft certified trainer so if you have the full course the excel file that i'll be using should be part of the course and this powerpoint that i'm using should be part of the course as well so let's get right into it so i want to minimize powerpoint and we're going to go into excel and i'm going to open up the workbook that comes with the course so in my case it's going to be in my desktop folder so i'll pick on open and then browse and mine is going to be on my desktop and there's the student file by the way i try to make the mouse a little bit bigger for you so you can find my mouse also sometimes during the class i'll make my mouse do this so you can find my mouse this way so i'm going to go to a different sheet this particular workbook has many many sheets so i'm going to right click on this arrow at the bottom right click and then you see a list of all the sheets they're not in alphabetical order they're in the order they appear in the workbook so i'm going to scroll back up and we'll start with a sheet that's called autosum relative and absolute so the sheet is called autosum relative and absolute and i'll click okay so let's start off with the autosum just in case you don't know about it it's very very important it's going to be used all the time so you can see in column c i obviously want to have a column total so i'm going to click on cell c11 now this could be a big long column all the way down so you click at the bottom of the cloud so you click on the bottom of the column and there's a couple places you can find the auto sum the classic place used to be under the home menu home and then here's the auto sum icon over here but also now it can be found under the formulas menu so pick in the formulas menu up at the top and then we have the auto sum here and then typically it'll put the right formula in there for you it says equal sum c4 through c10 which is correct so usually the autosum will put the proper formula in there for you now later on i'll do another example of the autosum where it doesn't pick the proper range and then i'll show you what to do about that so by the way when you click on the pull down for the autosum then you can get other calculations like sum average count minimum and maximum now the next tip i want to show you is something that's called a relative formula so a relative formula is going to help you build your spreadsheet a lot quicker so i'm going to go to cell d4 let's say the february column is going to be a 10 increase over january so of course all formulas start with the equal sign i'm going to click on cell c4 and i'll use the asterisk for multiplication and i'll type in 1.1 so it says equals c4 times 1.1 and then we have a 10 increase now i want to copy that formula all the way down the column so i'm going to click on that cell follow my mouse i'm going to move my mouse to the corner of that cell and get the black cross you have to be right on the corner of the cell to get the black cross that's called the speed fill or the auto fill i can drag it to the right or i can drag it down so i'm going to drag it down let's see what happens the number is filled in but let's see what happened to the formula so the first one says c4 times 1.1 that's the one i typed in the next one down says c5 times 1.1 the next one down says c6 times 1.1 so when i clicked in the cell and then got the black cross and dried the dam it copied the formula but i changed each one relative to that formula it's called a relative formula so when you see most people's larger spreadsheets it's hardly ever the case where you type in each formula one by one whenever you can you do that you type in the top formula you get the black cross by the way we can also drag it down which we'll do in a couple minutes or we can drag it across and then i'm going to drag it down and then each one change relative to that cell it's called a relative formula once you learn that you're going to use it over and over again so what if march is going to have a 10 increase over february and april will have a 10 increase over march watch what i'll do i'm going to highlight the numbers in the february column i'll get the black cross and drag it over to the july column i know this all filled in and they're all relative formulas so the next one over says d4 times 1.1 here we have e4 and f4 those are all relative formulas like i said once you learn that you're going to use it over and over again so let's click on this cell before we did it with the auto sum and it says sum c4 through c10 so i'll get the black cross and drag it over to the right each of those filled in so the next one over says sum d4 through d10 the next one over says sum e4 through e10 so those are relative formulas now let's do another absolute let's do another auto sum so i'm going to go to cell j4 and again i'll pick on the auto sum over here and then typically it puts the right formula in there for you i'm going to hit the enter k good now let's do another relative formula i'm going to get the black cross and drag it down to row 11 and each of them filled in this one says sum c5 through i5 and c6 through i6 and so on so there you see the relative formulas now let's do another example of the auto sum but this time it's not going to have the correct range so i'll tell you what to do i'm going to come over here and i'm actually going to well whenever we insert our column at column k i'm going to right click on column k and i'll pick on insert so here i'll have the average so watch my mouse this time i'm going to click on the pull down for the auto sum and i'll pick on the word average if you look closely it did not pick the proper range that uh that time it says c4 through j4 but if i included j4 in the average the number would not be correct so watch what i'll do i'm going to hide at a different range i'm going to highlight from c4 to i4 so usually the autosum picks the right range for you but if that you can highlight your own range i'm going to hit the enter k watch that one again so i'm going to pick in the pull down for the auto sum i'll pick in the word average it did not pick the proper range so i'm going to highlight my own range i'm going to go from c4 to i4 and then i'll hit the enter k good now we'll copy down using the relative formula i'll get the black cross and drag it down each one of those changed uh this one says average c5 to i5 right so those are relative formulas so let's go to the third point the third big tip with formulas and functions we're going to talk about the absolute reference so let's go to cell l4 here and i like to get the percent of total so i'm going to take the total for that row and divide it by the grand total let's do it the normal way first so i'll type in the equal sign click on this cell j4 divided by the grand total which is j11 so it says equals j4 divided by j11 i'm going to hit the enter k so this is 21.74 percent let's see what happens if we copy it down we're going to get the black cross and drag it down to row nine now they say division by zero let's see what happened you see there's still relative formulas this one says j4 divided by j11 the next one down says j5 divided by j12 there's nothing in j12 so that's why it says division by zero but i don't want to divide by j 12 i want to divide by j 11. the next one then is going even further away it says j 6 divided by j 13 except i don't want to divide by j 13. i still want to divide by j11 so the absolute reference is going to be an important one to know about i'm going to go ahead and click on cell l4 and follow my mouse i'm going to type in a dollar sign before the j and a dollar sign before the 11. when we see the dollar signs in the formula we are not talking about money right now that makes it what do we what we call an absolute reference which means when we copy it down that part of the format is not going to change it's going to remain constant or absolute but you really have to say it i'm going to hit the enter k now the dollar signs don't make a difference until you copy it down so i'm going to click on that cell hit the black cross again and drag it down now we have real numbers let's see what happens the next one down says j5 but is still dividing it by j11 because of the dollar signs that's called an absolute reference the next one down says j6 j7 notice how they're all dividing it by j11 and that's the results of the dollar signs that's called an absolute reference let's try another example of the absolute reference so i'm going to go to uh this cell the sales tax will be the total for that row times the tax rate so i'll say equals click on the total for that row j4 times i'm gonna get the seven percent which is m2 now that's the part of the formula i want to make an absolute reference you can either type in the dollar signs or a keyboard shortcut is to use the f4 function key on your keyboard i'm going to press f4 and now that put the dollar signs in there for me so you can either type in the dollar signs or you can use the f4 function key and that'll put the dollar signs in there for you so i'm going to click on that cell and copy it down with the black cross the next one down says j5 but it's still multiplying good by m2 that's called an absolute reference the next one down says j6 and it's still multiplying by m2 so the first part of that cell first part of that formula is relative this one says j7 j8 j9 but they're all multiplying by m2 because of the dollar signs so the second part of that formula is an absolute reference okay so hopefully you got the idea of the order sum which was tip number one the relative formulas which was tip number two and the absolute formulas which is tip number three uh the next thing i'd like to talk about is making formulas that will link your sheets together so i'm going to go to a different sheet here oh by the way let me go back to those slides the slide that should come with the presentation if you bought the full course from my website so so here uh when i made this slide the autosum was under the home menu it's also under the formulas menu so i picked on home and then autosum over here and then here is uh it did the auto sum as you can see then here is where we did the relative formula i said c4 times 1.1 and then we copy that one down with the black cross as you can see in that cell and we copied it down as you can see those are all relative formulas and then i copy it across using this slide and those are all relative formulas then here we did another autosum let's go to slide nine and then here is the absolute reference that's how it has the dollar sign before the j and the dollar sign before the 11 which makes it an absolute reference good now let's uh let me show you the next step which is going to be making formulas that will link the sheets together i'm going to go back into excel now and i want to go to a different sheet so i'm going to right click on this arrow and i'll click on sheet 2014 and i'll click ok so we have the company budget for 2014. if i go to the very next sheet i see sheet 2015 notice how there's a space for the 2014 totals here so let me show you two ways to link your sheets together in fact over here i'm going to type in 2014 grand total now i'm going to go back to sheet 2014 i'm going to highlight these numbers on the total row and we'll just do a normal copy now i'm going to go to sheet 2015 we're not just going to do a paste we'll do something that's called a paste special so i'm going to right click on this cell i'll click on the word paste special and then you pick in the word paste link so we do a paste special and then paste link so notice the formula is pointing back to sheet 2014 on this case cell 11. whenever you see the exclamation point in the formula it means you're referencing a different sheet so we're referencing the 2014 sheet cell c11 so if if that other sheet changes then this should change let's see if that's the case i'm gonna go back to sheet 2014. now if you see those blinking cells just press the escape key on your keyboard good so i'm going to change this number over here to say 20 000. now let's change this total to 111 000 let's see if it changed on sheet 2015 and it did so we copied from one sheet we went to another sheet and we said paste special and then paste link and now the sheets are linked together so if i change one it'll change the other now let me show you a second way to link your sheets together this time we're going to start on the 2015 sheet and as we're building the formula we'll go back to the other sheet so i'm going to type in the equal sign here as i'm building the formula i'll click on sheet 2014 and then i'll click on the grand total over here which is j11 now i can keep on going and do more with that formula but this will make the point that i'm trying to make when i press the enter key it'll bring me back to sheet 2015 and we'll see this number i'm going to hit the enter k and there's that number now i'm going to click on the cell and notice how it's pointing to the other sheet cell j11 so that time we start the formula on one sheet with the equal sign and as we were building the formula we clicked on cells on the other sheet so that's how you can make your sheets together so you can see the number says one million fifty three thousand and seventy five dollars and ninety eight cents so i'm gonna uh and this sheet this number says a hundred eleven thousand so i'm to click on sheet 2014 i'll change this total to say 18 000. so now the total for that column is 114 000 and the overall total is 1 million eighty one thousand five hundred and thirty seven dollars and forty nine cents let's see if she 2015 has those and it does so there's two ways to link your sheets together you can copy from one sheet and then go to another sheet and you say paste special and then paste link or you can start the formula on one sheet with the equal sign and as you're building your formula you can click on cells on another sheet by the way that's how you can link your workbooks together as well i can copy from a workbook go to another file and then say paste special and then paste link that'll link the two files together or you can start the file on one workbook and then as you're building the formula you can go to the other workbook so that'll make your workbooks together as well so now we're going to go to point number five point number five is gonna have a very popular function that's called an if function so we'll start with an if then in the next couple tips i'll show you how to make the if more complicated so let me go to a different sheet here i'm going to right click on this arrow and i'm going to scroll back up and find a sheet that's called sumif countif and average if we're going to be on this sheet for a while sum if countif and average if and i'll click ok good so here we have the sales tax and i'm going to go to cell g2 so let's just say if it says dinner in column f then they'll get zero percent sales tax otherwise they'll get the um look at the seven percent now you can type in the formula but in this case i'm going to use the formulas menu up at the top formulas and this one happens to be under the logical category so i'll click on logical and i'll click on if all right the logical test will be some kind of comparison so i'm going to click on cell f2 type in equals the word dinner here has to be in quotes because it's text so when you're using these formulas usually the text will be in quotes like that and i mean the double quotes so it says f2 equals dinner now it's either dinner or it's not if it is dinner then the value of true will kick in and if it's not then the value of false will kick in so for the value of true i'll type in zero for the value of false i'll type in seven percent times the number for that row which is e2 the logical test says f2 equals dinner the value of true says zero and the value if false says seven percent times e2 i'm going to click ok now you can see the if formula over here you could always type it in once you learn it or we found it under the logical uh category of your functions now you can see how the formula is broken down we have the logical part the true part and the false part the different parts of the formulas are separated by the commas so this one does say dinner and that's why it's a zero there i'm going to go ahead and copy it down with the black cross let me show you a shortcut by the way of course you can drag it down but the shortcut is to double click on the black cross if you double click it goes all the way down so i think that can save a lot of time notice how the ones that say dinner are gonna say zero over here but the ones that don't say dinner have a real number those will be those would be seven percent times the number for that row by the way those are all relative formulas so for example this one down says f3 and e3 this one says f4 and e4 so those are all relative formulas now i'm going to make that column look better i'm going to highlight column g and we'll make a currency format it looks a whole lot better good so that's the basic f now let me show you ways to go further with ef thank you for watching this video if you liked it please like share comment and subscribe to my channel if you really liked it please click on the thanks icon that looks like this below this video to support the channel please check out some more of my free videos you can also subscribe to my mailing list and find out about my other services by going to www.pcwebinars.com thank you again you