Say Goodbye to IF Statements in Excel with SWITCH

hi I'm Jamie and welcome to teachers Tech today  I want to show you how the switch function can   outperform if statements in Microsoft [Music]  Excel if you'd like to follow along with today's   lesson I have the practice sheet Down Below in  the description where you can click on it and   download it and work through it as I show you I'm  kind of curious how many of you have heard of the   switch function in Microsoft Excel before and  how many have used it and what are the results   when you're using it let me know Down Below in  the comments so what we're going to do first of   all is I'm going to show you the F statement that  I've already created in this one and I'm going to   create the same type of functionality with the  switch statement down below so if you uh take   a look at the formula bar up here you can see I  have the uh if statements it's nested ifs in it it   goes through and finds if this number matches to  whatever it's going to give back uh the word like   average here so three equals average in this case  I copied it down good is for and so on so it works   well it copies down but now we're going to create  the switch uh show you the switch function in here   and I just want to show you why it's a little bit  more streamlined and a little bit more efficient   to use especially if you're adding to it so let's  go ahead and start this let's go ahead and start   the switch function right here in D12 I'm just  going to place my equal sign and start typing   switch and I have it right here so we can take  a look that what it does evaluates an expression   against a list of values and Returns the result  corresponding to the first matching value if   there's no match an optional default value is  returned and I'll place that at the end so let's   go ahead and select that switch and I'm going to  start with selecting the cell that I'm going to   be looking at and it's going to be this one right  here so that's my reference place my comma in and   all I have to do now is give it the first uh what  it's going to be looking for so the number one   comma and if it is number one what do I want it  to be well it's just going to say four and then I   can just Place Another comma put my two comma and  then Place what's the two going to be equal to so   this is going to be below average like so and we  can continue on so let me go ahead and fill this out and so notice when I got to the end not  rated there's no number that that's uh compared   to there so it's just going to be if it's not  1 2 3 4 5 live that's what's going to have the   option now notice when I do this it just has the  one bracket at the end one bracket at the very   beginning not like the nested if that had had  to close every one of those opening brackets so   if I was going to make this longer I can just  quickly add another comma in here and continue   on with my list let's go ahead and hit enter and  copy this down and you're going to see it gives   me the same results but the advantage of this is  going in into this and formatting it if I needed   to add more when I compare it to this you can see  this is a little bit longer and there's just a few   more things you have to adjust to make changes to  let me give you another example now before I move   over to the example two I just want to show you  that the switch function did work if it wasn't   listed it took the alternative not rate it there  now at the bottom move over to example two and   we're going to do a operation so multiplication  using the switch function so I'm going to have   this right here so here's total sales and we  have regions but the commission is different   for each region so I'm going to be multiplying  this by a certain number based on it switching   to uh north south east west central or other now  I'm going to place my uh switch function in here   so I'm going to go ahead and get started but the  first thing I'm going to do is just select this   cell and start my multiplication so this is  going to be be multiplied and now I can start   my switch here and I'm going to choose this  and start filling it out so it's going to be   based on D2 because it's going to look up what  region it is and then so what I put my comma in   and then it's going to be saying North make  sure that we have everything spelled exactly   the same and North is going to be a certain  commission so I'm going to say it's going to be 0.05 and then we can place our comma and then  go ahead and type our next one like so so we're   going to keep filling this out just like we did  before so I'm going to go quickly finish this up so now I have it all finished up I closed it  up I have north south east west with the different   commission rates for each of them notice that I  don't have Central in there because it's going   to be the other one uh the only other option and  I'm just going to put zero there so that's what   it should get give back so let's hit our reenter  and copy this down and so Central shouldn't be   anything if I copy it down and you can see that  it is at zero so at each of these as I copied it   down it went through it switched it to which one  I needed so this one went over to the South took   the 6 multiplied it by the 15,000 and we got it  there so again another efficient way to use this   switch function in Microsoft Excel let's move  over to another example for the last one so in   this example I started with an if statement and  then I'm going to put a nested switch inside of   it let me explain what I want to have happened  here so we're going to look at this column right   here look at difficulty level and it needs to  assign a number to medium easy heart different   numbers and then it needs to look at urgency and  assign a number to high low medium and it needs   to add these two together to get a final number  but at the same time if any of these are blank   I need it to just return a priority score of  zero so I'm going to click in where my formula   is and take a look at it so you can see I  started with an if here and then I had an   or statement and I Ed the function is blank so  it took a look at C2 and said if this is blank   then it needs to uh Return To Zero I'll show  you that that will work but it also needs it   to look at D2 and do the same thing return  zero now this is where the switch comes in   so switch it's going to take a look at C2 and  we're going to just before like before assign   those numbers to what it says so you can see  easy is one medium is two and hard is three   I've added an operation I need to add the two  so I'm adding another switch to do this column   right here and doing the same low zero medium  one and so on so those two are going to get   added and I have my closed bracket ETS at the  end of just the two brackets so when this is   all said and done I can copy this down and it  works for each of them and like I said if one   of these are blank if I go ahead and delete it  it will turn the priority score to a zero using   the function so this is where it's coming in at  the beginning with the if or is blank and here's   the zero returns so I hope you like these  different ways to use this switch function   in Microsoft optic cell uh it can be a lot more  efficient than just using the if statement and   using nested ifs give it a try let me know what  you think if this comes in handy for you thanks   for watching this time on teachers Tech I'll see  you next time with more Tech tips and tutorials

Channel: Teacher's Tech

Views: 332,063

Keywords: Excel SWITCH function, Excel IF function, Excel tutorial, Excel formulas, SWITCH vs IF, Excel tips and tricks, Excel skills, Conditional logic Excel, Excel functions, Excel data analysis, Excel for beginners, Excel efficiency, Improve Excel skills, Excel conditional formulas, Excel best practices, Excel productivity, Excel spreadsheet tips, Excel learning, Excel function guide, Excel formula tutorial, microsoft excel, switch function in excel, teacher's tech

Id: 7jF62HvHG3Q

Length: 7min 45sec (465 seconds)

Published: Wed Jan 31 2024