in this video we'll go over eight things not to do in excel let's go first up we've got error values
and nobody likes to see them and you certainly don't want to send your boss an excel file with
them inside so here's a quick fix and you can download this excel file we'll be working with in
the description so over here if we take a closer look you can see that we're calculating the profit
margin which if you press f2 to look inside it is simply gonna be the gross profit divided by the
revenue here now if you move this along you can see that over here we start to get an error sign
that's simply because there's nothing that is being able to divide by the same thing over
here if i change this net revenue to just delete it you'll notice that i get the same error sign
so you want to try to avoid and getting get rid of that essentially to do so first let's press ctrl z
to go back there and then here what we're going to do is press the f2 key and at the very front
we're going to do the if error function press the top key there and this is saying well if there is
no error then we'll just keep it as is so this fraction is fine with us comma and if there is
an error then we're just simply going to put in quotations let's just put n a close the quotations
close the brackets and hit enter there from here let's just drag this across so ctrl shift right
all the way to the end and then press ctrl r great now you can see over here that it starts
to say n a for us i put an a but really you can put anything you want in there it could just be
nothing if you just leave the quotations that's going to be nothing for you ctrl shift right and
then ctrl r there you go next up we have hidden cells and this one's pretty serious in fact it's
so serious that barclays a bank ended up losing money in 2008 as nobody spotted the hidden cells
as they became visible only when printed in a pdf which meant that they ended up buying a lot
more contracts than they had in mind and ended up losing money to show you in excel let's say we've
got this data set over here with the different regions alongside some figures and let's suppose
that we want to hide this whole email region because we're not quite interested in it for now
so to hide it we're just going to select this shift space here and then shift down arrow that's
going to select that area for us right click and from there you can go to hide now the problem with
the hiding is that it's not very clear that something's hidden in here if you just come
into the model for the first time and you see this database then it's not very obvious that
there's something hidden in there unless you're actually looking for it let's press ctrl z to
get out of that now the better way to go about this is through grouping so once you have this
area selected you're just gonna press the shift alt and then you're gonna press the right arrow
and then press the left arrow to remove that so the right arrow there that's gonna group it for us
then you press the minus sign and over here if somebody is new coming into the the excel file
then it's quite obvious that there's this thing over here and they can just press it
and see what's going on there thirdly we've got static text and this one's more of a productivity
hack than it is an error sometimes it's useful to have the text in a dynamic format so if you look
into the excel file over here you can see that we have this netflix investment analysis let's say
we use the same template every time for different stocks that we want to analyze and all we have to
do here if you go under company name is suppose we change this to apple then you'll notice that
everything has changed to apple dynamically so that's something that we want to be able to do
in the future press ctrl z to go back there now if you want to change this all you have to do
is actually link it if you can see let me just go ahead and delete this one and we want to link the
that cell over here and then add shareholders to the end so we'll just go equals then we're
going to link this cell over here we're going to press the amper sign and then in quotations we're
going to put a space first so there's a bit of spacing there and we'll just type shareholders
close those quotations and hit enter now you see it says netflix sharehold holders if i change
this to hbo then all of a sudden all of that is going to change as well so that's basically the
idea here so having text like this especially in templates is going to save you a lot of time
more importantly it's going to minimize the chances of you making an error somewhere
next up in number four we've got hard coding numbers inside a formula and i'm definitely
guilty of this one in the past so over here if you look at the excel file you can see that we've got
an income statement and we have a few assumptions below that that are basically making up the income
statement so if you look under the gross revenue let's say we go on this one over here you can see
that the formula is saying it's going to be the previous year's gross revenue times a one plus
a growth rate now that growth rate is linking over here to the growth the gross revenue growth
but the problem is that this one's actually not dynamic instead you just type the number in here
and this is what is meant by its height coded meaning that it's not linked to anything
and so over here if i go ahead and change this revenue growth to say something like 85
everything is going to change over here however up top nothing is changing and the reason for that is
because it's hard coded so we need to go ahead and change that now luckily this has a bit of
an easy fix we'll just press the f2 key from here let's go ahead and delete this area and we're just
going to link it down over here hit enter there and from here we can just shift ctrl shift right
arrow all the way to the end press ctrl r that's going to drag the whole formula all the way across
if you go over to the very last one press the f2 key you can see that now it's dynamically linking
so if i were to change this this revenue growth to say something like five percent
then you'll notice that everything else is going to change not just down over here but
also on the revenue figures above and if you're watching these tips and tricks and wondering how
you can apply them to the real world financial edge provides certified online finance courses
and they're also sponsoring this video they're the instructors that teach the new hires at the
top four investment banks across the globe so you can get the exact same training online at your own
pace their best-selling investment banker course goes over all the relevant skills to succeed in an
analyst role these include financial accounting financial modeling on excel valuation and more
and if you're interested in other areas of finance they've also got courses on private equity trading
and portfolio management where you can learn all aspects of the investment management industry so
if you're interested in checking them out you can go to the link in the description below and get 25
off using code kenji25 all right so we're past halfway in things not to do in excel and now
we've got merge and center now it's fine if you merge and center something like the title within
a specific excel file but you really don't want to be merging and centering specific rows within the
data set to show you an example over here you can see that you've got all of these different regions
with their story count etc and you'll notice that all of these are merged and centered as
you can see up over here so you can see the europe one the asia one etc now suppose that we've got
all these revenue figures and we want to find out what's the average revenue of all of these
so we'll go equals average press the tab key there we're just gonna go ahead and select all of these
so we'll keep going up but you notice that this starts to open up all the way to the edge to the
edge you can't really take it back anymore now the reason for that is because this whole area is
merged and centered so first let's go ahead and undo that by just clicking on merge and center
once what you can do instead first let's delete this one what you can do instead is select that
area that you're interested in where you want it to be centered by pressing the shift right
from here you're gonna go down to this toggle over here if you can't find it just press the
ctrl 1 key and under align the alignment tab for horizontal we're going to go right into
the center across the selection hit ok there now you'll have the same effect but the only
difference is going to be that it's not its own cell instead it's on multiple cells which
is a lot nicer there so now you can easily go do the average and it's going to take you up
way above that with no problem there next up in number six we've got forgetting to protect
cells and one of the easiest ways to do that is using data validation so let's look into that over
here suppose we have this data set with the high net worth individual list so people that are above
18 million net worth as you can see over here and suppose by mistake we put somebody with a lower
net worth than that like let's say just 500 then obviously that shouldn't be on this list and that
would be an error for us now in order to protect that to prevent that from happening you can do a
few different things let's delete that first we're just going to select this whole area over here
so all the relevant lists for us which is going to be down all the way to the bottom from here we're
going to go under data then you're going to click on this part over here where it has the check sign
from here we're going to go we want to allow only people that have a net worth higher than the 10
10 million there so we're just going to go for a whole number instead of between we want it to be
say greater than or equal to that minimum for us is going to be 10 million and now let's also
put an error earlier so basically when this is wrong when somebody is not inputting that we
should let them know what's going on exactly so let's say as a title we put something like not
not above 10 m and then as the error message you can actually describe it in more detail
for now i'm just going to put hello there just so you can see what it's like hit okay and from
here suppose uh this next person nila it actually has a net worth of five hundred dollars then it's
gonna say not above 10 million and it's going to tell you why here i just put hello for now though
so that's a quick and easy way to protect your data from having the wrong values similar to that
in point seven we've got protecting entire sheets now reason you may want to do this is suppose
you're sending a complex model to somebody else you don't really want them to actually be touching
the formulas and seeing what could be changed there in this scenario suppose we've got an income
statement like this one over here forecasted into the future so quite a complex model in which we
have different assumptions the base the model over here you can see that we've got revenue
assumptions for a best case so a very optimistic scenario a base which would be a just an average
scenario and the worst case if something's really went bad if you go all the way to the top here
under a true scenario you can see that you can actually go ahead and change this from the best
case to the best base case and see how all the figures would change suppose i go to the best case
now you can see that all the revenues seem to be a lot higher and everything else looks a lot better
as well now to be able to only protect this area over here so to make sure that nobody can come in
say put a zero here and end up breaking the model now for this you can go ahead firstly we're going
to have to click on this one go to right click go to format cells because this one we still want as
editable so people can see what it's going to be like based on a base case a best case or a
worst case scenario and click on the locked under the protection area hit okay there and
then down over here we're gonna want to protect everything else so we're just gonna go to review
go to protect sheet hit okay there don't worry about what it says over here
and now what we have is this area that's fully dynamic so we can't change and then everything
down below if i change this to a zero then it's not gonna let me it's gonna give us this pop-up
which is exactly the way we want it so everyone is still able to see what would happen under the
different scenarios but they're not able to switch things up and lastly we've got filtering and i'm
not gonna say not to filter but instead just be careful with it so let me show you an example
if you go over to the very last tab you'll notice that you have all of this data set
over here press ctrl a to select it let's just go to sort and filter under the home tab
from here we're just going to click on filter and let's suppose that we want to get rid of
all of the apac ones because we're not really counting them so we're just gonna go in here
click on that drop down and we're just gonna deselect the apac hit okay there
and now let's say that we want to count all of these revenues over here we're just gonna select
them ctrl c and suppose we need them for something else so we're just going to paste them over here
to the side ctrl v now you'll notice that it's actually not pasting all of them or that's
what it appears like and the problem here lies in these rows that are actually not showing right now
and so the paste has actually gone inside all of the apac region which you currently can't see
if i go and unfilter this so clear clear filters from region you'll notice that it's actually in
there but the problem is that it's copied kind of to the wrong area so it seems like all of these
revenues here are for apac when in fact they're for emia now a quick and dirty way to fix this
is simply to paste them somewhere where the rows aren't hidden so let's go back to the region here
let's deselect the apac hit ok and now let's say i want this data set over here again i want these
revenues for america's and emia only i'll press ctrl c and if i paste them down below it's not
gonna have a problem it's gonna be be able to paste all of them the problem here is with these
ones that you can actually see the rows 8 8 to 13 that's why you get this over here which looks
like an error let me know down in the comments if you made any of these excel errors yourself and
no shame about it i've made many of them myself if you want to learn more about excel formulas check
out this link over here or this other link over here if you want to check out our excel course
hit the like and the subscribe button if you liked it and i'll catch you in the next one