Master the MOST POPULAR Excel Formulas and Functions

welcome to this webinar is great to have you here it is going to be an amazing webinar today we have one of the most knowledgeable Excel teachers out there in Excel Microsoft certified trainer his name is Cole pew and he is standing by o to teaching one of the most popular Excel features his formulas and functions it is a feature that you must know in order to increase your Excel skill level and he's probably one of the most asked webinars that I get asked and I've been asked over laughs she is hey John when are you gonna create a formulas webinar about vlookups about Eve's winning the credit that well the day has arrived today you are going to learn man now before we get started a couple of housekeeping rules or make them note here first of all make sure that you close any other web browsers that you have open like Facebook you shouldn't be Facebook because you're at work right now or any kind of accounting ERP systems like Oracle or people saw or Maya whatever can't suck where you have open closed that because that drains a lot of the bandwidth of your computer and it's gonna slow down maybe freeze they were so close those now and that will help with web experience if the webinar freezes during the weather was you're inside for example 20 minutes into word or 3 min simcha where you fit in a phrases just go back to your link in your email click on that and that will open in your browser refresh it and you're gonna go straight back to where you left off so you never miss out okay now the next thing is to close your door close any distractions that you may have so block this hour out going out photos or put on you know do not enter here learning yourself it's more important than anything else is happening right now in the world so do the hat put it out there because this is gonna be I mean thanks love it now you're gonna learn a lot of stuff are you going to be concentrating so make sure you do that the other thing is if you think that card is going too fast you know I may be going fast announce because hey I'm nervous to go through if you think college is going through fast then that's okay to step back because this webinars being recorded and the half of the web and I went to send you an email with the recording and also the finished work so he can review it at the end okay because you're not gonna get a lot of stuff today I never learned formulas within an hour it took me a bit of time so if you ever get stuck during the webinar just sit back enjoy the webinar and then you can always go back after mmm for your afterwards now the other thing is that you're going to receive the finish workbook so when your mother left you as well so you go practice a bit she needs to go back and practice practice make mistakes practice make mistakes practice practice practice you're going to increase your ask yourself okay now next one I need to ask you is there's a chat box if you ever have any questions throughout the webinar put it in there so hey you know I don't understand these I'm going to do man the check boxes and I'm gonna try my best to help you out there's a lot of people in the webinar now if I don't have any time they're after the webinars I don't want to personally a mommy so put in any questions you have in the chat box I'm going to receive that question along with your a model that he signed up with and I will personally a model to you there now I need to know we need to know number one what is your favorite excel formula and also what excel formula did you want to learn more about put it in the chat box now putting that favorite and your formula and what you want to learn about and the formula you want to learn about they just give us feedback so we can create more webinars or more tutorials on formulas that you're struggling with and you need pity okay we're ready to get started wait after them after coffee stirrers is he's his teachings there is a special bonus so what it is got a guillotine I'm wearing it right now okay let's get started Oh party automotive coiled pure and he is going to teach you the most fabulous excel feature how to Excel formulas and it is one that you must know so let's get right into it when coffee just I'll get back there with my special bonus and despair it's gonna be great hello and welcome to this Microsoft Excel formulas webinar my name is Kyle Pugh I want to thank John first off for allowing me to participate in this webinar and to meet with you all and go over some of the common and popular Microsoft Excel functions I'm going to bring up a list of topics here that we're going to get into and then we're going to jump into an excel file and we're going to go through a couple of examples of these formulas these functions as well as a few other tools so take a look let me change my screen here we've got a few different topics that we're going to get into first we're going to talk about a handful of different functions and these are perhaps some of my favorite functions that are built into Excel the first one which is really my absolute favorite we're gonna talk about the if function now the if function if you haven't used it before is going to allow you to set up a condition and then have Excel evaluate that condition and to perform one of two tasks based on whether the condition is true or whether it's false if this is true do this if it's not then do something else again I love this function because you set it up once and it does all of the work for you now the next one that we're going to take a look at kind of builds on this idea of a condition a condition using the if we're gonna sum if something is true I want to build a grab a range of cells and I want to be able to sum up a value but I only want to be able to sum it up if something is true this is your sum if now there's also a very closely related function here called sum ifs where you can set up multiple criteria I want to sum if these things are true now we're gonna switch gears and then we're gonna take a look at the vlookup this is another one of my favorite functions that's out of Excel very common more advanced type function but you want to look up information I have a large list of data I want to build a lookup information within that list well rather than manually digging through line items one at a time get the vlookup to do it for you now the vlookup I mentioned it's it's it is one of my favorite functions inside of Excel but it does have some limitations there's some kind of drawbacks to the function so I'm also gonna introduce you to two functions that will perform a very similar operation to that the vlookup does but it doesn't have the limitations that the vlookup has and these are the index and match functions another one of my favorite functions are pairing up functions inside of Excel then after we get done talking about these functions I'm gonna take you to three little tips of evaluating or auditing your functions and we'll go through a few techniques there so let's go ahead and jump in and start to start to master these techniques and these built-in operations inside of now I'm gonna switch my screen here a little bit there we go now I've got this exercise file that is going to be available to you to download this way you can't get back in and practice the techniques that we're going to be discussing here this is this is gonna be the exact same file that I'm gonna use during this webinar and you're gonna be I have an option to build and download it and go through all the exercises on your own as well okay so watch for that link so the first one that we're gonna take a look at here is the if function again one and one of my favorite functions inside of Excel so let's jump in and take a look at this one so I'm looking at a worksheet called if function here now within that worksheet I've got a simple little table just of some salespeople and some weekly sales amounts for those sales individuals we've got some some totals there along the bottom just their weekly totals and we've got the totals for each salesperson as well as a couple of the grand totals for all of the sales across all weeks and all salespeople now over on the right this is where we're gonna focus we've got a monthly goal we want to know whether or not each salesperson has met the monthly goal well we've got their total we got their monthly total right here right for example each James each of the weeks values for each James has matched up to thirty six thousand two hundred and forty five now I want to be able to compare that value right there against the monthly goal and decide whether or not each James or Kay done or L carry our Smith or deal Brian has met the goal or exceeded the goal okay so in steps DF now granted we only got five records up there so I want to be too hard to see here and say hey 36,000 yeah that's greater than 34 31 no sorry I didn't make it 33 no didn't make it 39 yep you made it and 35 359 yep you made it so five records right except there's a yes no no yes yes right what if you had hundreds if not thousands of records or hundreds of thousands of records I don't want to do that manually let's get excel to do it for you so once again in steps the if so I'm gonna hop into H five so H five to h9 this is where I want it to say yes or no as far as that condition so I'm gonna go into H five I'm gonna start to type in the if so I'm gonna say equals if I'm gonna open up a print sees here and nice seen ever since the I want to say like the 2003 edition of Excel you got this nice little screen tip that pops up on the screen this is your if function and it's got three little things that it wants from you in order for it to perform its job we need to provide it with three bits of information so to help us out here a little bit rather than typing it manually especially if you're just starting out with the function I'm gonna go up to a little F X button just up here on my my formula bar I'm gonna give that a click and this will open up the argument window for my if you get my function arguments and I'm looking at the F function so again it's got three little things that it wants from us first the test what is it evaluating well in our case I'm looking at H James right I'm gonna say f5 and I want to know whether or not that is greater than or equal to I to there's our tests nothing big now I'm gonna go ahead and give it a value of true so it's gonna test for that is f5 greater than equal to I to now the test is gonna return one of two things you can see here that it's returning true because yeah thirty six thousand two forty five is greater than thirty four thousand then I'm gonna tell what to do if it's true I'm gonna say yes and then I get to go down to the next argument and tell what to do if that formula or that test happen to return a false so I'm gonna say no you didn't make it now this can be whatever you want it can be goal goal met no goal could be another formula maybe you want to say hey if somebody met the goal then I'll give you a ten percent of your total sales if you didn't meet the goal then you get a big fat zero or maybe you get one percent whatever it might be so here's mine logical test if f5 is greater than equal to i2 we'll give them a true yes if it's not then we'll give them a No and that's it I'm gonna hit okay and eight shapes of course we sell that earlier each James met the goal exceeded the goal 34,000 compared to the 36 to 45 we got our yes now that's the if I love it and the reason why I love it is you set it up once and you're done right you've got the condition in there something we've all done I know I can guarantee you've all gone through data before and compared it against something else maybe you got dates that you're trying to evaluate or numeric values like this or maybe it's texts I want to know if these text values equal these text values whatever your condition is we've all done it get the if to do it for you now I need to get this to happen for K Donnell carry and our Smith and do Brian now inside of Excel we all use a little autofill handle that little box in the corner there we grab that we drag it down this is just gonna copy the formula I let go and it's copied my formula ooh wait a minute I got a bunch of yeses in there first one that was correct right look back at the formula F 5 greater than equal I to which is the total there the the monthly goal no and then we tell it well if that's true then give me yes if not then we give him a No so I copy that formula down and I've got a bunch of yes is the flexed person the very next person K done should not have made it well let's look at the formula for K done remember I just copy that formula down I look up there and pay f6 f6 is K tons total that looks good i3 i3 I got wait a minute I I three is one cell down that's not one I want to compare against you ever see this happen never copy a formula expecting one thing and it gives you something completely different well here it's moved one cell down too far if I go down to the next person now carry now it's I four it's not sounding good I'm gonna delete these formulas just get rid of those copies I'm gonna go back to the original one and I need to make a change here I need to take this I too and I need to make it absolute now this is a whole nother discussion and we can spend a whole note of the webinar getting into relative versus absolute references but the point is when we copy the formula I never want I to to change it should always be I to so what I'm gonna do is I'm gonna click up there right before the I'm gonna put a dollar sign and right before the two I'm gonna put another dollar sign so dollar sign dollar sign - that makes it an absolute reference I'm gonna hit my Enter key same results H James don't got a yes when I copy that down and there we go yes no no those two did not make it and then yes yes looking good your if function you define a condition they call it a logical test something for it to evaluate and then based on how it evaluated whether it's true or false you tell it what to do again one of my favorite functions inside of Excel so there's the first one hope you like that one any questions you got jump into the the message board the QA the little question box there ask your questions we want to get to those questions as well so all right let's take a look at the next function this is the sum if function now when the let's see I think it was the 2007 version and some if has been around for a while inside of Excel but the 2007 or doesn't an right around there one of those versions they came out with another one called some ifs okay very very similar so I'm going to talk about some if and then I'll give you a big old tip about the some ifs as well so let's talk about some it all right so the idea here is I've got two little tables I've got this this little list here records that consists of a month a store number SKU a sales amount and units sold and then I've got this little table on the right where I want to get some totals I want to get sales or maybe total units but I don't want to get all I don't want to sum up all sales I want to give us sum of all sales where the store number equals a specific number so this is where this sum if comes into play you're familiar with summing probably use the auto sound like all the time we just took a look at the if now I want to combine the two some if something is true take a look I'm gonna do the total sales I'll do that one in front of you and then when you get an opportunity to download this file I'm like jump in and do the total units and do it for the SKU as well so I'm gonna jump to total sales and I'm gonna say equals sum if there it is I'll give that a double-click all right now up comes a little screen tip again just like the if it's got three little things and you just needs us to fill out so to help me out I'm gonna go back to the little FX button here we have it so first part is your range so each of these range criteria some range especially if you're just starting out within these functions we need a little bit of definition what do these things expect of me what do they need well if I go to the range it tells me down below the range is the range of cells you want evaluated remember the F if something we need to evaluate something right well here in order for it to sum we need to evaluate something so here I'm going to say range I want to get a total sum if the store number is equal to 3000 so here I want to evaluate the store column so for range and I'm gonna go grab b3 down to be 272 272 alright that's the range that I want to evaluate my criteria well what am I trying to look for inside that range I want to find three thousand one two three now you can set up in your summit function you can set up criteria a couple different ways you can type it in just like I did there that's great that's gonna work or to make it a little more dynamic I can reference a cell that contains that value in this case cell g3 contains the 3,000 that looks good I'm gonna leave it down I'm going to show you a bonus to locating a cell referencing a cell as opposed to typing it in manually 3,000 all right now the sum range we've got what we want the range that we want to evaluate what are we looking for in this case 3,000 and then the range that we actually want a sum in this case I'm looking for a total sales so I'm gonna grab d3 down to d2 72 and that's it that's it what are we evaluating what are we looking for inside that range and which range do you want to sum I'm gonna hit okay there's my total 490 1000 $64 this is total sales all the sales that said the D column but only where the store number is equal to 3000 again another one of my favorite functions were combining two common functions sum which is absolutely very very common inside of Excel and then the if some if the Commission is true now I mentioned I want to show you a little bonus here because inside of our formula our criteria we referenced a cell in this case G 3 here we got the 3000 well you know what maybe I'm looking looking for SKU number or store number 3000 anymore I want store number 2000 so I clicking the g3 I'm going to tape it in two thousand one two three there's my total right now 481 thousand right I hit my Enter key 495 I'm now getting that total sales for that store and that's pretty neat let's try another one say 250 very dynamic one formula referencing a cell summing up if a specific condition is met it's true make sure you train these out getting you download the file try out each of these exercises really master these techniques there save you loads of time now once again if you got any questions jump into the little QA section a little little question and a box there and ask your questions and we want to make sure we get to everybody if we can't get to you right away we will as soon as this webinar is over with we will be sending out emails and response to your questions as well no all right let's take a look at the next one this is your vlookup now this is a another pretty common function now it's a little more advanced there's a bit more going on inside of it now but it's a very popular one it seems like every class I teach on Excel when I go do live trainings people are asking me hey you look up I've heard about vlookup how do you do it right well let's take a look at it so first off what's the idea of a vlookup well imagine this somebody's giving you a list of in this case employee IDs but some list of ID's employee IDs product IDs invoice numbers whatever they've given you a list of some IDs now based on those IDs I want to be able to fill out the rest of this table I want to know for employee number one zero five four what's their last name what's their first name what's their department what's their pay rate and so on I need that information but right now all I've got is a bunch of ID's well located someplace else and in this case the very next sheet we've got all that information we've got the IDS but then we've also got their last name first names departments pay race I think that was what the other sheet was looking for right we've got all that information right there now prior to the vlookup which is what we're digging into here you've probably done this you've probably got some list of some IDs or account numbers or invoice numbers or whatever and you've got a list for all the informations contained and you're sitting there manually going through and comparing the two lists and then when you find what you're looking for maybe you're copying and pasting data between the worksheets well this is what the vlookup does for you the vlookup will take a known value such as an ID in this case the employee IDs it'll say hey I found the ID where am I gonna go look for this you're looking for the last name where am I gonna find the ID that's associated to that last name so I can return that users last name while we say here's one zero five four go over to this list search vertically it's called the vlookup vertical lookup search vertically through the first column once you find it one zero five four I want you to return something from that row that's what the vlookup does for you take a look so I'm gonna do it for last name but I'm gonna encourage you once you download this file go do first name go do Department go do pay rate try them all out so I'm gonna go into last name here then I'm gonna say equals vlookup there it is now it's got one two three four bits of information that it needs from us in order to perform this task and fighting the last name well to help me out once again I'm gonna go to the little FX button there we go so first thing it needs what's called the lookup value ultimately I want to find the last name but in order to find the last name it needs to look up the ID that's associated to that last name so for our lookup value I'm gonna give it a three so a three what is what it's gonna look for in order to find the last name now table array this says Excel says great I'm looking for employee number one zero five four where am I going to find it so what's the table array so I'm gonna go to the master employee list make sure I'm clicked in the table array master employee list then I'm gonna grab this entire list here from a two down to I thirty-eight now you could include Row one not gonna hurt anything but we don't need it so I'm just gonna grab the actual date of the records now the next option here call index num we told it hey I want you to look for what's inside of a three which is one zero five four where am I going to find it well the table array is on the master employee list inside of cells a2 to I 38 now Excel says get great grabs one zero five four jumps over to this list looks vertically through the first column of the list finds it and then it says call index num it wants to know which column to return back to us in this case column number one is employee ID column number two is the last name for ours I'm gonna put a number two in there because I want the second columns value of that table all right now the last thing is called range lookup in this case I'm going to put false there's two options you got false or you got true or you could leave it blank but blank and true mean the same thing in this case false means I want to find the exact match this IID this lookup value that you're looking for Excel I want you to find the exact match and that's what false means you can look down below range lookup the logical value meaning true or false to find the closest match in the first column sorted ascending order you either make it true or you'll leave it blank you omit it if you want to find the exact match I want to find one zero five four or one zero five six you have to find those then you make it false and that's it I'll hit OK and there's my Smith now I'm gonna do one more thing we talked about making absolute values here I'm gonna take that list that we did earlier number eight to tie 38 and I'm gonna make that absolute because I want to copy this down so with that highlighted eight to the I 38 I'm gonna tap the f4 key there's a little tip for you f4 makes it an absolute reference so I hit my Enter key again and now I'm gonna copy that down and there's our B lookup based on some list of numbers some IDs some account numbers in this case employee IDs we could fill out the rest this table using a vlookup now again the exercise pile is available for you I want to make sure you go through and get some practice so go get in there do the first name do the Department do the pay rate and if you get questions jump into the little question box ask your questions in they're super powerful function hmm and again another very popular one lots of people want to know about that very good very very very good skill to have inside your arsenal of skills that make up excel though alright now I mentioned that the vlookup it's great it is very very popular but it does have some limitations the vlookup and it's looking up information but it relies on a specific setup of the data so for ours remember 1:05 for employee ID or 1 0 5 6 we told it I want to look for that number but when you get over to the list it Excel expects that number to be inside the first column what if employee ID was in the middle someplace right and then last night the list started with last name well the vlookup you could still do it but then your list or your table array remember one of the arguments of the function would have to start someplace in the middle and it wouldn't bill to look to the left vlookups can only start from the column that you're searching and move to the right if employee ID was someplace in the middle you could start from the middle and then move to the right you would never be able to go to the left that's one of the limitations of a vlookup another one is they're they're pretty big resource hawks process of intensive processor intensive meaning that if you've got a large Excel document lots of you lookups it's gonna be a slow document yeah it may even take a while to open up because all the vlookups have to perform all their tasks and go through and search the data and just the way the vlookup is built it takes a bit of time now if you don't know all you got a handful of vlookup functions and no big deal but if you've got hundreds of rows of data that you're looking up and that can take some time so again some lemon patience to the vlookup well there's these limitations how can we overcome these limitations this is going to bring us into the next couple of functions that we're going to take a look at and these are the index and match another one or pairing of functions that are perhaps one of my favorite ones inside of Excel so first before we really get in and overcome the limitation of the vlookup let's first understand what the index and the match function do for you they are two separate functions now for the most part you probably won't use index and match all by themselves but more commonly you'll use them together so take a look first let's see what they're all about individually so I've got inside the same exercise files to looking at the vlookup tab I've got this index and matched little boxes what is that G 3 and H 3 so first let's take a look at the index so like I said probably we won't use this all by itself and you're gonna see why so I've got equals index I'm gonna open up my parentheses and I'm gonna go hit the little FX button alright now the index function is a bit unique in the fact that it has two different ways that you can use it here when I hit the end end the little FX button it's asking me do you want to use it as an array row number column table or do you want to use it as a reference row no I'm calling them area numb well I'm gonna say 90% of the time perhaps even larger you're gonna use the first option and that's what we're gonna use here so array roam down column number I'm gonna hit OK alright it's got three little things array row num column num but what does this do well the index function returns a value or a reference of a cell at the intersection of a particular row and column in a given range what do you think well let's break that down a little bit what it's essentially saying is let's say this is a an array or a collection of data from a 2 down to be 18 this block of data right here is an array or a collection of data it's a collection of data the index function will return a value within that collection of data based on the intersection of a row and a column take a look I'm gonna break it down even smaller I'm gonna say my array or my collection of data is this section right here from a three to a nineteen that's my array or collection of data over here on the right you can even see all that data right there one zero five four there it is one zero five six there it is and so on and so on now from that collection of data from that array I want to know what's at a specific location in this case at a specific row number within there so I'm gonna tell it romanum I want to know what's at the fourth position of that collection of data so I'm gonna put in a row number here I'm gonna same row number four now the next one : um it's optional I'm not gonna use it here because my original array or collection of data is a single column but if I had multiple columns in there then I could tell it I want to find once I have a specific row number and a specific column number and then we would get that cross-section right where the two met and it would tell us what's located at that spot so I'm just gonna use the row number I'm gonna hit okay and it tells me from that collection of data remember a three to a 18 what's at the fourth position one two three and four one zero seven five that's your index function taking a collection of data an array of data which is maybe a single column maybe a single row maybe a multiple columns multiple rows and tell me what's at a specific position within that data now once again it's probably not one that you're gonna use all by itself can you think of any reason why you'd say okay and say that block your data give me what's up position ten maybe maybe maybe if you're doing some type of just blind hat type of statistics right yeah I'm gonna reach in there give me what's a position for right okay great but probably not maybe you're gonna use it in combination of other functions now we're gonna see that but before we do that let's take a look at the match so I'll here I'm inside of h3 I'm gonna say equals match I'm gonna open up a parentheses now the match function let's get some help I'm gonna go a little FX button so here's the match looks pretty similar you got three things that it wants from you now what is the match do and the match returns the relative position of an item in an array that matches a specified value in a specified order now once again that's a whole lot of words going on in there now remember the index had an array a collection of data in our case it was this column right here we told it here's the column of data tell me what you find at position 4 that's what we told it earlier the match very similar we're gonna tell it hey go search an array of data it has a lookup array where's your collection of data well we're gonna give it a specific value to look for inside of that array then the match function will return the position of that value within that array take a look let's say for my lookup I'm gonna tell it to look for this this value here which was g3 which was without one zero seven five now my lookup array we get to tell where to search for this so I'm gonna tell it to search for that value inside of a three two eight nineteen so look for the contents of g3 inside of this collection aside of this lookup array now the match type this is similar to the vlookup that we saw earlier this is whether or not you want to find the closest thing or the exact match for the match I'm going to put a zero in there meaning we want to find the exact match that's it what are you looking for where are you looking for it do you want to find the exact match now when I hit okay it's gonna tell me what position that value was found at there it is number four number one zero seven five inside of this collection of data right here one two three four it was at the fourth position we saw that earlier now again that's the match look up a value from a collection of data tell me where you find it just like the index it's perhaps one that you're not going to use all by itself you could once again but more likely than not you're going to use it in combination of other functions and that's what we're gonna see here index and match using these two functions together nested together will overcome the limitations of the vlookup but perform a very similar task - the vlookup take a look so index match let's say I want to find the first names I want to fill out all these first names but I want to do it over here using the index and match functions so I'm going to jump into j-3 I'm gonna say equals index I'm gonna open up a parenthesis now ultimately once again we want to find the first names so let's go into our little FX button we're gonna use the first one there so I'm going to hit OK alright the array now once again ultimately we want to return the first names so for our array I'm gonna go to the master employee list and I'm gonna grab the first name column c2 to see 38 now I'm gonna go ahead and lock that down I'm gonna make that an absolute remember the f4 key now row num never using this earlier we did that inside the index right here of g3 we just dropped in a number I said like number 4 so now it says okay I'm gonna go look through that array I'm gonna return what's in the fourth position well that's really not what I want I don't want to just give it a number because I'm looking for a specific name in this case I'm looking for the first name of employee number one zero five four well what row back here inside the master employee list what row is employee one zero five four located at I don't know I can go over there look and look for one zero five four and say oh it's in Row one or it's in row five or a row 10 or whatever but once again I don't want to do that I want to get Excel to do that for me so here inside the road num we're gonna bring in the match function remember the match it looked for a value inside of an array or a collection of data and in return its position back to us so here for match I'm going to open up my parentheses there got my row num match open parentheses to help me out I'm gonna go click on match here this takes me to the match arguments so lookup value remember I'm looking for the first name but in order to find that first name we need to look for the ID so I'm gonna give it a three the lookup array where do we want to search for this well it's back on the master employee list and I'm gonna grab the a column here from a 2 to a 38 and I'll lock that down let's make that absolute all right and then the match type I want to find the exact match so I put a 0 in here alright that's looking pretty good we're gonna look for 8 3 we're gonna find it inside the a column of the master employee list and we're gonna make it an exact match all right now I need to finish off the index if I go back to the index it's actually all right I'm not gonna use the column num I'm done I'm gonna hit OK look at there's Howard Howard Smith which was employee number one zero five four let's make sure one zero five four Smith Howard there we go if I go back I can copy that down done I've now overcome the limitations of a vlookup and it's much faster in processing using an index and match pairing right there get the same results that have you lookup will do for you but you overcome those limitations of the vlookup that this column right here and point num could be anywhere inside this list doesn't matter doesn't it start at the beginning like a vlookup he could be in the middle someplace because it's two separate functions that are doing the work for you so once again download this file try that out that's a big one and I'll even later on we're gonna provide this webinar as a recorded webinar and you can get in there and you can re-watch it as well and get some practice with that one definitely one I would recommend practicing a couple of times a few times alright so there's a handful of functions we've talked about the if function the some if we've talked about the vlookup and now the index and match lots of things aside there practice practice right now I'm gonna give you a few tips I want to get in there and then show off some little tools that you can use to help you audit and kind of go through your formulas especially if you got errors so we're gonna take a look at a few things so we're gonna take a look at the watch window we're gonna take a look at tracing formulas and we're gonna take a look at a little shortcut called the f9 shortcut to help you evaluate formulas so get ready for those all right so I'm gonna take a look at the watch window first I got another little tab here the watch window now it doesn't look like much and it really isn't just for this example I've got a header in there December 2015 sales and I've got a numeric value in there well that numeric values inside a b3 that thing references a cell in another worksheet if function worksheet cell f12 well if I go back to the if function worksheets here's f12 it references that cell right there now imagine this you're creating some report maybe a dashboard and you're referencing data from all sorts of different worksheets well you've got a worksheet that's maybe 20 worksheets later instead of a workbook and you're over here working on this worksheet but you manipulate data here it's affecting cells back on that other worksheet so now I'm toggling back and forth between worksheets I make an adjustment here I go over to the other worksheet now you take a look at what it did and I'm going back and forth to see what's happening between these two worksheets that are connected well the back and forth takes time so inside of Excel we have a feature called the watch window the watch window is going to allow you to target a cell or multiple cells target some data some cells inside of your Excel worksheets and then keep an eye on it you can always have that on your screen while you're working inside of other locations within the workbook take a look I'm on the watch window worksheet I'm gonna select b3 I'm gonna go to my formulas tab and I'm gonna turn on the watch window all right inside my watch window don't have anything in there currently I'm gonna add a watch all right now I want to watch that cell watch window cell b3 that's where I'm at so I'm gonna go ahead and add it here we have it so it's telling me it's inside of this book it says on this worksheet it's this cell and it's this value one hundred seventy-six thousand two hundred eleven even gives me the formula that makes up the reference inside that cell now I've got this little window open it's always gonna be there now if I go back to the if function worksheet still there and I can watch that cell right there now if I make some adjustments here let's say I change some values I go from eight thousand five baby inside of c7 let's go to ten thousand one two three four there I got just updated that number there which updated this number over here now it's 177 thousand let's change this one a five thousand one two three and now it's dropped down to 173 so now I'm keeping track of a cell on a completely different worksheet I can keep an eye on that that's huge you're not toggling back and forth between worksheets and trying to keep tabs on what's happening if I just this what happens over here you just watch it then you can make your adjustments wherever you need to and watch what happens to that cell and this is the watch window I'm going to close that out it's on your formulas tab then underneath formula auditing you've got your watch window command right there select the cell you want to watch add the watch go make your adjustments and you can keep tabs you can watch keep an eye on that cell on its value there's one little tip for you helping you to audit and keep track of your cells and more importantly your formulas now let's show you another tip here I'm gonna find my next worksheet and have that watch window there I'm gonna go to trace all right all about auditing formulas this next one I'm gonna show you here this is especially important or a super cool tool if you're inheriting worksheets from others that ever happened to you you ever inherit work from other people in your office co-workers or maybe somebody emailed you a document you have no clue what's going on in there now I want to be able to figure out what's happening within this worksheet and more importantly what's happening within the formulas of this worksheet take a look this is tracing so let's say this one 76 right here one hundred seventy six thousand two hundred eleven or even this one right here they're exactly the same I want to know which cells make up that number right there hey or let's say this number right here thirty-six thousand two forty-five how did we get to a number what cells is that referencing or or this number right here why is that number important what other cells rely on that cell right there again this is a sheet that I'm not quite as familiar with so give me a little bit information here I want tres what sells rely on other cells let's go back to the total 176,000 211 I'm gonna go up to my formulas tab underneath formulas back in the formula oddity you got trace precedents and trace dependence all right let's see I've got the hundred and seventy six thousand here if I go up to trace precedents what does this one say show arrows that indicate which cells affect the value of the currently selected cell that sounds good so I've got the hundred seventy six thousand they're selected I'm gonna go trace precedents so for that cell right there these cells right here affect that one this one right here relies on these other cells it needs these cells in order to get that total now let's remove that arrow let's get rid of it remove arrows let's try one of these weekly numbers here I'm gonna grab the sixty nine hundred right there and I'm gonna go up above let's see if I hit trace precedence let's see remember that one says show arrows that indicate which cells affect the value of the currently selected cell well if I hit that button the trace precedents command requires that the active cell contain a formula which includes valid references okay well that cells not a formula so there's nothing else that feeds information into there right here though I'm gonna say I'm gonna trace the dependence which cells depend on that cell give that a click all right so this cell right here right draws an arrow to this total and it draws an arrow to this total these two cells rely on that one they get information from that one dependence tracing a dependent they can remove the arrows from there so helping you to make a little more sense out of a document that perhaps you're not as familiar with or even a document that you created but it's been a while since you've been in there trace the president's and dependence of the various cells related to your formulas let's try one more I click in new cells to this ten than 2-hundred I'm going to trace the dependence so now it's this total in this total let's grab this one here and I'm gonna trace the precedence so these cells here feed into this one it's that simple but a very effective very powerful tool Tracy let's go ahead and remove those arrows so another little tip for you once again auditing your worksheets auditing your formulas let's take a look at one more this is a really neat one it's a simple little shortcut but a very effective and very powerful shortcut and it's dealing with your formulas so I'm gonna go to f9 alright so this should look familiar we worked with this one earlier right we found that goal no goal we use the whole if scenario here now I'm gonna take it a step further I'm gonna build a really quick formula here and this will bring us into the f9 scenario so here's the bonus we want to know whether or not these salespeople remember these guys they met the bonus now what what's the requirements of a bonus well the bonus one they had to have met the goal that's step one receiving the bonus you had to have met the goal step 2 each week each week they had to have made eight thousand or more met the goal and each week eight thousand or more so I'm gonna go over to my bonus I'm gonna drop in and if now I'm gonna do a bit more magic inside of here and this is something you can you can go back and re-watch later on but I'm gonna kind of fly through this just to get us to this f9 scenario and as we get an F 9 it'll describe a little bit more about what's happening here for our if our logical tests we have multiple conditions we have the goal and each week has to be more than 8,000 now a standard if you can only put one test in there just one but I've got multiple so here I bring in an and function the and will allow me to test for multiple multiple tests so my first one I'm gonna say if this cell equals goal that's my first test H 5 equals the goal next comma I'm going to use a min function and say if the minimum of this range of cells right here is greater than equal to 8000 there's my and so now I got my two tests in there where I could only do one normally in seven if the and allows me to do multiple if this is true and if this is true there's our multiple test since I done it and if now I need to tell it for the if I need to tell what to do if it's true so I'm just gonna say bonus if it's true then I need to tell what to do if it's false you know close that parenthesis there's my F now there's a bit more going on in there bit more going on in there but we're gonna get the idea of working with this new tool the f9 shortcut I'm gonna hit my Enter key alright first person made it their third or excuse me their their goal they made their goal and each week was more than eight thousand eight thousand or more let's copy that down alright bonus no bonus they didn't meet the goal right bonus no bonus oh they met the goal but they had two weeks that were less than 8,000 that's the bonus one now it's a pretty complex formula again I didn't talk a whole lot about it just kind of went through the motions there and created that formula and this is gonna happen to you you're gonna inherit formulas from other people on this like well what's going on in there what's happening within this formula or maybe you get an error inside of a formula I want to know why well this feature I want to show you here will allow you to evaluate portions of a formula take a look if I hop up here we got my formula I'm currently on sale i-5 I get in here and h5 equals all I'm gonna highlight that H 5 equals goal I want to evaluate that portion of the formula I want to know whether or not that's true so with that highlighted on my keyboard I'm gonna press the function key f9 look at that it's true I just evaluated that portion of the formula let's try another one let's grab this one right here minimum b5 to e5 greater than equal to 8,000 I want to evaluate that I want to know if that's true so I'm gonna hit f9 and that's true I'm gonna hit Mike's kick here this will wipe out the evaluations just go back to the normal formula let's try it one more I get down let's say this one right here what is that hi 9 so I'm gonna go up in highlight h 9 equals goal let's evaluate that portion right there f 9 that's true now let's evaluate the minimum men b99 greater than equal 8,000 I'm gonna hit f9 again and now it's false so now you to evaluate portions of a formula select a cell it contains a formula highlight all of it or just a portion of it and it'll give you the results evaluating right there inside your formula again super slick especially if you're inheriting work from other people and you're not quite sure you got something a little more advanced or you got an error and you're trying to figure out why this is happening or how that result came about evaluate within your formula highlight f9 now once you're done very important once you're done hit your escape key take it back to the normal formula again all right we've covered a bit and then some of them little bit quicker than others we only got so much time aside of these these the webinars so make sure you download the file ask your questions jump into the question board if you haven't already bang out some questions there we love questions John and I are right here to help you out our goal is to help you succeed with your skills with your knowledge with your path and inside of Microsoft Excel so once again I want to thank you for allowing me to come in and meet with the and go over a few functions and a couple little tips in auditing and going through your formulas so I'm gonna hand the time back over to John and you can talk to you some more inside there and remind you about the questions and downloading the document to practice so I'll talk with you later all right Kyle thank you very much for that fantastic fantastic webinar made I know that based on the feedback and the questions that I've seen and been replying back to everyone was happy and just blown off the chest with the amount of content and what they've learned so thank you for that great 45 minutes and if any of you guys have any questions write them in the chat box Kyle is going to be answering them so put in there and cold answer them there's a lot of people in the webinar if we can't get to you now after the woman has finished we're going to personally send you an email so don't be shy putting there your questions or comments or anything that you like okay now the bonus that I said before the webinar is here it is the my Excel online Academy we are launching this new excel course it is a membership style Academy and it is one that's gonna advance your Excel level and career now this is a proven system to increase your Excel skills and it is the quickest way to get to the next level so from beginner intermediate and advanced now in this course here everyone that signs up goes through a short online Excel skills survey and based on the feedback then we evaluate it and put you in either the Excel beginner intermediate or advanced course so that is the best way for you to learn the content and then move on to the next Excel skill level because there is no point throwing you thousands of video tutorials and saying okay start over here no this is managed in a way that you start in a course that's based on your skill level and then you slowly finish that and then you move on to the next level and then you learn more and then you move on to the next level and each level that you move up you get a certificate of completion now these are self course covers everything within Excel he covers formulas it covers pivot tables analysis charts power query power pivot macros and VBA now when you complete one section we'll give you an assessment quiz which you can retake as many times as you like until you pass when you pass that you receive a sticker completion for that level and then you move on to the next level each month we're also gonna give you brand new excel video tutorials and resources so you can keep on learning the new Excel features and new excel tips so whatever is in the course today you're gonna get that is your core content every month we're gonna be adding new video tutorials because that is the best way for you to be engaged into learning more excel features and finally we're here to support you Carl and I and all the other teachers and support this back here to support you so we have giving you access to our exhaust forum so you can search for questions and answers there we have a live quarterly killing our webinar and also we have a dedicated mi support from the instructors and also inside the course you can post your questions come we're there to answer them so we are there to support you so you can gradually learn Excel an increase to the next excel level now the Mike's online Academy just a quick recap of what you're gonna get you're gonna get a monthly or annual online access you're getting yourself video tutorials delivered in your course player each month you get three in depth exact courses covering formulas pivot tables analysis charts power query power pivot macros that is over 350 episode video tutorials you can gain access to today you also get downloadable Excel workbooks so you're going to follow along and practice your skills you also get a certificate completion which you can put onto your CV or resume as well as your LinkedIn profile so that's great so you can put on your LinkedIn profile and other people colleagues or prospective employers can have a look at your skill set you also get a 30 day money back guarantee so you can view this course without any risk and the six a limited time bonuses for attending the webinar is what you're gonna get today this is Valeria over one thousand nine hundred dollars and you get it for free when you sign up today you get access to our recorded Excel webinar training so the webinar training that you had today you get access to that plus all the other excel webinar trainings that we've had over the years now you're also going to get access to our quarterly live killing our webinars that Carl and I will host it's a place where you can send in your questions beforehand we answer them and we give you support to their self problems that you have so you also get dedicated email support in case you get stuck just in an email and we respond within 24 hours or less you also get access to our private members-only facebook group and it's a great place for you to ask questions and also interact with your fellow students and also share your success stories you also get access to our my excel online forum and it's a place where there are questions and answers so people post their questions and we answer them so it's a great resource to find answers to your Excel questions and you can also participate as well and help other students by answering their questions and you also get an exclusive member welcome pack which includes an a4 laminated keyboard shortcuts template which is designed as a keyboard and it's got all the shortcuts he's a great template which I created you get an academy member mousepad you get an academy member pen and also some stickers okay so let's go to the course in here so the Excel beginner course so there is an introduction here and this takes you through the work we interface the menu ribbon features entering data to your worksheet and also saving so this is someone who hasn't used Excel or use a little bit and needs to know how to navigate through Excel and learn the must know ribbon features now we also talked about the formulas and we introduced different formulas like cell referencing the order of operations we explain the relative and absolute references and also show you the comment formulas that you need to get going first in the formatting course there we show you the most use formatting features like adding and deleting columns rows copying and moving worksheets font formatting number formatting and conditional formatting in the charts area with reduce you to the images shapes smarter and various charts to spice up your presentation and printing is important a nice ctrl P press one button but there's a lot more that goes behind it and we show you different ways to allow your page the way you can print your documents and create custom templates but you can reuse time and time again now if you're an Excel beginner you started this course when you finish that you go on to the Excel intermediate course and now the Excel mini course has these five different courses in there first of all is a formulas and in there we talked about the different must know formulas like if vlookup hates lookup index match offset indirect some product test form text formulas the nesting formulas and also named ranges in the pivot table cost we show you how to create a pivot table and also the defriend array of pivot table features that we thin pivot tables that you can use to analyze your data like summarizing values grouping data filtering and sorting slices which is a great feature from Excel 2010 calculated fields capital items pivotcharts plus conditional formatting we also have a section on macros and VBA so this section here talks about an introduction to members of EPA using the macro recorder creating macro buttons and also editing your macros that will save you hours each week the data and analysis course is a great one we show you the analytical tools within himself like sorting and filtering Excel tables data validation drop-down menus these are great text to columns flash field plus much much more and then we go on to the chart I'm a little bit of different charge we have in Excel and different users like the column tab the bar the pie the bubble the scatter and sparkline that's just a field name but we go into more details there and then when you finish that course you go into the Excel advanced course in here we talk about formulas but we're going to more advanced formulas so we'll talk about how you can use vlookup index match offset some product but in a more advanced way in terms of nesting formulas and just different ways that you can analyze your data pivot tables that's a more advanced pivot table course here and we go through how you can create your KPIs for your business interactive Excel dashboards also the pivot table formula is explained plus much much more macros and VBA here we've got in depth into the VBA editor and we show you how you can cut your time in half by the different VBA features and we go through the worksheet and range objects the relative references modules and procedures variables and data types conditional statements and loops message input boxes plus creative interactive data entry user forms we show you how to install and also use power query which is called get a transform in Excel 2016 and onwards power query is a great way where you can consolidate your workbooks and worksheets and create management reports you can also clean up your dirty data and transform your data into easy-to-read reports we jump into power paper which expands on the pivotable knowledge that you learned before now in here we show you how you can import your different data how you can create relationships with the different data sources advanced calculations which are also called measures and also different tax formulas now this is pivot tables on steroids it is a great added and as of now is available in all of the Excel versions and you don't need any special licenses in data and analysis we'll go into more predictive data tool analysis like the goal seek solver data tables scenario manager relationships and forecasting these are great and it's going to save your time and the research you set up when we show you how you can do that and then we'll go with your more advanced charting knowledge and how to form a chart and and create different charts and also the new Excel 2016 charts so the it's go all the way down here and talk about the limited time bonuses it was said before these are all here first number one you get our pro level email support there so we've always had your back so you will never have any issues and any questions you have will always get answered the course bonus number two is our access to recorded webinar trainings on various Excel topics pivot tables formulas and macros course bonus number three is quarterly live Q&A webinars with Carl and myself to give you our extra support and answer any questions that you may have of course bonus number four is access to our private members-only Facebook group and course bonus number five is access to about my Excel online forum and course bonus number six is the exclusive Academy member pack but this is only for students who sign up for the annual subscription now this will physically get mail to Union you're going to receive package in your home and you're gonna love it this is great okay so the price that here is we have a special grandfather pricing then you're gonna lock that price in forever so as long as you are a paying member you're going to have that price if you decide the other track to cancel your membership and then come back a few months later then this price goes away and going to pay the higher price $49 a month or four hundred and ninety dollars a year so this is a great offer we've made it so more people can access it okay so I put up here the resuming of what you're gonna get today all the great benefits all the great bonuses now before I go through wondering again just putting your questions Karl is answering them he can't get to them right now then after the webinar here we'll get back to you by sending you a personal email him or myself so putting your questions there right away now now the webinar replay is also going to be sent out to you as soon as we're finished shortly and that's gonna be emailed to you if you don't receive it send an email to John at Mike's online.com and I will personally send you the webinar replay you also going to receive the Excel workbooks finished that we went through today so you can practice all the tips that we showed you and just to let you know that the webinar replay is only available for the next few days that is going to come down when the Mike's online Academy closes its doors to new student registrations so you only have a few more days to view the webinar and purchase the Mike's online Academy and then after that we are going to close the Academy because we have to support the new students we have to send out and create the new videos that we're going to be sending out month to all the new students and and support them in them getting better at Excel so you only have a few more days to really watch the webinar and access this awesome awesome courses that we have for you today okay so we're getting a few questions here before we do that let us go through what you're going to get by joining today okay so you're gonna get monthly or annual online access depending on which option you choose and then you can cancel at any time you also get new excel video tutorials which will be added each and every month you're a new member you get three in the Excel courses which are the beginners intermediate and advanced courses and each of them cover formulas macros and VBA pivot tables power pivot power query data analysis and charts you also get access to over 315 video tutorials each workbook is downloadable so you can view it in star format and finish format so you can practice you also get a certificate of completion sir each course that you finished whether it's a beginner intermediate and advanced course you get a competitive completion and you can put that on your LinkedIn profile you also have a 30-day 100% money-back guarantee if you don't think that this course is going to elevate your Excel skills then just send us an email we'll refund you the money no questions asked you also get these six limited time bonuses valued at over one thousand nine hundred dollars you get it for free today so bonus number one is dedicated pro level email support bonus number two is access to our Excel webinar trainings bonus number three is the quarterly live Q&A webinars that we're gonna host for you so we can help you to answer any questions that you may have bonus number four is access to our members only Facebook group bonus number five is access to the Mike's online forum and bonus number six is an exclusive Members Only welcome pack that's only if you choose the annual membership so if you choose we're gonna send you out a gift box straight to your home address and you're gonna enjoy that cause it is a great great welcome pack okay now we've got a few questions that are coming through here let me just scroll through a few of them I see Karl is busy answering a few questions and there are some questions about the course we can which I will go through right now so people are asking you know who should take this course if you're a new user if you're a newbie you have me use up cell before if you're a beginner intermediate or even an advanced Excel user this course is for you when you purchase this course the first thing we do is give you an online test and based on that test it gives you a result whether you're beginner intermediate and advanced Excel user and then you start on that course what version of Excel do I need you can use any Excel version it's compatible for Excel for Windows 2007 2010 2013 2016 and also excel for mac 2011 and 2016 when does a course start and finish the course starts when you pay your monthly or annual membership fee and it finishes when you cancel your membership fee it is a self-paced online course and you decide when you want to start the course material and finish it you know you can access the course from your work computer from home computer from your tablet from your smartphone and any other device with an internet connection how long do I have access to the course after enrolling you have access to the course content as long as you're a paid-up member and did not cancel can i download the excel file using the course of course you can each section has its workbook you can download it and keep it on your desktop and save it on your on your desktop forever can i watch the videos on my ipad or tablet yes the course can be active on any tablet or smartphone and it also looks very nice there you can watch the course on your iPad tablet while following along with excel files on your computer screen how do I ask questions to get support now each video has a discussion section so you can use this section to ask questions and see what questions other students have you can also find answers to your questions on their marks on one forum which all members have access to you can also send an email to a dedicated email support if you cannot find your answer and we send a response within 24 hours if not less can my employer pay for enrollment of course I can and they should because it is your professional development you're going to help your company become more efficient so first question you should ask is your boss whether they can pay for it and we accept credit cards for that I received rules of an email so you're so you can submit for reimbursement to your employer now most companies are willing to invest in making an employee's even more awesome at Excel so go ask your boss can I purchase multiple memberships for all my employees of course you can we have a package so if you have three or more people that want to sign up we can give you a further discount so just send an email to support at Mike's online.com and then we'll give you the link to that how often will I be billed okay so for the monthly option when you make your first payment if you make your first payment today you will automatically be built every month so every 30 days from today so for example if you roll on the 15th of the month you will be billed on the 15th of each month and for the annual option when you make the first payment you'll automatically be built every 12 months so if you enroll on the 15th of January 2018 then on the 15th of January 2019 you'll you'll be rebuilt again now you can cancel your membership at any time throughout your membership what is our refund policy now you can get a full refund of our courses within 30 days of course enrollment so that is our 30 day money back and what payments means do we accept we accept all major credit cards Visa MasterCard American Express now the course fees and the US dollars you know paid by bank transfer you can send us an email support at Marcos online.com if you need a tax invoice we can certainly send that out to you as well and we can also complete any paperwork there here your company or government may require so to access the course on the chat box area on the top you have a link to click and then let it go straight to this page here so you can have a look at all the different benefits and bunches you're gonna get you can also read the testimonials you can go to the sample videos and we'll get a few sample videos there that'll take it from inside the course you can have a look at that you can have a look at the curriculum again so the link is there now we're also going to email you the link so you can have a look at that as well and as I said these courses I've been just for the next few days only and then we're going to close it so we can support our students and that means that the webinar replay is going to close down as well so you only got a few days to access and watch the webinar replay okay so we're going to be closing this now any questions that I've been unanswered will personally send you an email so don't worry don't think that you've been left out there's a lot of people in this webinar and it's just we just don't have a lot of time to get an answer each one but that's okay after the webinar will personally email you a response so once again thank you for participating it's been awesome having you here and teaching you excel this is what we are here for is to make you better at Excel because it is a tool that not a lot of people know how to use there are 800 million users and less than 5% know how to use it and if you know one more feature than your colleague then you're seen as an Excel expert and that means that you're gonna get more recognition and if you get more recognition that leads to promotions to more jobs and also a higher salary and we are here to make you more efficient more productive so you can get those promotions that you deserve so we can't wait to see here inside the course it is been great teaching you excel and now we're gonna put on the slide just a few testimonials and if you have any questions send them through support at Mike's online.com and I can't wait to see inside the course you

Channel: MyExcelOnline.com

Views: 134,236

Rating: 4.9078016 out of 5

Keywords: excel tutorial, excel, vlookup in excel, excel formulas and functions, microsoft excel tutorial, excel vlookup, microsoft excel, excel for beginners, index match excel, excel vlookup tutorial, how to use vlookup in excel, if function excel, excel if function, countif excel, hlookup in excel, vlookup excel 2016, formulas, formulas & functions, excel formulas, 10 most used excel formula, vlookup, index, match, sumif, if, formulas in excel, functions, subtract, multiply, office 365

Id: eBkMQxIIkx4

Length: 82min 50sec (4970 seconds)

Published: Wed Apr 03 2019