Excel for Business Analysts Crash Course (XLOOKUP, IF, Forecast Sheets & More!)

subscribe and click the bell icon to turn on notifications we've made the files the instructor uses in this tutorial available for free just click the link below in the video details to get these this tutorial contains extracts from our full excel for business analyst course we hope you enjoy it hello everyone and welcome back to this course on excel for business analysts we're done in section three and in this section we're taking a look at lookups now as i explained in the previous module lookups are essential tool for you to have in your toolkit as a business analyst and you'll probably find that they're one of the functions in excel that you're going to be using most frequently so throughout this entire section i'm going to try and run through a number of different examples of how you can use lookups to extract data from one table and pull it into another and we're going to start out with one of the most popular lookup functions and that is the vlookup function now you'll probably be aware that there is something called vlookup and also something called hlookup and if you're wondering what the difference is all it really means is that vlookup means that your data is running vertically down the page whereas horizontal lookup means that your data is running horizontally i'm going to take a look at an example of hlookup a bit later on in this section but for now we're going to focus our attention on vlookup and specifically vlookup exact match so let's start out by taking a look at the data that we're using so i'm using the workbook called vlookupexact.xlsx and this workbook has two worksheets the one that i'm currently on that's labeled vlookup and then we have another worksheet at the bottom here called catalog and on this page i have three columns of data that's showing me part numbers a description of that part number and then the price of that particular part number so this might be data for somebody who works in a large hardware store now what you'll see here on the catalog page is that we have three headings part number description and price now what i'm aiming to do here if i jump back to the vlookup worksheet is you can see that i have a small table on this particular page which has some part numbers listed but currently the description is empty and so is the unit price so what i'm aiming to do here is utilize vlookup to look in the catalog find the part number and then pull back the relevant description and price and populate these empty fields in my table now the most important thing when you're doing a lookup is that wherever your data is and i'm using data that's contained on a separate worksheet but it might even be that you have it in a completely different workbook the one important thing you need to have is that there is a common field between the two tables because that field will be used as the reference point when performing the lookup so in my case with the data that i have i can see that the part number is that common denominator between these two tables so the part number is essentially the key to all of this it's what i'm going to use as my primary lookup value now i'm going to show you a couple of different ways that you can construct a vlookup that is reasonably simple once you get the hang of it so i'm going to click in this description field and i'm going to type in equals v lookup i'm going to open my bracket now let's take a look at our first argument highlighted underneath in bold it says lookup value now as i mentioned your lookup value is that common denominator between those two tables so for me that is the part number comma to move on to the next argument table array so it's saying okay you want me to look up the part number where do you want me to look up this part number now for me i want to look up this part number in the catalog so what i could do is jump across to catalog and highlight all of the data that i have in this catalog like so i'm going to jump up to my formula bar now to continue this comma it's now asking for a column index number so it's saying okay i'm looking up the part number in the catalog but which column of information do you want me to pull back so i'm looking for the description first of all now one thing you have to realize with vlookup and this is actually one of the limitations of vlookup is that vlookup numbers columns from left to right so essentially column 1 is the part number column 2 is description and column 3 is the price in this case so because vlookup works in that way it means that the lookup value in this case the part number always has to be to the left of the information you want to pull back now that might be okay in some instances but sometimes it's not and later in this section i'm going to show you a more flexible way to perform lookups but for now we're just going to type in column 2 for the description comma and now i need to say if i want to do a true which is an approximate match or a false which is an exact match so what do i want to do here well i'm looking up the part number and i want it to look up the exact number so my last argument here is going to be false so i can type in false like so and close my bracket and that is essentially my vlookup formula let's hit enter and there we go i can see that that has pulled back the description and if i wanted to do a quick visual check what i could do is i'm just going to copy this number i'm going to go back to the catalog i'm going to do a control f to find and i'm going to find that part number which is just there and i can see that yes that is the correct description now i'm going to do this again to pull back the unit price so let's type in vlookup open bracket our lookup value is still our part number comma our table array i'm going to jump back to catalog and i'm going to select all of this table one more time like so so i'm now working up in the formula bar comma now this time i want to pull back the price so my column number that i'm pulling back is going to be column three comma and i still want to do an exact match now remember in excel you can use zero for false and one for true so if i type in zero on the end there for false close my bracket hit enter i'm gonna get the correct price and let's just jump back and do a visual check because it's not very far down there it is just there now in both of those examples you've seen me go into the catalog and highlight everything in this catalog which can get a little bit tedious particularly if you have large amounts of data i'm quite fortunate my data set isn't huge i can do that very simply but if you've got thousands and thousands of rows you don't want to be having to highlight the table every single time so what i tend to do is i tend to name this range of cells so i'm going to click i'm going to do control a to highlight everything and then up in the name box we've seen this before in previous modules i'm going to name this catalog and press enter so now when i construct my vlookup it is a little bit simpler so i'm going to say equals vlookup this time i'm searching for this particular part number comma now instead of having to jump across and highlight that cell range i can now press the f3 key to bring up all of my named ranges and i can just select catalog comma the column that i want to pull back is the description so column two comma and i'm doing an exact match false so i'm going to put zero on the end close my bracket hit enter and there we go now i'm going to do this one more time for good measure because i really do believe with things like vlookup practice makes perfect but i'm going to do it slightly different way so instead of typing into the cell i'm going to utilize the functions dialog box just so you can see how easy it is to use that as well so let's jump up to our little fx button insert function and i'm going to do a quick search for the lookup i'm going to click go there he is double click so this is our functions dialog box and you can see that these are exactly the same arguments that we get when we're typing it into the cell so my lookup value is the part number b6 table array i can press f3 again to bring up my named ranges double-click to select catalog column index while i'm looking up the unit price this time so that is column three and my range look up that's that true or false argument i want to match the part number exactly so i'm going to say false and click on ok and there we go so now what i could do for quickness is i could copy these down like so now one thing you'll notice with this is that for one of these part numbers one that suspiciously says one two three four five it says n a in there which means they can't find this part number in the catalog now you could leave those n a's just there or you could make those a little bit neater and a little bit more meaningful for anybody looking at this spreadsheet so they know exactly what that means that it's not an error it just means that the part number wasn't found it's fairly straightforward to do and it comes under the bracket of error checking which we're going to get onto in the next couple of modules so i'm going to leave those as n a for the time being and we're going to cover that very shortly now the final thing i might want to do here would be to add some formatting let's add some dollar symbols and i'm actually going to change that to currency formatting like so so as you can see vlookup very simple once you get your head around it my biggest takeaway here would be always to name your ranges because it's just going to make your life a lot easier when it comes to constructing your vlookups now in the next module i'm going to be showing you how to do a vlookup with the true argument on the end in every example we've used in this module it's been that false argument so i'm going to show you where and why you would use the true argument on the end of your vlookups so please join me for that hello everyone and welcome back to this course on excel for business analysts we're down in section three where we're looking at all things related to lookups and if you remember in the last module we were taking a look at vlookup and how that works but the examples that i showed you in the previous module we're all using that final false argument in the vlookup formula so what i want to do in this module is just very quickly run through the kind of situation where you might use the true argument on the end of your vlookup formula and i've got a very simple and straightforward example on the screen that is hopefully going to demonstrate this quite nicely to you so you can understand the difference between the two and then know which one to use in any given scenario that might come up in your daily work so what i have on my worksheet is i have a table at the top highlighted in blue that's showing me a salary range so it's showing me the low end of salaries in column b then it's showing me the high end in column c i then have a range description in column d and then finally in column e i have a marginal tax rate for each of those salary brackets and then underneath that i have a little table that lists out some annual salaries and we have the marginal tax rate column is currently empty and that is what we're endeavoring to complete and this is the kind of scenario where you would use the vlookup approximate or true argument in your vlookup formula now the first thing i'm going to do here is i'm going to jump in and i'm just going to name this top table as i told you before i like to name all of my tables or all of my ranges so i'm going to do ctrl a to highlight this table and i'm actually going to format this as a table and i'm going to select just this blue formatting just here it says my table has headers which it does click on ok what i'm then going to do is name this table from the table design ribbon over in this first group so currently it says table 2 which is a bit too generic i'm going to call this tax underscore rate and hit enter so my table is now all named which makes it a lot easier for me to recall that table when i'm putting together my formula so now what i'm going to do is i'm going to start out with a regular vlookup open my bracket now as usual we need to use a lookup value so what i want to look up here is the annual salary so my lookup value is going to be b13 i'm going to say comma it's now asking me for the table array or the table name in this case so remember if you have named your tables i can just start to type it in and underneath it's now coming up with my tax rate table i can double click and you can see that i know i've got the correct table because it's highlighted it with a red border let's say comma it's now asking me for the column index number so so far in this example everything is exactly the same as the last example that we looked at in the previous module now in this case i want to pull back the marginal tax rate so counting our columns from left to right i want to pull back column number four comma now this is where we get the change so in our previous examples we always went for that false argument but in this case we're going to need to use true for an approximate match now why do we need to use that well we're currently looking up the annual salary of 55 000 pounds and if you look at the table above what you'll see is that we don't actually have the exact figure of 55 000 listed in there so if i was to do a false or an exact match what excel is going to do is look for 55 000 and it doesn't actually exist in that top table 55 000 falls within this range just here 35 000 and 85 000. so it's going to be sort of somewhere in the middle here but because we don't have 55 000 listed exactly this is why we would need to use the approximate match so i'm going to say true on the end and close my bracket hit enter and see what we get we get a result of 25 percent and if i look across i can see that yes that is actually the range that 55 000 falls into now of course what i could do here is i could utilize my auto fill handle and fill that down which i will do in a moment but let's just do this one more time but this time using the functions dialog box so i'm going to double click to select my function which happens to be at the top of my list as it was the last one that i used my lookup value is going to be my annual salary b14 table array is going to be tax rate so i'm going to say tax underscore rate my column index number is going to be four and my range lookup is going to be true now remember you can substitute true and false for zeros and ones so false is zero and true is one so i could put one in there if i wanted to click on ok and i get my result of 10 let's do a quick visual check and i can see that yes 8699 is in this bracket just here and the marginal tax is in fact 10 percent so what i'm going to do now is i'm just going to use my autofill handle double click to copy those down and again we can do a very quick visual check 90 000 is going to fall in this one here which is 28 and 450 000 is actually above so it should be 35 which it is so hopefully that makes sense and shows you the difference between that true and that false argument on the end of your vlookup formula that's it for this module in the next module i'm going to show you how you can deal with data that expands when using vlookup so please join me for that hi guys and welcome back to this course on excel for business analysts we're still down in section 3 where we've been looking at lookups and in the previous two modules i showed you two different ways that you can do a vlookup using that false and the true argument what i want to focus on in this module is how you can deal with data that expands so that your v lookups still work correctly now most of the time you'll find your data won't stay static it's quite a rare occurrence to have a data set that never gets updated or added to so if you are a business analyst maybe for retail or some kind of sales figures those sales figures will probably be added to as each month passes and it's the same for this example that we have on the worksheet now so we're going back to that parts catalog that we were looking at previously so i have a whole list of part numbers here their descriptions and their price but this data probably isn't going to stay static it might be that we update our inventory with new parts and we add them into this parts catalog at the bottom and they'll be new part numbers new descriptions and new unit prices so essentially at any point this data could start to grow so it's really important that if we're referring to this data in any kind of vlookup function or anything else for that matter that that new data gets included and it doesn't become too much of a burden on us we don't want to have to go in and start messing around with cell ranges cell references when we add new figures in so i'm going to show you a couple of ways that you can get around that one of them we've already seen but we're going to do a recap and i'm going to show you another example but first of all let's take a look at what happens if we don't accommodate expanding data so what i'm basically going to do here is pretty much what i did before i have my parts catalog i'm going to click in it press ctrl a to highlight everything and i'm going to name this range so i'm going to go up to my name box i'm going to type in parts underscore catalog and hit enter i'm now going to go back to my vlookup worksheet and if you remember this is where we have some part numbers and then we want to pull back the description and the unit price so as we've seen before and this is just a repeat of what we did a couple of modules ago i'm going to say vlookup over my bracket my lookup value is the part number comma my table array well for this i can press f3 which will bring up any ranges that i've named and i can see my parts catalog sitting just there click on ok comma i need the column index number i'm looking for the description which is column two and in this example i want it to exactly match that part number so we're going to do a false on the end close my bracket and hit enter and you can see it nicely pulls back that description and you'll see that if i copy this down i'm going to copy it all the way to the bottom just going to copy it down to there you can see that that pretty pulls back what we were expecting so wherever it finds that part number it's giving me the description where it doesn't find the part number because this one doesn't actually exist in the catalog it's given me an na so now what i'm going to do is i'm going to jump back to the catalog and i'm going to add a new part number onto the end of this list so i'm going to come in here and i'm going to type in 4567 is our part number and just to keep this brief i'm going to say this is door hinges and we'll give that a value of 20 per unit so let's now go back and see if we can pull that through so i'm going to go back to here i'm going to type in part number 4567 and let's see if i copy this vlookup formula down let's see if it works i'm going to use my autofill handle drag down and you can see it doesn't it's not finding it even though i have four five six seven listed just here now why isn't it finding it so if we look at our formula in the formula bar you can see i'm looking up cell b10 which is correct i'm looking up in the parts catalog now the parts catalog is a named range if you ever want to go in and take a look at your named ranges if you jump up to the formulas tab and go into the name manager you can see there there is my named range parts catalog if i click on edit it's going to allow me to go in and i can see where that's referring to so it's telling me catalog worksheet a1 to c85 so let's take a look on the catalog worksheet so you can see that this range stops at c85 because i've added something in outside of that range which is why it's not being picked up over here so in this particular scenario using a named range is not always the best option so what i'm going to do is i'm going to jump into my name manager and i'm actually going to delete out the parts catalog i'm also going to delete out this other one that i have in here like so and we're going to do this a slightly different way now let's remove what we just added once again go back to our vlookup and i'm going to remove all of these errors like so and we're going to try again but we're going to use a slightly different method so let's construct our vlookup open bracket b5 comma now when it comes to the table array instead of utilizing a named range if i know that my data is going to expand i can select the columns instead so what i could do here when i'm selecting table array is jump across to catalog i'm going to scroll all the way up to the top and i'm going to hover my mouse over the column a until i get that little black arrow i'm going to click and drag across and that is essentially selecting columns a b and c i'm going to say comma i'm now working up in the formula bar i want to pull back the description so column two and i'm doing an exact match which is false or zero close my bracket and hit enter and you can see that that's completed it and once again i can drag this down i'm just gonna leave that bottom one for the time being all looks okay so far now for four five six seven if i go back to my catalog go down to the bottom and once again i'm going to add four five six seven door hinges twenty dollars let's go back and let's drag our formula down and see if that works and it does because essentially the vlookup is referring to everything in column a b or c and that stretches all the way down to the end of the workbook so columns is a really good alternative if you're going to have data that expands now the other method that you can use to accommodate expanding data is one that we've done before and that is to utilize tables because tables will expand to accommodate any new information so what i could do here is i could click in my data press ctrl a to select everything go up to the home ribbon and i can either go to formatters table and select one of these options alternatively if i press ctrl t that's going to create a table for me as well out of my data i'm going to make sure my table has headers is selected and click on ok and then the final thing i'm going to do here is name my table so once again on the table design ribbon where it says table 1 i'm going to call this one parts category and hit enter let's go back to our vlookup i'm going to delete out all of this and i'm going to construct my vlookup one more time we're going to look up b5 we're going to select our table which is parts category and you'll see as i type it in it comes up underneath to select we're pulling back column two and we're doing a false or an exact match so zero on the end hit enter and i can then safely copy this down and you can see that it works so let's add something new into our parts catalog so i'm going to go all the way down to the bottom and let's add 8 9 10. we're going to call this door frames and we're going to say 100 now what's happened here is that because this data is in a table as i've added this new row in the table has automatically expanded so the new entry is essentially part of that table so it means that if i now go back to my vlookup and type in a new part number 8 9 10 i can then copy the formula down and it's going to pick it up because it's referencing the table name and the table will always expand to accommodate any new data so those are two different methods for dealing with data that's going to change data that's going to be added to when you're constructing your vlookup formula i hope you found that useful we're going to move on to the next module now where we're going to start talking about hlookup so please join me for that hello everyone and welcome back to this course in excel for business analysts we're still doing in section three where we've been taking a look at how you can utilize lookups to look up information and in the previous modules we've mainly been focusing on the vlookup function what i want to do in this particular module is now switch our focus to hlookup now if you haven't already worked this out the main difference between vlookup and hlookup is that vlookup allows you to look up data that's laid out vertically whereas hlookup allows you to look up data that's laid out horizontally so it really depends on the kind of data you have and the way you have it positioned on the page so really just to illustrate that point we're going to run through a couple of examples of hlookup now i will say fundamentally when you're constructing the h lookup formula it's not a great deal different to vlookup so once you've got your head around one of them you're pretty much good to go with the next one but let's dive in and take a look at a couple of examples now i'm going to start out with an example that you're familiar with it's the one that we've used in the previous couple of modules and that is this example of the parts catalog now i haven't changed anything in here as yet but what you can see is currently the way i have my data arranged it's in a vertical format so i have my column headings listed across the top part number description and price then i have my data in rows running underneath so when i'm trying to complete my table i'm looking up my data vertically what i'm going to show you now is i'm going to take this data and i'm actually going to flip it around so that it runs horizontally and then we're going to utilize hlookup to perform the same lookup now i could have done this before i started this video but i wanted to leave it in because it is just another little extra thing that's really nice to know in excel if you ever need to utilize it and that is how you can quickly flip data around so what i mean by that is instead of having my data running vertically i now want to flip this data so it's running horizontally across the page so what i'm going to do is i'm going to click in my parts catalog and i'm going to press ctrl a to select everything i'm then going to copy my data control c is the shortcut key now what i'm going to do is i'm going to paste this data but i'm going to do a paste special now i could choose to paste my data on the same worksheet but just to keep things a little bit cleaner i'm going to create a new worksheet and i'm going to paste it in here and what i'm going to do is make sure i have cell a1 selected i'm going to jump up to home and i'm going to click the lower half of that paste button and what i'm going to do is instead of using just a regular paste i'm going to jump into paste special and i'm going to select this little option here transpose and what that will allow you to do is paste data that flows in the opposite direction so now you can see i have the parts catalog data but my column headings are no longer running across the top in the columns they're now running down the side in the rows and the data is spread horizontally across the page so if you had data that's kind of laid out like this and you wanted to do a lookup you would need to use hlookup in this scenario so let's do that so let's jump back to our input sheet this is where we're going to be pulling back the description and the unit price and we have our part numbers now this is still running vertically down the page but that doesn't matter because the data i'm looking up is horizontally that's why we need to use hlookup and which way you choose to do this which one of the methods i've shown you in some of the previous modules is entirely up to you when it comes to how you select your data so i'm going to jump back to my data just here and what i'm going to do is i'm going to name this data and i'm going to name it as a range first of all so while it's all selected i'm going to click in my name box just here and i'm going to call this parts 1 and hit enter i'm now going to utilize that range as i construct my hlookup so all i need to do here is say equals hlookup open my bracket and you'll notice that pretty much all of the arguments are the same so it's asking me for my lookup value which is my part number comma it's asking me for the table array well i've just named that range parts 1 so i'm going to select that comma now it's asking me for a row index number if you remember with vlookup it asks you for a column reference number so you do practically the same thing you just count the number of rows so i want the description and that is in row 2. comma and i'm doing an exact match i want to exactly match that part number so my final argument is false hit enter and it pulls back the correct result i can now use my auto fill handle just to copy that down and if you remember one two three four five it's not a number that currently exists in the park catalog which is why we're getting an n a i'm going to do this again for unit price but i'm just going to show you a slightly different way so let's jump back to our horizontal catalog so in the previous module i named the range but if you remember when we were looking at vlookups if new parts were to be added onto the end of this the range doesn't automatically update so what i'm going to do is i'm going to put this into a table so that i don't need to worry about that i can add as many as i like in and i know that my hlookup is still going to work so with my data selected i'm going to convert this to a table ctrl t now this is quite an important step here so it's picked up my data which is absolutely fine it's now saying my table has headers now if i was to keep this box ticked it's basically going to take this first row as having headers so what i want to do is i want to say no my table doesn't have headers click on ok and it's going to give me another row at the top but don't worry about that too much at the moment now what i'm going to do is i'm going to name my table and i'm going to call this parts 2 and hit enter now in the next hookup that i'm going to construct i'm going to be looking for the price so that is going to be row number three so let's jump back to our table and construct our hlookup and this time i'm going to use the insert function dialog box let's do a quick search for hlookup there we go and lookup value is going to be the part number it's now asking me for the table array so i call that table parts 2 my row index number so the row that i'm pulling back is row 3 and my range lookup that true or false argument that's going to be a false and click on ok and you can see once again if i double click it's pulled back those unit prices and of course the advantage of putting it into a table is that i can add data onto the end new data that table will auto expand and my formula is still going to work if you're wondering what to do about this kind of ugly looking top heading row that we have there you can always right click and you can hide that row away if you wanted to now i'm going to show you one more example of hlookup just so you can really get a feel for how it works let's jump across to our hlookup 2 spreadsheet now in this spreadsheet we have a table at the top that's showing sales the amount of commission for each of those sales and also the amount of bonus applied to those sales figures in the table underneath i have a list of salespeople and how many sales in dollars each of those sales people have generated and what i want to do is i want to complete the commission amount the commission amount in dollars and then the bonus as well so what i can do here is use hlookup to pull those figures from the top table and populate this bottom table so let's deal with the commission percentage first of all i'm going to type in equals hlookup my lookup value is going to be that sales figure so b9 comma it's asking me for the table array now i haven't gone in and named the range or created a table what i'm going to do here is i'm just going to select the data so i'm going to just select like so and that's fairly straightforward to do if you have a reasonably small table now one thing you need to be aware of here is if you want to auto fill this h lookup formula going down you want to make sure that you make these references absolute so that when you drag the formula down excel doesn't try and move those references down one cell so i'm just going to click within a2 press the f4 key to make that absolute clicking k4 and press the f4 key to make it absolute once more i'm going to say comma row index number now i'm looking for the commission percentage so i can see that that is row 2 comma and what am i doing here i'm looking for the sales figure and these sales figures don't exactly match the sales figures we have in the top table they might fall between two numbers so i want to do an approximate match which is that true argument on the end and hit enter and what i can do is double click to copy that down and i now get my percentage sales for each of my sales people and if you want to you can do a quick visual check just to make sure that that's all correct now in the next column i don't actually need to use a lookup for this because i'm just doing a straightforward calculation but let's do it it's good practice so i'm going to say equals sales multiplied by commission and hit enter and that's going to tell me i'm going to auto fill that down the amount of commission in dollar value for each of the sales people and then finally i want to see how much bonus each of these people are going to get so i am going to use hlookup again for this so what i'm going to do here is equals hlookup open bracket the sales figure is my lookup value comma so now table array and i'm going to do this a slightly different way i'm going to allow for more data expanding on the end and i'm just going to select all three rows so you can see there rows two to four now again i want to make sure that i lock these so i'm going to put a dollar sign in front of both of them so that when i drag this formula down they don't move comma row index number i'm looking for the bonus which is row 3 and i'm doing an approximate match again so that's that true argument or i can put a 1 instead of true close the bracket hit enter i can double click to auto fill that down and i also might want to apply some dollar formatting to that as well so there we go a couple of examples of how you can utilize hlookup how it's different from vlookup and also a couple of extra tips and tricks in there with regards to using that transpose utility when you're pasting and also some more work on formulas as well hopefully that all makes sense to everyone i will see you in the next module hello everyone and welcome back to this course on excel for business analysts we're still done in section 3 where we've been looking at everything related to looking up information using lookup functions in excel and so far we've focused on vlookup and in the last module i introduced you to its sister function of hlookup we're going to start to move on into some of the other lookup functions available in excel now and we're going to introduce the match function now if you've never used match before it is a lookup function that you'll find on the formulas ribbon underneath the lookup and reference group and if you scroll down to ms you'll see it sitting in there and you can see as i hover over we get that little screen tip and it says match returns the relative position of an item in an array that matches a specified value in a specified order now that's quite hard to visualize and understand so an easier way of thinking of it is match will tell you the position of a particular item within an array or a table or a group of cells and positional information can be particularly useful when you're performing lookups so in this example what i'm going to do is i'm going to take the vlookup that we've already looked at and i'm going to show you how you can automate it slightly make it a little bit more efficient simply by adding in a match function so let's take a look at our first example now on this spreadsheet here again i have a couple of very simple tables in the top table we have a list of employee names and then we have some certain aspects of a yearly review so in this case this is the results of the 360 yearly review for the finance team and we can see all the members of the finance team listed out there it then gives us a score out of 100 based on their review for each of these disciplines so initiative teamwork creativity attitude development tick ability and communication and then what we have underneath is a very small little table which just lists out three of those employees and three of those criteria so it might be that out of the whole finance team i am a manager within the finance team and maybe i look after these three particular employees and i'm particularly interested in the scores they achieved for teamwork communication and initiative so what i essentially want to do here is use a vlookup to pull out of the top table the results that i'm looking for and populate this bottom table but i'm going to use a match function as well to make that a lot more efficient for me so first of all let's build this up let's take a look at what we would do if we were purely using vlookup so what i could do here is i could type in equals and i'm actually going to construct this formula up in the formula bar because sometimes the process of actually typing in the formula into a cell can obscure some of the cells which you want to select so just to keep everything neat we are up in the formula bar i'm going to type in vlookup i'm going to open my bracket and it's asking me for my lookup value so i want to look up the employee name cell a18 comma it's now asking me which table do i want to look up this information well i want to look it up in this table just here and remember because i'm going to copy this down i'm going to make these table references absolute by pressing the f4 key just to put those dollar signs in front of the row and the columns comma it's now asking for column index number so the first one i'm looking up is teamwork so counting from left to right i can see that is one two it's column three comma do i want to do an approximate match or an exact match well in this example i'm looking for the name of the employee ming li in this case so i want to do an exact match so i'm going to put false on the end there and close my bracket hit enter and it pulls back the correct result i can see there for teamwork that mingli did in fact score 59 out of 100 and what i could do here is i could double click to auto fill that down and if i just do a quick visual check courtney lane 58 kirk collings 80. so that all appears to be working now the way that i've done it there is just utilizing vlookup and you can see that that's worked so it's absolutely fine for me to use but remember i have a very small data set just here imagine if you had a much larger data set possibly with thousands of rows and maybe a hundred or so columns sometimes counting across those columns to find the correct number or counting the rows if you're using hlookup can be extremely tedious and time-consuming so a much more efficient way of doing it would be to automate the process of finding that column or row number and what i'm talking about here is if we take a look at this formula again up in the formula bar you can see this number here number three this is the column index number this is where i've counted one two three imagine if i had a hundred rows do i really want to start counting along no i don't so it'd be much more efficient for me to automate using a function the finding of this particular column index number and for that we can use the match function so let me just show you what match does on its own and then we'll combine it with our vlookup so i'm just working in cell f17 i'm going to type in equals match and open my bracket you can see my arguments underneath so it's asking me for the lookup value so i want to automate the finding of the column position so my lookup value here is going to be teamwork so b17 comma lookup array where do i want to look up this word well i'm looking up in this headings row so i'm going to select all of that heading row just there comma do i want to do an exact match less than or greater than well i'm looking up exactly the word teamwork so i'm going to do an exact match which is zero in this case close my bracket and hit enter and it gives me the position in that data in that table of the word teamwork so it's telling me that it's in position number three and if we go back to our vlookup formula you can see that we have number three just there so what i could essentially do is replace this number three in the vlookup formula with my match formula now a simple way of doing that is just to double click to jump into your formula highlight it and press ctrl c to copy the formula i'm going to enter just to come out of there i'm going to go up to my vlookup formula in the formula bar i'm going to highlight where we have the number three and sometimes it's a little bit tricky just to get that number three and i'm going to do control v just to paste in that match formula and hit enter and you'll see there it's giving me the correct answer of 59 but we've automated the finding of the position now what you'll notice is if i try and use this autofill handle to copy this formula down i'm going to get errors in the other two cells and that is because i need to make sure that i also make sure that those cell references in the match formula are absolute so i'm going to jump up to my formula bar clicking b17 and f4 that and i'm gonna also f4 the table array and hit enter and now let's copy down and you can see that i now get those correct results now this time i'm going to do the same thing again but i'm going to name my table instead so i'm going to click at the top ctrl a to select all of that top table and i'm going to name this range review and hit enter so now let's pull out the results for the communication aspect of this review for each of those three employees i'm going to do this up in the formula bar again we're going to say equals vlookup open bracket lookup value is going to be the employee name a18 comma the table array i've named it so i can press the f3 key and that's going to show me all of my named ranges and i can select review from there for my table array comma column index number now if you remember this is the part that we automated so this is where we would add match open bracket lookup value this time is communication and i'm going to f4 that my lookup array i'm looking up the word communication in this particular range so a4 to h4 and i'm going to absolute both of those by pressing f4 and i'm doing an exact match for the word communication so i want a zero on the end just there and i'm going to close off my match formula now that i've closed off that formula it's taken me back to my vlookup formula because i still have one more argument i need to add so if i press comma you can see that i still need to put on the end whether this is an approximate match or an exact match and now because we're back in vlookup we're talking about that employee name do i want to do an exact or an approximate match well i want to do an exact match so my last argument is false or zero and close off my bracket hit enter and it gives me a result of 59. now it's a bit of a coincidence that both teamwork and communication are 59 i did wonder if i'd made an error but if you actually look in the communications column for the result for ming li which i'll highlight just here it actually is 59 for communication as well what i'm going to do now is i'm going to double click to copy that down and you can see i get the results for courtney and kirk and if i do a quick visual check i can see that courtney has got 57 for communication and kirk has 52. let's do that one last time so we can pull out the results for initiative i'm going to jump up to my formula bar equals vlookup open bracket lookup value is cell a18 comma table array well i've named it so i can press f3 and select review comma this is the part we've automated so we want to start off our match function what am i matching i'm matching the word initiative so d17 and we want to make that absolute as we're going to copy this formula down comma where do i want to look up that word i want to look up that word in this top row and i want to make sure that those don't move as well so we're going to lock those bad boys down comma i want to do an exact match for the word initiative and i'm going to close off my match formula i still need to do a comma and add on whether i want to do an approxima of false match for my vlookup and i want to do a false or an exact match and for this i'm going to type in 0 and close my bracket and hit enter i can then safely go in copy this all the way down and those are my results and you can always do a quick visual check just to make sure that those are correct in the initiative column so hopefully that gives you a good idea of how you can utilize both vlookup along with match to make your lookups a little bit more efficient and a little bit more powerful that's it for this module i will see you in the next one hello everyone and welcome back to this course on excel for business analysts we're coming towards the end of section three and in this section we've been looking at everything to do with lookups and we've seen quite a few examples so far which have shown you how you can utilize vlookup hlookup and also the match function in order to perform some really powerful lookups what we're going to do in this module is i'm going to show you an even better way of performing lookups using index match and the new x lookup function now index and match are seen as a good more flexible alternative to using vlookup when performing your lookups so to really understand what those advantages are i'm going to start out by telling you about the main drawback when it comes to using vlookups and if you've been going through some of my examples on vlookup you may have begun to ask yourself the question or notice what that main drawback is so i've quickly switched back to a vlookup spreadsheet now when you're performing a vlookup remember that your columns are essentially numbered and that enables you to pull back the information that you need so for example if i'm looking up the figures 14 work i put my vlookup together and i count my number of columns and you always count from left to right so one two three teamwork is the third column now we've seen how we can automate that process to make it a bit easier using match and you can definitely use vlookup along with match however you will find problems start to occur if you're trying to do a vlookup where the lookup value is to the right of what you're trying to look up so if i was using communication as my lookup value and i wanted to pull back the employee information i wouldn't be able to do that because i can't add minus figures into my vlookup so i couldn't say look for the word communication and then count back one two three four five six seven rows i couldn't say minus seven in order to pull back that employee name so vlookup has a limitation that you can only look from left to right and your lookup value must always be to the left of the information you want to look up now that might be absolutely fine for you and in a lot of the scenarios you come across it will probably be completely fine however there are some occasions where you need to be able to do a lookup where you can literally search anywhere in the table using a lookup value and that is where index and match comes in so let's take a look at our first example so on this worksheet here you'll see that this might be some kind of market research spreadsheet and this is showing me some of the most popular apps available for the iphone it showed me the category that the app falls in it's showing me the name of the app and you can see there we have lots of the popular ones on there facebook whatsapp messenger all those kinds of things the type of app that it is so if it contains in-app purchases if it's a free app or if it's a subscription app we have the revenue for that particular app and the profit for 2019 and then what we have over to the right of that is we have a little table here and you can see in the top set it says select app and currently in cell h4 it has google docs i then have blank cells for category type profit and revenue and i want to populate all of these cells so essentially what i want to do here is i want to look up google docs in this table of data and i want to pull back the category the type the profit and the revenue now the reason why i wouldn't use vlookup for this is because in the case of the category if you look at where the category falls category is in column one and my lookup value is the app which is in column two so if i was doing a vlookup utilizing google docs i wouldn't be able to count forward columns using a vlookup because the thing i'm trying to look up is actually to the left of the lookup value so this is why in this case i'm going to use an index along with a good old favorite match so let me start out by showing you them separately we've already seen match but let's take a look at what index does on its own because quite often you will hear index and match used together in the same sentence because they are so frequently used together but they are in fact completely separate functions and as always you will find them on the formulas ribbon in the look up a reference section you'll see that we have index in there and then a few below we have our match function so separate functions that we're going to utilize together so let's take a look at index first of all i'm going to type in equals index open my bracket now you can see we have quite a few arguments underneath here now you don't necessarily have to use all of them remember any arguments that are in square brackets are optional arguments so what i'm going to focus on here is just selecting the array and the row number so the thing i need to think of here is when i'm doing an index is what am i looking up well i'm looking up the category so the first thing it's asking me for is the array so i'm going to go to my table and i'm going to select the category array and all array really means is the cell range so i'm going to select everything in my category column a5 to a50 comma now it's asking me for a row number so i'm looking up google docs so what i need to do is count down rows until i get to google docs so one two three four five six so my row number is row number six i'm going to close off my bracket and i'm going to hit enter and you can see it pulls back the correct category which is productivity now that's all well and good i've got my results but imagine again if you had a lot more data than i've got here i don't have a particularly large data set so it's quite easy for me to count down if not a little tedious but essentially to make this more efficient we want to do what we did in the last example and automate the finding of this row number by putting a match function in there as well so let's remind ourselves of what match does on its own let's say equals match open bracket i'm matching google docs which is in cell h4 now remember i might decide to change what app i've got typed in there so i want to make sure i use the cell reference comma is now saying okay where do you want me to find the word google docs what is the lookup array well i want you to find the word google docs in the app list so my array is this cell range just here am i doing an exact match yes i want you to exactly match the word google docs so we're going to have a zero in there and close off our bracket and it gives me that row number row number six so essentially what i can do is combine my index and my match together in order to perform this lookup so let's do the whole thing from the beginning i'm going to type in equals index open my bracket what am i looking up here the category so i want to choose the category array i'm going to do a ctrl shift down to make that a little bit quicker comma row number this is where we add our automation so i'm going to say match open bracket my lookup value is whatever is in cell h4 at the moment that is google docs comma where am i looking that up well i'm looking it up in the app list so i'm going to select the top one ctrl shift down to select the entire column comma and i'm doing an exact match so zero on the end and because i've opened two parentheses i want to make sure that i close off both of them and hit enter and there we go i now have my result of productivity which i can see here is correct so now if i was to change the app up here to let's say doom and hit enter you can see that that formula automatically updates so it's now telling me that the category is game and if i do a quick visual check i can see that yes that is correct now if i wanted to make this even more efficient and easier to read i could start naming some of these columns so for example i'm going to name the category the app and the type column so i'm going to click on category ctrl shift down i'm going to go up to my formulas ribbon and i'm going to say create from selection and what this will do is it will create me a named range and it's going to ask me what i want that named range to be called so it's saying create names from values in thee and then it's selected top row for me so if i keep this selection it's going to name this range based on what is in that top row which is category and i'm fine with that so i'm going to click on ok if i click the drop down in the name box i can see that i now have a named range called category so i'm going to do exactly the same for app i'm going to click in the top ctrl shift down to select the entire column create from selection use the top row okay and now i have app and category and let's do that one more time ctrl shift down create from selection create from top row ok so now i have app category and type and i can now utilize these in my index and match just to make the entire formula a lot neater so let's do another one i'm going to say index open my bracket so this time i'm looking up the type and it's asking me for the array so i'm going to use my named range now remember you can press f3 to bring up all of your named ranges in your workbook and my particular named range that i'm going to use is called type comma it's now asking me for the row number this is where our automation comes in match open bracket lookup value is whatever is in h4 and remember if you are going to drag this formula down you're going to want to make that absolute by pressing the f4 key to lock that in place comma where do i want to look up the word doom well i want to look it up in the app list and i have named that named range so i'm going to say f3 and i'm going to say app comma i want to do an exact match so that's zero and close off both of my brackets and hit enter and if i do a quick visual check for doom to make sure that information is correct it is the category game and the type is in app purchases if i was to change this to let's say temple run the top one hit enter that's in fact exactly the same so that is game and in app purchases let's do something slightly different let's say slack hit enter it's telling me it's in the communications category and it is a free app so let's have a quick look there we go communications slack and free so i can see that that formula is working correctly now this is a side by side comparison because we did both of those formulas in a slightly different way on the formulas ribbon in the formula auditing group i'm going to say show formulas and you can see the shortcut key for that is control plus apostrophe so let's click show formulas i'm going to go across to here and you can see both of those formulas next to each other so i think you'll agree that the one that we've done for type is a lot easier to read it's a lot more meaningful and it helps us get rid of all of these kind of messy cell references but the other way does work as well it's entirely up to you which one you would like to use so that's how you can utilize index and match to perform more powerful lookups i'm going to finish this module off by just introducing you to a brand new function now i'm not going to spend too much time on this because this function is only available to people who use microsoft 365 the latest version of excel so if you have an older version so if you're using 2013 or 2016 you're not going to have access to this particular function now if you're not sure which version you're using a quick way of checking if you have access to the function is just to select a cell type in equals x lookup open your bracket and if you get all of the arguments underneath then excel is recognizing that function and you have access to it and you can use it if you get nothing then you more than likely do not have it also another way to check is if you go up to look up and reference and scroll down if you can't see x lookup in there at the bottom of the list then it's likely that you don't have this function yet but if you are using the latest version of microsoft 365 then this is one of the new features and i'm going to demonstrate it because it takes all of the hassle out of doing index and match so we've seen a couple of examples which work perfectly well and if you are used to those and you'd like to use them that's absolutely fine but i just want to show you how simple x lookup is of performing exactly the same thing so we're going to do this again and we're going to look up the profit but we're going to use x lookup open bracket now with x lookup it's asking for the lookup value so my lookup value is whatever is in h4 in this case slack comma it's saying okay where do you want me to look up the word slack well i want you to look it up in the app column now i could select my range but because i've named mine if i press f3 i can choose the app named range comma now it's asking for the return array so very simply it's saying okay once i've found this app in this column what do you want me to return well i want it to return the profit which is this column just here the last column now i haven't named this column so i'm literally just going to select it ctrl shift down and close off my bracket hit enter and it gives me my results so let's have another quick look let's find slack and make sure that that is correct so it's a communications app it's free and the profit is 341 now you may have to apply some formatting to this so i'm going to jump up to home i'm going to give that some dollar formatting like so and if i come in here and change the app to let's say twitter hit enter let's make sure that that all updates correctly it's a social media app it's free and the profit is one four five seven two three two forty and i can see that that is correct so again if we go into show our formulas just so you can see the difference look how much neater and easier x lookup is let's do it one more time for our revenue equals x lookup lookup value is whatever is in h4 i'm looking up the app in the apps list so i can select my cell range or press f3 and select my named range and i want it to return the revenue so i'm going to select the top cell control shift down close up my bracket and hit enter and there i have my revenue let's just make sure we add some formatting to that and do a quick visual check so let's find twitter and i can see that yes all of those values are correct let's do one final switch so we can make sure that this is working i'm going to say candy crush hit enter it's the second one down and i can see that yes it's picking up all of those figures correctly so you've seen a few examples there index and match or if you have the newest version of excel you can utilize that really powerful and really simple x lookup feature that's it for this module in the final module in this section i'm just going to add to this a little bit and show you a little bit of data validation to make these lookups really slick so please join me in the next module for that hello everyone and welcome back to this course on excel for business analysts we've made it all the way down to section four and in this section we're going to be talking about how to make better decisions using logical functions and more specifically in this first module we're going to concentrate on one of the most popular logical functions and that is the if function now if you're looking for the if function on the ribbon you'll find it under the formulas tab in the functions library underneath logical this is where you have a list of all of the functions that belong to that logical functions group now if you're not entirely sure what a logical function is don't worry too much i'm going to explain it from the very beginning so what i have on the screen here is a very basic spreadsheet it is a list of text sales for a technology company called microworld and you can see in column a we have the product that they sell we have the customer we have the date we have the item cost the number of items and the total cost and what you'll see is that the total cost if we look up in that formula bar is basically d7 the item cost multiplied by e7 the number of items that is how we get to our total cost figure and what we want to do here is we need to work out the shipping and the total and in order to do that we're going to use the if function now when it comes to working out the shipping we have some useful information just above so you can see here it says that there is no shipping charge for orders over fifteen hundred dollars but for everything else there is a two percent of the total cost shipping charge now if we just jump in here and type in if and open a bracket you'll see the arguments that we have the first thing that's highlighted is the logical test and then essentially depending on if that logical test is true you get one value if the result of the logical test is false you get another one so taking a look at our example here what would be our logical test well we know that there is no shipping charge for orders over fifteen hundred but there is for anything under fifteen so a logical test is going to be if the total cost f7 is greater than or equal to 1500 and you'll find that with a lot of logical functions you're going to use those operators the greater than less than equal to so on and so forth so that is our test is the total cost greater than or equal to fifteen hundred let's press comma and move on to our next argument so now we want to say what do we want excel to do if that is true so if the total cost is greater than or equal to 1500 what do we want to do well i can see here that there is no shipping charge for orders over 1500 so i'm gonna say i want excel to do nothing comma what do we do if that logical test is false so if the total cost is less than 1500 what do we need excel to do well if it is less than i want excel to work out what two percent of that total cost is and use that as the shipping charge so we're gonna say f7 multiplied by two percent and close our bracket let's hit enter and see what we get so i've got the first number inputted there it's showing me 29 and what i'm actually going to do is just apply some accounting format to this column and i can double click the autofill handle to copy that formula down and what we should see and it's a very nice quick way to be able to do a visual check is that anything that's equal to or greater than 1500 should have no shipping charge so i can see here where we have a dash that that one is over 1500 and i can see as i scroll down that that appears to be true everything else that's under 1500 we've worked out what two percent of the total cost is and we've got that as our shipping fee so just to recap again we have logical test value if true value if false and our final calculation in the totals column is a very straightforward sum calculation so it's simply going to add the total cost f7 to the shipping cost g7 close the bracket and hit enter once again i'm going to apply some accounting formatting to this column and i'm going to double click to copy that down and now we have all of our figures so very quickly we've been able to work out everything we need for this particular data set hopefully that explains how if works a little bit more we're going to move on to some more examples of if so please join me in the next module for that hello everyone and welcome back to this course on excel for business analysts we're down in section 4 where we're taking a look at logical functions and in the previous module we took our first look at how to utilize that if function in this module we're going to do another example of if on slightly different data so in front of me here i have some employee data so this might be for somebody who works in hr and what we have in column a is a list of employee names we have the building that they work in and their department we have the hire date the years that they've been employed their salary and then they've been given a job rating so this job rating might be something that's been generated from a yearly appraisal and each staff member has been ranked from one to five and depending on what they've been ranked determines if they get a bonus or not and we're going to utilize the if function in a couple of different ways to work out who is going to receive a bonus so what we're going to say here is that anybody who has achieved a job rating of five is going to get a bonus of three thousand dollars so i'm going to click in cell h2 and we're going to fire up our if function and if you remember from previously we need to first state our logical test so in this case our logical test is if the job rating g2 is equal to five comma what happens or what do we want excel to do if that is in fact true well if the employee has a job rating of five they're going to get a bonus of three thousand dollars if they have anything that isn't five they're going to get absolutely nothing which is a little bit unfair but there we go close off the bracket hit enter and now if i copy this down by double clicking the autofill handle we'll see that we have 3000 listed wherever there is a job rating of five so it's very easy for us to then see which staff members have received that bonus and once again i'm going to highlight this column and i'm just going to apply some currency formatting so now let's take this on a stage further i'm going to double click to jump back into my if function and what we're going to do here is instead of having just a 0 or a dash we're going to replace that with some text so i'm going to remove the zero and what i'm gonna say is that if the job rating is equal to five i just want it to say in the cell bonus and when you're dealing with text within a formula you must always put it in quote marks comma what do we want it to say if the logical test is false well i want it to say no bonus and hit enter and now if i double click to copy that down you can see we get those results so essentially what i could do if i wanted to quickly analyze this data and just get a list of everybody who's going to receive a bonus i could do that very quickly by combining this with some filters so i'm going to jump up to my data ribbon make sure that i'm clicked somewhere in this heading row and i'm going to click on the filter button this applies these little filters to each of the heading rows and what i can then do is filter just for bonus click on ok and it's going to show me all of the people who are going to receive a bonus now so far in the two examples that i've shown you i've been hard coding values into this particular formula so i hard coded in 3 000 into that if formula which means if the bonus amount changes i'm going to have to go into my formula and i'm going to have to change it what i could do is if i wanted to have my bonus listed somewhere else so maybe i could have bonus just here and then underneath it can say 3000 i'm going to apply some formatting to that and instead of actually hard coding 3000 into the formula i can then utilize cell references so let's do our formula again equals if open bracket our logical test is if the job rating is equal to 5 comma if that's true then they're going to get a bonus of 3000 but we're going to utilize the cell reference instead j4 now remember because we're going to copy this formula down we want to make sure that we fix this cell j4 so excel doesn't try to move down one cell so we're going to do that by pressing our f4 key to add dollar symbols in front of the row and the column comma if they don't have a job rating of five then they're going to get zero and then of course we can copy that down and we get our result of three thousand but the difference being if i want to very quickly update this bonus to 3500 i just need to change it in one place and all of my figures are going to update the final example i'm going to show you in this module is if we want to maybe set the bonus as a percentage of this salary again if we jump into cell h2 and say equals if open bracket our logical test is exactly the same if the job rating is equal to 5 comma if that's true then they're going to get a bonus of 15 of their salary so i want to say salary which is f2 multiplied by 15 percent and as i was saying previously i've hard coded the 15 into the formula but you could very simply have the 15 listed over at the side in a different cell and utilize the cell reference comma if they don't have a job rating of 5 they are going to get nothing once more and we can go in and copy this down and there we can see our figures so a few different ways there that you can utilize the if function in various different formats utilize text utilize cell references etc in order to extract the results that you need in the next module we're going to move on to something called nested if statements so please join me for that hello everyone and welcome back to this course on excel for business analysts we're done in section four where we've been looking at logical functions and in particular the if function one of the most popular functions in excel and something that you really need to wrap your head around if you work in business analysis now in the previous couple of modules we've taken a look at some basic examples of if and how you can utilize it in different ways in this module i want to move on from that idea and just show you how you can construct what we call a nested if statement now when we say a nested if statement what we essentially mean is an if statement or multiple if statements within an if statement now that might sound a little bit confusing at this stage but hopefully this very simple example using the data we used in the previous module will make it all a lot clearer so once again just a reminder of our data we have employee names and we have the building department that they work in we have their hire date we have the years that they've been employed their salary and their job rating and in the previous example we were giving out bonuses for all of the employees that had a job rating of five now it seems a little bit stingy to only give employees that have a job rating of five an annual bonus so what we're going to do is we're going to dish out some more money using nested ifs so i'm going to jump into cell h2 and we're going to start off with our equals if function again now a logical test is going to be the same so if the job rating is equal to five comma and i'm going to start out by hard coding in what the bonus is going to be if that logical test is true and just to keep things consistent we're going to stick with 3000 comma now this is where the nested part comes in because what i want to do now is say if the job rating is four the employee is going to receive fifteen hundred dollars so instead of providing a false argument at this stage i'm going to add in another if and open the bracket i'm going to do exactly the same thing if the job rating is equal to 4 this time then that employee is going to get 1500 comma let's add in another one if open bracket the job rating is equal to three then the employee is going to get nine hundred dollars bonus and we can carry on going so let's do one more comma if the job rating is equal to two then they're going to get five hundred dollars bonus comma and what i'm going to say is that if somebody's got a job rating of one then they're not going to get anything so we're going to say comma 0 on the end now with these longer nested if statements what you need to remember is that for each bracket that you've opened you need to close off as many at the end so i can see that i have one two three four open brackets so i need to close off four one two three four and hit enter now the first one is showing me nothing and that is a job rating of one so that appears to be correct i'm gonna copy this formula down and what i should see is that i have figures filled in for anyone with a job rating of two three four or five and whenever the job rating is one there is no bonus so that appears to be working very nicely using those nested if statements as you can imagine if you have a lot of data these can get pretty long and in the next module i'm going to show you a way that you can kind of shorten this up a little bit but it's really important that you understand this concept of nesting functions inside other functions now of course as we saw previously we could also utilize cell references to construct our nested if statements so what i've done here is i've just added a little table over the side that shows me the ratings one to five and the bonus for each of those ratings so instead of hard coding these numbers into the formula which makes them a little bit more difficult to change i can just reference the cell from this little table instead so let's take a look at how that would look we're just going to say equals if open bracket a logical test is the job rating if that's equal to five then they're going to get a bonus of 3000 which is cell k 8 and remember we need to lock that in place by pressing the f4 key to make it absolute because we're going to copy this down comma if open bracket the job rating is equal to 4 then they're going to get a bonus of 1500 and lock that in place with the f4 key and i could carry on going as i did before but in this case i'm just going to leave it there i'm going to do my final argument for anybody else who hasn't got a job rating of four or five they're going to get nothing in this case and i need to close off two brackets because i've opened two enter and then double click to auto fill that down and of course as we've seen before if these numbers now change everything is going to automatically update so let's change the bonus for a job rating of 4 to 1700 hit enter and all my numbers have updated very simply hopefully those examples have demystified nested if statements a little bit more for you in the next module i'm going to talk to you about the newer if s function which really makes nested if statements a lot more concise and a lot shorter so please join me in the next module for that hello everyone welcome back to this course on excel for business analysts we're down in section 4 where we've been taking a look at how to make better decisions using the if function and in the previous modules we've seen a couple of basic examples of how to utilize if and we've also looked at how to utilize nested ifs that is if statements within if statements and if you remember we had a very small example but when working with nested ifs the formula can become quite long and quite cumbersome imagine if you had a lot more data and a lot more if statements that you need to add in that formula would become extremely long so what we're going to do in this module is we're going to take a look at excel's new alternative to working with nested ifs and that is the if s or the ifs function i like to call it if s to differentiate from regular ifs now one thing to note here is that the new if s function is not available in all versions of excel so if you have excel 2016 2019 for office 365 then you should find that you have access to the if s function if you're not sure which version you're using a very quick way of checking if you have access to the function is just to click in a cell type it in and if you can see it listed underneath then you are good to go if you don't have this function then you will need to use nested ifs as i demonstrated in the last module until you upgrade and have access to this new function so let's take a look at how the ifs function works so i have an example on my screen here and this is just some test results for an english exam and i have these student names i have their score and what i want to determine here is whether they passed failed or if they need to retake and just above i have the threshold the pass mark they need to achieve so essentially if they score above 75 percent they have passed if they score below 75 percent they have failed and what we're going to say is if they have achieved exactly 75 they're going to need to retake the exam so i'm going to use the if s function in order to populate these empty cells in column c with the result so let's jump into our first cell c6 and i'm going to type in equals ifs and open my bracket now the difference with if s as opposed to nested ifs if you look at the arguments underneath we're only required to provide a logical test and a value if true we don't need to provide a value if false and that essentially makes this formula a lot more concise to read and a lot more concise to construct so let's type in our first logical test we're going to say if the score which is in cell b6 is greater than 75 and we're going to lock that cell because we're going to copy this down by pressing the f4 key comma if that is true then the student is going to pass comma you'll see now it moves straight on to logical test two we haven't have to provide what we want excel to do if it's false so let's do the next logical test if the score is less than 75 and we're going to lock that by pressing f4 comma we're going to say fail now for the time being i'm just going to do those two i'm going to close off my bracket i'm going to hit enter and i'm going to double click my auto fill handle to copy that down so we can see here we have anything under 75 is listed as a fail and anything over is listed as a pass but take a look at this score here which is dead on 75. currently that's telling us n a it's giving us an error and that is because we haven't told excel what we want it to do if the score is 75. all we've said is if it's above 75 it's a pass if it's below it's a fail now this is fairly easy to solve what i could do is just jump back into my formula click on the end and provide another logical test so i could say if the score is equal to 75 and lock that in place then i want it to say retake and hit enter and now if i send that down i get exactly what i'm looking for now there is another way that we could deal with this now it's perfectly fine to do it that way and because i have a fairly small data set it's very easy just to add in that third logical test however there is a slightly different way that we could do this i'm just going to remove that last logical test that we added in and instead of actually providing that logical test i'm just going to type the word true comma i'm going to say retake hit enter and if i copy that down i should find that my results don't change so why is this working what is this true argument and what exactly is it doing well the way that this formula works is that excel is looking for the first true condition in order to produce a result so if we take this first score for example 67 essentially excel starts to run through this formula looking for the first true result and then it returns whatever text that corresponds to that so if i just highlight this first logical test and what we can do here is just calculate this formula so we can see the results you can either press the f9 key or on the formulas ribbon you can choose the calculate now option you can see that this first logical test calculates to false for this first score so what excel does it says okay that's a false it moves on to the next calculation so if we highlight the next logical test and do f9 or calculate now it finds true and so returns the result of fail and it basically goes through all of these and does exactly the same process it's looking for the first true when it finds it it will produce the result that relates to it now with this one here where it's dead on 75 let's double click let's highlight and do f9 or calculate now we have a false it moves across to the next one we still have a false and so because we've hard coded true as our third logical test it's going to produce true so this is a nice kind of catch-all option to add into your formula now just to finish off this module on ifs one little thing you might want to do to make these stand out a little bit more is combine this with conditional formatting in order to maybe highlight the results which are showing as fail or maybe highlight the results that are showing as pass so let me very quickly show you how you can do that i'm going to start out by highlighting all of my results i'm going to go up to the home ribbon and across to conditional formatting and i'm going to create a new rule i'm going to select a rule type i'm going to say format only cells that contain and then because my cells contain text i'm going to make sure i say specific text containing and let's say the word pass i want all of those results formatted in a green color click on ok click on ok again and now i can very easily see all of the people that have passed this test you could of course go through and create another rule to highlight the results that are fail or maybe the ones that are retake as well but that's just a nice way of combining conditional formatting with if s in order to analyze your data a little bit more effectively that's it for this module i will see you in the next one hello everyone and welcome back to this course on excel for business analysts we finally made it all the way down to the final module of section four and in this module we've been taking a look at if functions so i want to finish off this section by just rounding it out with how you can utilize if functions along with the count and sum functions now before we jump into countif i'm just going to briefly remind you of what the count function does so we're going back to our spreadsheet where we have our text sales for 2018 and 2019 for the company microworld and you can see that just to the right of our table of data i have count countif and sum if so the first thing i'm going to do is show you what count actually does now the count function in excel again is one of those big five functions it's one of the functions that everybody needs to know and one that you'll probably find you use on a fairly regular basis and what it does if we take a look at the arguments for this function is that we need to provide it with some values and it's going to count how many values we have so for example if each row of this data represents a single sale i might want to find out how many individual sales i have i could use the count function for that and because i'm just counting the number of items i could really utilize any column so i'm going to click in here i'm going to do control shift down to select all of my rows of data and now if you look up in the formula bar if i close off my bracket and hit enter it's going to tell me that i essentially have 909 individual orders in my data so count just counts the number of items so now we understand what count does we're going to move on to count if now this will essentially do the same thing but it allows you to add some criteria into the count for example i might want to count the number of products that are called hb spectre 360. that would essentially be my criteria i might want to count all of the orders we've had for people who work at computec alternatively i might want to count the number of orders i've had where the number of items is greater than 10 and that's exactly what we're going to do so i'm going to jump into my cell and type in count if and open my bracket and you can see i have two arguments that i need to provide and the first one is the range so it really depends what you're counting in this particular example so let's use that last example that i mentioned we're going to count the number of orders where the amount of items ordered is over 10. so my range is going to be the number of items so i'm going to click in my first cell control shift down to select the entire column so we're now working up in the formula bar i'm going to press comma and it's asking me for my criteria now your criteria always needs to go in quotation marks and what is my criteria well i'm only counting the order if the number of items in that order is greater than 10. close my quote marks and close my bracket so i've provided my criteria range and also my criteria hit enter and i can see there that out of all of these orders there are 344 orders where the number of items is greater than 10. so it's a count but it allows you to add in criteria now sum if works in a very similar way but instead of counting we're actually summing so it might be that i want to sum all of the orders where the shipping is greater than twenty dollars or maybe i want to sum all of the items where the cost is less than a hundred and fifty dollars but in this example what we're going to do is we're going to say equals sum if open bracket we're going to sum all of the totals that are greater than or equal to 4 000 so our first argument is the range so we want to select add totals range control shift down to select again we're now working up in that formula bar comma we need to provide our criteria so i'm only summing totals that are greater than or equal to four thousand dollars and we need our quote marks and we're going to say greater than or equal to four thousand close quotes and close brackets hit enter and there we go i now have my total it's summing together all of the items that are greater than four thousand and again i'm just going to apply some accounting format to that and take those decimal places down so pretty simple to use both countif and sum if let's move on to another example now this time we're going to utilize countifs and sumifs in order to achieve our result now countifs and sumifs differ from countif and sumif because it allows us to use multiple pieces of criteria so very similar to when we were looking at that if s function so what we have over here are a few different tables this first table in the top left hand corner is showing us the names of some salespeople the region that they work within the number of orders they've generated and the total amount of sales this time in uk pounds and what i essentially want to do is in this first table i want to count the number of orders that are greater than or equal to 100 for each of the regions so essentially what i have here are two pieces of criteria for my count which means i need to use count ifs so let's type that in and take a look at our arguments so the first argument we have here is criteria range one so what exactly are we looking for well we're counting the number of items in each region so our first criteria range is the region so i'm going to select that entire range now because i'm going to drag this formula down i don't want these cell references to move so i'm going to need to lock that in place by pressing the f4 key to make those references absolute comma it's now saying okay within that range what do you want me to look for and i want it to look for the east region f2 i'm now being asked for my second criteria range so my second piece of criteria is i'm looking for the number of orders so i'm going to select the orders range and again i'm going to lock that in place f4 comma what is my criteria well i'm looking for orders that are greater than or equal to a hundred now i could directly type that into the formula by putting it in quote marks and saying greater than or equal to a hundred i could do it that way in this example i actually have it listed out in the cell so i can just select the cell for my criteria remember that makes it a lot easier to change all of your formulas in one go if this figure changes it's going to update across the board now if i had more pieces of criteria i could carry on going just by doing comma selecting my third criteria range and my third piece of criteria but in this case i only have two so i'm going to close off my bracket hit enter and i'm then going to copy that down and if we do a quick visual check and look at the east region first of all which is these top two i can see that we have zero orders so nobody has made any orders which are greater than or equal to a hundred if i look at the west region there is just one entry for that so we can see that we have one order that's greater than or equal to a hundred the north region both of these match that criteria so my result should be two which it is and the south region that doesn't match the criteria so my answer is zero so we can essentially do a very similar thing if we want to sum these sales figures and for this i'm going to use the sum ifs function so our first argument is the sum range so i want to sum the total sales that meet my two pieces of criteria so my sum range is going to be my sales range and i'm going to press f4 to lock those in place because i intend to copy this down and i don't want them moving comma what is my criteria range number one well my first piece of criteria is the region so my criteria range is the region f4 to lock those in place comma i now need to specify what i'm looking for in that criteria range well i'm looking for the region in this case f12 comma i now move on to my second piece of criteria so i'm looking for all orders that are greater than or equal to a hundred so my criteria range is my orders range f4 to lock those in place comma what is my second piece of criteria so again i could select cell g12 alternatively i could manually type that in greater than or equal to 100 making sure it's in quote marks and then closing off my bracket let's hit enter and let's just send that down and there i have my results so again this is a small data set we can do a very quick visual check if i look at sales for east none of those match my criteria hence why i have nothing in my total we only have one for wes so i would expect to see the same thing 41 107 for the north region again we only have one that meets that criteria and also for the south region as well and of course because of the way we've set this up if i was to change one of these so let's make one of these figures let's make the other north figure above 100 let's say 101 if i hit enter you'll see that that will now update accordingly hopefully that gives you more of an idea of how count countif sumif and then count ifs and some ifs work that's it for this module and that is it for this entire section in the next section we're going to move on to talking about how you can prepare your data for deep analysis so please join me for that hello everyone and welcome back to this course on excel for business analysts we've made it all the way down to section 8 and in this very short section i want to talk to you a little bit about forecasting so forecasting is essentially being able to predict future values based on historical values and trends and it's really useful to be able to create a forecast to give you some idea of how your numbers are going to look in the future so if you can imagine if you are the head of a sales team and you have your monthly sales data being able to predict 6 or 12 months into the future and get a rough idea of what your sales are going to look like can be extremely helpful and excel has a few utilities that are really going to help you when it comes to forecasting accurately now i will say that when it comes to forecasting it's not an exact science forecasting uses your historical data to predict what's going to happen in the future it doesn't necessarily account for any anomalies or things that might happen for example if a global event much like we're having now impacts sales for your company then excel isn't going to be able to predict that accurately so really just think of it as an estimate it gives you a rough idea it's not an exact science now i'm going to start out in this module by showing you a really helpful little utility called forecast sheets or one click forecasting and whilst it's not quite one click it's pretty close and it really does take all the stress out of creating forecasts now the forecast sheets option is one of the newer options in excel so if you have excel 2016 or later then you should have access to this if you want to quickly check if you jump across to the data ribbon in the forecast group it's this button here forecast sheet and you can see as i hover over the screen tip is telling me it creates a new worksheet to predict data trends so let me show you how this works so on the screen here i have a very basic data set i have a set of dates and then i have some sales figures and you can see that these dates are evenly spaced so essentially what i'm doing here is i'm taking a reading on the first of every month of the sales figures so essentially these sales figures are from the previous month and this is pretty important when you're forecasting it's always going to be better if you have your dates evenly spaced if you're taking your reading every day or every week or every month as long as those dates are evenly spaced excel is going to have an easier time helping you with forecasting it's also worth mentioning that with this forecast sheets option you don't necessarily have to have all of your data points in fact if you have a few data points missing excel can still work out a reasonably accurate forecast in fact you can have up to 30 of data points missing and have the forecast still be accurate now looking at this data for these sales figures i can see that there is an increase each month the increase isn't particularly evenly spaced there's no real pattern to it so this data is a good candidate for using the forecast sheets option what i essentially want to do is work out what my forecast is going to be for the next few months so maybe i want to forecast up until the end of the year so all i need to do is select all of my data including the column headings i'm going to go up to my data tab and click forecast sheet and immediately you can see what excel does here it shows me a line chart and i can see my current sales plotted so this blue line is my historical data and then these orange lines coming off here this is the forecast now currently my forecast is ending on november the 1st so i'm actually going to change this forecast end date so that it ends on january the 1st so now i can see how my data has been forecast that's the middle orange line running through there and then what we have is an upper and a lower confidence bound so this is how confident excel is that 95 of all sales figures are gonna fall between this lower and upper confidence bound now if you click the little options to expand that out you do have a few more things you can modify in here so for example you can set the forecast start date and you can see there is that confidence interval so as i said the lower and upper confidence bounds they are 95 confident that all results are going to fall within those bounds you can of course modify that if i start to take that down you can see my confidence bounds become closer together however if i start to move that up and get above 95 percent those lines get further apart now seasonality i have this set to detect automatically i don't particularly have seasonality to my data it's monthly data that's the only seasonality i really have but it might be that for certain job roles or for certain figures there might be an increase in sales at certain times the year so you might want to set your seasonality manually we have our timeline range in our values range those are just the selections i've made on my worksheet i've selected to fill missing points using interpolation so essentially what that means is if i have any missing values in my data excel's going to utilize the data before it and the data after it to create the forecast alternatively you could choose to fill the missing points with zeros now this is a line chart that i'm looking at but if you'd look in the top right hand corner you'd also have the option to create a column chart so again everything in blue are the current sales and everything in orange is the forecasted data and you can see we have these little what i like to call error bars this is showing you your confidence bounds in the column chart now i actually want mine to be a line chart like so and i'm going to click on create and this is pretty much what it does it's very quick and it's very simple so i get my line chart which is showing me my forecast but i also get a table which is showing me the actual figures so now i have my forecast sales i have my lower confidence bound sales and my upper confidence bound sales so essentially with one click i've been able to create a really nice forecast table and a really nice forecast chart so if you are someone who does a lot of forecasts or needs to predict future values and you're using excel 2016 or later definitely worth checking out that forecast sheet option that's it for this module in the next module i'm going to just show you how to do something similar but this time using forecast functions in excel hello everyone and welcome back to this course on excel for business analysts we're down in section 8 and we're talking about forecasting and in the previous module i showed you a very quick and extremely useful way of forecasting or predicting future values using forecast sheets what i want to do in this module is just show you some of the alternatives that you have when it comes to forecasting mainly focusing on functions that we can use to forecast our data as well now i'm not necessarily saying that the functions i'm going to show you now are quicker or easier than using forecast sheets but i think it's always good to know what options you have within excel in case you end up using a version where you don't necessarily have some of the latest functions or the forecast sheets option you have a backup of other functions that can be used to forecast so i'm going to start out by showing you the slope and intercept functions in excel which can help you forecast or predict future values what i have on the screen here is just some very simple data and i've labeled it x and y just to make this exercise a little bit easier to explain now what we have here are some values for x and at the moment they are just regular numbers 1 all the way through to 17. and then i have some values for y now these values aren't sequential they don't have any particular pattern to how they increase but what i want to do is i want to predict what these values are going to be for the x values 13 to 17. now if it helps you visualize this a bit better in your mind you could visualize these x values as dates and these y values as sales figures now when you're doing a forecast or when excel is doing a forecast what it's essentially trying to do is it's trying to work out the straightest line it can find through your data in order to predict the next values so working on the basis that excel is trying to find a straight line what we're going to do in this exercise is i'm going to show you what the formula is for a straight line and we can then use that to predict our values now this is where everything goes a little bit mathsy so i'm apologize greatly for taking you back to your school days but you may or may not remember that the equation for a straight line is y equals mx plus c so in this equation we know what our y values are we've got them listed here we know what our x values are because we have those listed out as well we have two unknowns we have an m value and a c and this is what we're going to use the slope and intercept functions to work out so if underneath i put in m and then c and i'm going to type in equals slope and open my bracket now this slope function is literally trying to work out the slope of the straight line and you can see that my arguments here are fairly straightforward it wants the known y's so i can just select those and i'm going to make those absolute because i'm going to drag this down and then it wants the known x's i can select those and make those absolute close off my bracket hit enter and now i have my m value for my equation i'm going to do the same for c and we're going to use intercept an intercept calculates the point at which a line will intersect the y-axis and again it's asking for the known y's make those absolute and the known x's so these are not complex functions they're very easy when it comes to arguments close my bracket and hit enter so now i have all of the constituent parts of this equation to complete my forecast so what we want to do is we want to do m which is this cell reference and we're going to lock that in place we need to multiply it by x and then we're going to plus c like so and it gives me my predicted value i can then double click to copy that down and i have my forecast in place now i'm not saying that you'll ever do a forecast this way but it's good to understand how forecasting actually works and that you have these alternative functions available to you in excel now that we've done that let's do the same thing but using the new forecast function in excel so we have exactly the same data here but we're just going to utilize the forecast function and you'll see that this is a much easier way of doing things so when i start to type in forecast you'll see i get a few different options now notice that the one at the bottom that is just forecast mine has a little warning triangle next to it and that means it's there as a legacy function it's been replaced by something more current in excel but it's still there for legacy purposes now forecast has been replaced with forecast linear and that is the one i'm going to choose here because i'm again looking for the straightest line possible through my data now the first argument here is x so i need to give it my next x value now i actually want to select cell c16 but you can see that my formula is kind of over the top of that so what i'm going to do is i'm just going to escape out of there and i'm going to construct this up in the formula bar instead so forecast.linear we want our x value which is c16 comma and known wise i'm going to make those absolute comma and unknown x's and hit enter and then we can drag that down and what i'm probably going to want to do here is just do a little bit of formatting so up on that home ribbon and i'm going to take all those decimal places down like so now there are lots of other forecast options and i would say that if you're somebody who is into forecasting or you need to do it a lot it's definitely worth reading up about all of the different options that you have something i find particularly helpful is just to search for forecast and go and get some help on forecast from within excel so you can see here it's going to show you all of the forecast functions that are available and you can run through and you can take a look at what each of those does and work out which one's going to be best for the type of data that you're forecasting but for the time being that is it hopefully in this section you've seen a few different options there if you need to predict future values based on historical data and trends that's it for this module and that's it for this very short section in the next section we're going to be taking a look at additional tools you're going to find really useful in excel if you are a business analyst so please join me for that hello everyone welcome back to this course on excel for business analysts we've made it all the way down to section nine and in this section we're going to be tying up some loose ends and delving into some of the additional functions available in excel if you're a business analyst and in this first module we're going to take a look at conditional formatting now conditional formatting is a great way to really highlight certain values in your spreadsheet and you highlight them based on conditions that you've set now there's lots of different types of conditional formatting that you can apply to your spreadsheets and if you're looking for the button you'll find it on the home ribbon in the styles group you have a conditional formatting drop down just here and you can see all of the different types of conditional formatting you can apply the top two here allow you to specify criteria in order to highlight certain values in your spreadsheet and the ones below the line these bottom three data bars color scales and icon sets are a really visual way of highlighting data in your spreadsheets they help you tell the story in an engaging way and i'm going to show you an example of all of these in this module so we're actually going to apply our conditional formatting to our pivot table so this is the data that we've been working with throughout the pivot table section of this course and you can see the way that i've got this pivot table arranged i have the ship date up in filters i have the region followed by the country in my rows and then i have three columns showing me the total profit in dollars the total costs and also the average profit and what i'm going to do is i'm going to apply conditional formatting to highlight certain values based on criteria that i set so the first thing i'm going to do is i'm going to click in this total profits column i'm going to go up to conditional formatting and let's look at highlight cells rules first of all now what this allows you to do is highlight the cell based on whether it's greater than a number that you specify less than between two numbers or possibly even equal to a specific number so i'm going to say highlight cells that are greater than and then i can specify the value so i'm going to say highlight cells that are greater than 25 million and i can also specify what color i want to use to highlight those cells so we have a few options in here or you can choose your own custom format now for this example i am happy to keep it on light red fill with dark red text and i'm going to click on ok now because i was just clicked on one cell when i applied the conditional formatting in order to get that to copy all the way down this column i need to click this little formatting options box and select the bottom option all cells showing total profit values for country and there we go so now i can see that wherever it finds a value in that particular column that's over 25 million it's highlighted it in red for me so fairly straightforward let's go up to conditional formatting again highlight cells rules and this time i'm going to say highlight all cells that are less than and we'll say 20 million and this time i'm going to do this with a yellow fill click on ok click my formatting options and apply it to all cells showing total profit values for country and there we go and i could carry on going applying conditional formatting to really highlight the information that i want to see now it's worth noting that once you've set up conditional formatting essentially what you've set up is a rule so if i jump back into conditional formatting and go all the way down to manage rules that's going to show me the conditional formatting that i've set up so if i want to delete these out i can simply do it from here i can select the rule and click delete rule similarly if i want to edit a rule and make any changes i have an edit rule button in here which will allow me to modify that click on ok and click on ok again so now let's jump back up to conditional formatting and look at top bottom rules so with this one excel essentially looks at your data and it will work out or give you the top 10 items the top 10 the bottom 10 the bottom 10 percent everything that's above average or below average so if i wanted to see the top 10 countries by the column that i'm clicked in which is the total profit i can say top 10 and i could change the percentage here if i wanted to and i could choose to highlight that with green fill click on ok and once again i'm going to copy that down so now all that's highlighted are the top 10 of items in my data by total profit now you'll notice here that when i'm doing these rules it's just highlighting the cell of the column that i'm clicked in and i always get a lot of questions saying well how do you get it to highlight the whole row so let me show you a quick trick on how you can do that i'm going to first jump in and just manage the rules and delete this piece of conditional formatting and this time what i'm going to do is i'm going to create a rule that says anything that's greater than 25 million in total profits highlight the entire row and not just the cell so let me show you how to do this the first thing i'm going to do is i'm going to highlight all of my data control shift down and then i'm going to create myself a new rule and i'm going to use a formula to determine which cells to format and i'm going to say equals if cell b5 is greater than 25 million then i want you to apply a green format to the entire row now the reason why this will work is because you can see there we've just locked the column the column b so it's only going to look down column b to determine which values are greater than 25 million but because the row isn't locked then the formatting can apply across the entire row so let's click on ok and click away and we should see our results there so now everything that is over 25 million has been highlighted in green let's move on now to looking at some of the more visual conditional formats that you can apply so these three options below the line data bars color scales and icon sets these really add a nice visual element to your spreadsheets so for example if i was to utilize data bars and i have the option of gradient fill or solid film if i just select the top one here and again i'm going to apply that to all it's going to give me a visual representation in the form of a bar as to what the value is in that particular cell in relation to the other cells and if you wanted to if you thought this looked a little bit crowded you could actually choose to display the bar only now you might think to yourself well why would that be useful wouldn't i want to see the value well if you can imagine a scenario maybe you work in hr and you're doing a presentation that shows people's salaries maybe you want to keep those confidential but you want people to have a general idea of where the salary falls against other salaries so in that scenario it's a good idea to remove the value and just have the data bar so all you need to do is jump up to conditional formatting go down to manage rules and edit the rule and you'll see there's an option there in the middle for show bar only click on ok and now we just have that bar the next piece of conditional formatting you might want to apply are color scales so the way that these work is that they look at your data and then they apply a graded cell color so for example this top option just here where it says green yellow red color scale it applies a color gradient to a range of cells the color indicates where each cell value falls within that range now because this one is green yellow red it means the higher the value the closer to green it is the lower the value the closer to red and you have numerous different options in here so let's select this one here red yellow green color scale you also do have two color scales down here if you just want green fading into white but i'm going to do three and again i'm going to apply that to all cells so with this color scale i selected the closer to red the higher the number so if i scroll down i can see that yes i have a red cell just there and that is a high number so it gives you an idea of where that value falls within a range and finally we have icon sets so again these are used as a visual representation of the value in the cell and you have three icon icon sets and also four and five icon icon sets so it really depends how you want to split up this data so for example if i was to select this traffic light system you can see here that there are three icons it's a three icon icon set so the way that this works is excel looks at your data and it essentially splits it into thirds because there are three icons and it assigns an icon depending on where in that range the value falls so if the value in the cell is in the bottom third it's going to be red if it's in the middle third yellow and if it's in the top third it's going to be green so let's apply this traffic light system and there we go you can see how that has applied now again with these if you don't like the way the excel splits up your data into thirds if you want to split it up a slightly different way then once again you can go in and you can edit your rule for your icon sets and you can define how you want these traffic lights to assign so you could say when value is greater than or equal to and then define your own number because currently excel is just splitting it into thirds so conditional formatting is a great way of analyzing data by highlighting the information that's most important to you and with these additional visual conditional formats it makes your spreadsheet a bit more engaging easier and more interesting for people to read and understand that's it for this module in the next module we're going to be taking a look at the indirect function so please join me for that if you're not a subscriber click down below to subscribe so you get notified about similar videos we upload to get the files the instructor used in this tutorial and follow along click over there and click over there to watch more videos on youtube from simon says it

Channel: Simon Sez IT

Views: 32,957

Rating: 4.9820223 out of 5

Keywords: business analyst, excel tutorial, excel vlookup, excel vlookup tutorial, how to become a business analyst, business analyst training, business analyst training for beginners, xlookup, xlookup excel, if excel, if excel function, business analyst tutorial, excel xlookup, xlookup in excel, business analysis, xlookup formula, xlookup vs vlookup, business analyst basics, excel vlookup formula, conditional formatting in excel, index match excel

Id: egmzpMBmH70

Length: 136min 16sec (8176 seconds)

Published: Wed Nov 11 2020