Top 10 Essential Excel Formulas for Analysts in 2024

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