hey do you wish you could automate some of your Excel work well in this video we go over three
real world scenarios where you can use macros to automate your repetitive tasks now first we'll go
over some easy examples just using macros and then we'll move on to some harder stuff using VBA and
don't worry you don't need programming experience for this so let's get into it as the first example
let's hop on to Excel and here you can see that we have a new project idea which we want
to assess and we want to reformat all of these cells over here such that they're in gray with
this blue color and the border around it and by the way you can download this Excel file for free
in the video description so for this you might think of using the format painter which is this
brush over here so just click on that and then we can click on any other cell we want to format
so you can see it changes highlight color and the text color and the Border but the problem is that
it also messes up with the format so instead I'm gonna go back by pressing Ctrl Z and what we're
going to do is record the macro so we're gonna go over to the developer tab for this in case you
don't have this developer tab you just need to go over to file then towards the bottom under options
you're going to find the customized ribbon so that's the one we want to click on
and then you want to take on developer if you haven't already and hit on OK
awesome once we're here we're just going to click on record macro this is basically going to record
our set of steps and so then we can just click a button and it's going to do that same step
for future tasks so let's just click on record macro we'll name it something like formatting
and then for now we're just gonna hit on OK
so the first thing we want is basically to change the color so we're gonna go over to home
and we're going to change the color over here to say a dark dark blue like this one then the
highlight color we want to change it to a gray let's say we go for this one here
and finally we want to add the borders so right next to that we're gonna add all borders
so that's all we need and so we'll go back to the developer tab this time hit the stop recording
awesome so we now have this macro recorded and suppose we want to use it for these two
cells right below and these other ones here so we just press the Ctrl key and just drag them down
now what we're just going to do is click on Macros and you should find the macro we just
did over here and just hit on run great now you'll notice that they have this new format but the text
remained in the original format of percentage here and dollar above next up let's say we've
got a complex financial model like this one over here that we've been working on and because we've
been working with our manager we have quite a lot of comments which are these things in red which
if you hover over them you can see that it's just some commentary from our manager for example now
let's suppose that you've worked on all of these and you now want to remove them but there's quite
a lot of these so what we're gonna have to do is record a macro so we can do it every time
in just one click so again we'll go up to record macro we'll call this one something like comment
hit OK there and then what we're going to want to do is go over to home then all the way to
the side you'll find the find and select option and specifically we're gonna select all of these
nodes which are the comments basically so we'll go to go to special and here you can see that we can
select a ton of different things from blanks but in this case we'll go for notes so we'll just hit
on OK there and here you'll notice that all of them have now been selected so then we can just
go over to one of them and right click and then just hit on delete note that's going to delete
all of them for us in one go and so now we just go back to the developer and hit on stop recording
if I move down to the next tab you'll see that we have the same commentary but I've also added
some more comments over here to the side so we can see if the macro also removes these so
we'll go over to macros and this time we'll select the comment one and just hit on run
and you'll see just like that in one click it's removed all of the comments including these to
the side in case you find the TDS to have to go to developer and click on Macros we can actually
just insert something like a button so we'll go over to insert and it's going to be this one
over here so the button and we're just gonna drag it like so and here we basically want to
link it to the comment one which is the one that removes the comments for us so we'll hit on OK
to rename it I'm just gonna click inside it and call it something like no comments
like so and then now let's suppose I add a new comment here just by going to right click
and say new note let me call this like test so we have this new comment and now let's suppose
I want to remove it so I'm just going to click on this button and you can see that it's gotten
rid of it like so one important thing to note is that you can select whether you want a macro for
one sheet or for all Excel sheets so for example let's say I click on record macro down over here
you can see that it has a few different options one is that it's only for a new workbook for this
current workbook you're working on or for personal macro workbook which basically means that whenever
you open up a new Excel file the macro is always going to be in there so depending on how useful
you think the macro is you could have it in your personal macro workbook so you can use it across
all your Excel files and if you're liking this video and you're looking for more Excel material
you can consider checking out our Excel for business and finance course and what makes
this course different is that it's all applied to the real world while we still cover theoretical
lessons like formatting formulas and charts we also offer case studies that simulate the type
of work you might be assigned in your day-to-day ranging from Financial modeling to cleaning a data
set and presenting some visual insights and if you get stuck along the way you can always ask us
questions in the discussions forum we also offer several other courses including power bi Finance
evaluation and more so if you're interested go to the link in the description below alright back
to the video and finally we have the third and hardest example here you can see that we've got
a new Excel file and let's suppose that every week we receive new data for the five products that we
sell and ideally we need to break this down into five separate sheets so product one product two
and so on on top of that we need to format sheets so each one needs to be called weekly sales we
need to remove the grid lines and a bunch of different things which makes it quite tedious
so to automate this we're going to use a macro but this time it's also going to require a bit of
programming but the steps here at first are going to be the same so we'll head over to the developer
just hit on record macro let's call this one something like product and let me just hit on OK
so these are steps we want to take first we're going to add a new sheet and let's call this
something like product hit enter there and then we'll go over to B2 and just do alt w
v g that's going to remove the grid lines for us and we'll call this something like weekly sales
and let's reformat this by boldening it so Ctrl B and underlining by control U so
let's suppose that we need to do this for each product so product one product two and so on
that's where we're gonna have to go back to the developer tab and first hit on stop recording
and now we're gonna head over inside the code so just click on this Visual Basic here
towards the bottom you'll find the product macro this is basically what we all the steps that we've
taken so far so you can see right now that it says Hey to create a new sheet then it also says to
rename that sheet into product and then it says to remove the grid lines so display grid lines
equals false then it also says to type in weekly sales in this B2 range and to make it bold and
underlined as well so that's basically all of the code that it's written what we need to do is add a
bit more so that we can add all of the five sheets so first at the very beginning here we're going to
hit enter to add a new row and we'll write them X as integer that's what we're going to want we need
this dim here to declare a variable and basically assign that X is going to be an integer where X
is going to be equals to one so it's going to be product 1 2 3 Etc that said we only want
five products so we're going to have to put some kind of a cup and for that we're gonna do do while
X X is less than or equal to 5. then right below you can see here that it says to add a new sheet
which we're happy with this right below it we don't really need so we're just gonna delete that
and then you can see here that it says sheet 2 is going to equal the product
we don't want it to activate the sheet 2 so instead we're going to do active sheet
and then call that product but it's not just product right we want it to be product one
two three and so on so what we're gonna have to do here is add an ampersand and then we're
gonna add the x that said we also want to add a bit of space so so that there's a space between
the product and the number then we'll hit enter because we want that X to move right we don't
want it to stay at only one so what we'll do is go to x equals X plus one that way it's going
to keep going up in increments but up until 5 as we've specified up over here finally we wanted to
take all of these steps so adding the grid lines the title and so on for all of the sheets so we'll
leave that and at the very end we're gonna have to do a loop such that it does it for all five
awesome now that we've done this we just need to close out of it you don't really need to save so
just hit X there and let's try and see if this actually works so we'll go over to macros and
then it's going to be the product one and I'm just gonna hit on run and you can see that it's
generated five product sheets where it goes from one all the way to five they're all formatted
without these grid lines and with the title inside them so it's all looking good there in this case
the example was for five products but it could be for 10 products for all the days in a week and so
on as well finally before we exit Excel we're going to need to save this so just hit on F12
but this time we can't save it as a normal Excel workbook instead it's gonna need to be a macro
enabled one which basically means that it's going to allow for macros to be run and just
hit on Save there for more on Macros check out this video over here or you can take our
Excel course over here hit the like and that subscribe and I'll catch you in the next one