Top 10 Essential Excel Formulas for Analysts in 2023

while there is hundreds of formulas on Excel if  you work in any of these roles you really only   need to know about 10 formulas to cover most  of your tasks so let's take a look at those coming in at number 10 we've got the if error  formula and here's the scenario for it you can   download this Excel file for free in the video  description so we want to just calculate the   profit margin percentage which is just going to  be equals to the revenue divided by the gross   profit and we'll hit enter there now we just  want to drag this across so we're just going   to go shift and then hit the right arrow and  hit Ctrl r but you'll notice over here that   we started to get this error sign that's  basically because we're dividing by zero   and you can't really submit something like this  as it just looks wrong so what we want to do here   is use the if error formula to go around that  so up front over here we're gonna go if error   hit the top key there and so this is a value if  there is no error and in the case that there is   an error what we want to do is just add quotations  so just like that which is telling Excel not to   put anything basically we'll close those brackets  and hit enter and now again we're gonna drag this   along and Ctrl r and now it's all Dynamic so  if I add a number here you can see that that's   going to automatically update as well next up in  number nine we've got string functions and these   make sense to extract a portion of a sales  value that might not make too much sense so   let's take a look at an example to show you you  can see over here that under location we've got   the abbreviation of the state and next to the  code so what we want to do here is just take   the first part because we only really want the  state name so we're going to go to equals left   this formula is going to allow us to select a  certain set of characters coming in from the left   so we're gonna select the location comma and we  want two characters and just hit enter there you   can see more or less how that works then we're  just gonna go shift down arrow and Ctrl D to   drag it all the way down as you can imagine you  can go ahead and modify this to something like   five characters or whatever you want similarly you  can use the right function with the mid function   which work in the same way in number eight we've  got joining text and for this we can simply use   the ampersand so if we take a look at this table  over here you can see that we've got the breakdown   by City And Country what we want to do here  is merge them together so join them for this   you could use what's known as the concatenate  function which is this one over here or there   is a faster way to go about it which is just  using the Ampere signs so we're gonna go equals   we're gonna select London Ampersand and then in  quotations here what we're gonna do is add a comma   and a space so there's a bit of separation  between the city name and the country name   close those quotations Ampersand again and this  time we're gonna link it to the country and hit   enter now you can see what that would look like  next up we have the sequence formula which allows   you to add a sequence of numbers so over here  let's suppose that we're running a few tests and   on the whole we need to run 50 tests so we're just  going to label them by going to equals sequence   hit the top key there and we just want to have  50 rows close those brackets and hit enter now   you can see that we go from one all the way to  50. similarly though we could make it a bit more   sophisticated so over here let's say that these  tests are going to start on the 1st of January   2023 they're gonna go on every week so every  seven days we can also specify that using the   sequence formula so we'll go equals sequence hit  the top key there for the rows we want the same   50 rows comma The Columns is if we want something  to move horizontally which we don't so we'll put   just a comma there and for the start we want to  specify the date so we'll hit the date formula   hit the top key there and for the year we set 2023  comma the month we said zero one comma and the day   at zero one we'll close those brackets comma and  the step here is the frequency in this case we   said once a week so it's every seven days so we're  just gonna put a seven there close those brackets   and hit enter now you can see what that looks like  where we have the step up for every seven days   awesome and speaking of dates another very useful  formula is the e-date formula so let's take a   look over here you can see that we've got the  income statement and we basically want to add   the months all the way here so the first one  let's suppose that this is January 23 so John   2023 and from here we want to go to increments of  one month for this we can use the e-date formula this is the start date comma and we want to add  one further month to that close those brackets   and hit enter once we've got the February one we  can go to shift right arrow and go to control R   now you can see how that looks in this case I went  plus one month but you can also go decreasing by   going to minus one two as we're getting all of  these results using formulas The Next Step would   be to visualize them and a good way to do that  is using chart templates like the ones HubSpot   the sponsor of this video is kindly providing  us using the link in the description you can   get multiple Excel chart templates completely for  free in the download you'll find the Excel file   with instructions on using the template alongside  all of the chart types you might need to visualize   your data from here you can easily modify your  inputs and the charts will automatically change   these templates can have either one column  of data or multiple depending on your needs   I personally find these charts useful for deciding  which graph showcases my data best as it's quite   uncommon to find templates that actually have nine  different charts that you can see at the same time   so if you want to check these out you can go  to a link in the description below to download   these completely free templates from HubSpot to  level up your Excel game all right back to the   formulas next up in number five we've got the  large and the small function so over here you   can see that we've got all of these brands that  we're working with and here's the different deal   sizes in amounts we want to find out what are  the five largest deal sizes that we've got so   you can think of just using the max function but  that's really only going to give us the first one   and not all of these other ones so for a top  five the best way is using the large function   so we'll go equals large hit the top key and  the array to us is all of this area over here   comma K is basically the rank that we're  interested in so it's number one all the   way to number five down here and we'll just hit  enter there and what's nice here is that it's   fully Dynamic so I can go ahead and change this  to a 10 and that's gonna automatically change   for me and as you can imagine the small function  works in the same way now we've got the sum ifs   formula which allows you to sum a set of values if  a certain condition is true so we'll go over here   and you can see that we've got all these company  names alongside their dates and whatever sales   and commissions they've got so up over here under  month seven we basically want to find out sales   amount so the condition here is that if it's in  month seven we want to find the sum of those sales   so we'll go equals some IFS hit the top key  there and the sum range to us is all of the sales   control shift down comma the criteria range is all  of the months so all of these over here Ctrl shift   down comma and the criteria itself is the month so  this cell over here that you can't quite see right   now and we'll just hit enter there and that works  quite well for us so let's take it up a level down   below here you'll notice that the name Amazon is  actually replicated many times but with different   endings you can see that it's Amazon Inc Amazon  UK EU Etc but for our purposes we would want them   all together as they're all Amazon for this we're  gonna use the some ifs formula again well with a   small variation so we'll go equals some ifs again  hit the top key the sum range again is going to   be the same Ctrl shift down comma criteria range  is going to be all of the companies so over here   Ctrl shift down comma and this is where it gets  a bit different we're gonna select the name like   we did before so for Amazon in this case but  we said that the ending after Amazon sometimes   seems to differ so we're gonna go ahead and add  an ampersand then in quotations we're gonna add   this asterisk over here close the quotations  and then close the brackets and hit enter   this variation basically tells Excel that hey I  want you to find anything that starts with Amazon   and it doesn't matter if there's something after  that as well moving to number three and over here   we've got the filter function so you can see that  we've got all of the sales figures by country and   we've got specific targets which are the 500 000  over here so in this table to the side we want to   find out which countries have passed the 500 000  mark for this we'll use the filter function so   equals filter hit the top key there the array  is all of this range that we're interested in   comma and now for the inclusion this is  where we're gonna add this condition which   basically says that hey if these  sales over here are greater than   the 500 000 then we want them to be included as  that means that they actually did hit the mark   close those brackets and hit enter you can see  how that filter works for us similarly we could go   ahead and change this to say 250 000 and you can  see that it's gonna add more countries dynamically   number two we've got the lookup functions more  specifically the X lookup and for this you'll find   here that we've got the sales person alongside  how much they made in commission how much they   generated and so on what we want to find out is  more specifically how much this Steven person made   in commission so we're gonna go to equals x lookup  hit the top key there the lookup value is we're   looking for Steven right comma Loca parade we're  looking for him in this whole sales person range   so we're gonna go Ctrl shift down comma and the  returnery so we're interested in his commission   so we're gonna select all of these and Ctrl shift  down we'll hit enter there and you can see what   Steven's commission is looking like and I can also  go ahead and change the names here to say Manuel   Lee and that's going to dynamically  update to this figure over here   and finally in number one we've got the index  match so let's look at an example as you can   see over here we've got the sales breakdown by  country and by month specifically we want to find   out the sales in the country of Austria in the  month of April so we'll go to equals the index   match is going to be a combination of two formulas  the index and the match so we'll go index first   and the array here is we're just going to select  this area with all of the numbers so Ctrl shift   down Ctrl shift right comma and the row number  this is where we'll add our first match so we'll   go match hit the top key there we want to match it  by country first so we'll select the Austria there   comma and we're gonna go for this lookup  area which is gonna be all of the countries   comma and we want to have an exact match it's  important to specify that we'll close those   brackets and hit the comma key now we need to work  on the second criteria which is the month so we're   gonna add a second match here under the column  part the lookup value is going to be the month   and I know you can't see it right now but I'm  basically down over here comma the lookup array   is going to be all of this month ranges that we've  got here comma and again we want an exact match   we'll close those brackets close the brackets  again this time for the index and hit enter there   now you can see what that's looking like if we  take a look at Austria in the month of April that   seems to be correct and what's nice again is that  it's fully Dynamic so I can change this to June   and the figures are going to change as well let me  know in the comments what other Excel formulas you   think are important to learn Financial formulas  you can check out this video over here or this   link over here to take our Excel course hit a like  And subscribe and I'll catch you in the next one

Channel: Kenji Explains

Views: 231,558

Keywords: excel formulas for analysts, excel formulas for business, excel formulas for finance, excel functions, excel functions for analysts, excel functions in 2023, excel formulas in 2023, excel for 2023, excel for data analysts, the only excel formulas you need, top 10 excel formulas, 10 essential excel formulas, must know excel formulas, improve excel with these formulas, excel functions for business analysts, excel functions for consultants, excel functions for finance

Id: MeBYShlqsak

Length: 13min 39sec (819 seconds)

Published: Sun Feb 19 2023