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