if you work in any of these roles odds are you'll come across some boring and repetitive tasks from
time to time now to help you eliminate those in this video you learn to automate them in a
click of a button like this using macros let's go so here's the scenario we're working with
let's suppose that every week we get a data set like this one which you can actually download
for free in the video description and as you can see we have the week one financials by
country and our goal here is to format it and make it look nice so that we can send it to the
management team suppose by the end of the day now because this task requires the same steps every
week we can actually automate it using a macro so to enable the macros we're just going to go
up over here to any part of the ribbon right click there and click on customize the ribbon
from here you should get a pop-up like this and we want to go down to the developer
area here make sure that's ticked if you haven't already and from there we'll hit OK
so that's going to open up this new tab called the developer up top here so make
sure you click inside of it what we're gonna do is record a macro so let's click on that
from here let's name it something like format hit OK there and this is going to record a set
of steps that we take and so every time you want to run it you can just hit run so first let's
format this so we'll press shift right arrow here all the way to the end and to center it
would use a shortcut alt h m c which is to merge and center over here then let's say we also want
to highlight it in dark blue alt H H for that and I'm just gonna hit on the dark blue over
here then we want to change the font color to something like a white here so we'll go alt h f c
and here on the white color hit enter there Ctrl B to bold in and for the row below just press Ctrl
shift and right arrow and here we're gonna make it in a light blue say so alt H H again and this
time we're just going to select a light blue like so so suppose we're happy with this and on top of
that let's also add a chart down on the bottom so they can see it more visually what that's going to
look like so we'll select all of this area here by going to Ctrl shift down Ctrl shift right
then we're gonna go to the insert Tab and we're gonna insert a chart let's say we go for a combo
chart to see both the revenue and the profit so combo here this is the first one that we
want and let's say we move this down over here we also going to remove that title layer so just hit
delete and there you go now to stop recording that macro we'll go back to the developer tab
and from here we're gonna go to stop recording and there you go now it's recorded that set
of steps let's suppose we're now in week two so go to control page down over here and you
can see all of the unformatted data so now to actually make it work we just have to hit macro
and you can see that under macro we have the macro name the format and we're just going to hit run
and just like that you can see it's fully formatted and we also have the chart right below
now to make this a bit faster instead of having to go through the developer tab and hitting
macro we can actually just add a button on the side so on insert here you can see that we have
a ton of different options but we want to go with that first one which is simply a button
so we're just gonna put it say over here like so and let's make sure that we link it to the format
so every time we click that button it's gonna do the format macro that we recorded earlier
we'll hit OK there and let's rename this something like macro actually let's rename it format there
just like so and now let me just delete this chart so we can see if it's gonna work again so I'm just
gonna click on format and now we only need to press that button and it's going to do the exact
same action as you can see here and even though you can automate tasks on Excel there's more
powerful tools like power bi which allows you to create awesome visuals in a fraction of the time
to learn about it I recommend checking out Financial Edge which provides certified
online courses and is also sponsoring this video their data visualization and power bi
course goes from the basics of data cleaning and progressively moves to harder topics like creating
power bi charts customizing visuals and finally creating some awesome dashboards in power bi
and if you're interested in other areas of business or Finance they've also got courses
on investment banking private Equity trading and more so if you're interested in checking
them out go to the link in the description below where you can get 25 off using Code
kanji 25 all right back to the video on top of that what if we want to highlight the country
we're sending things to so for instance if we're sending this to the Canada country manager then
we would like Canada to be emphasized for that we can also create another macro so just click
on any random cell here we're gonna go to record macro and let's call this something like highlight
hit OK there and from here let's do the set of steps to highlight on this cell in particular so
let's say I go alt H and we're going to color it say um in a light gray Also let's add some borders
to it so alt h b and then from here D is going to be the shortcut so now you can see that we have
the top and the bottom border and let's also bold them by pressing Ctrl B so once we've done all of
this we'll go back to the developer tab and hit stop recording and again let's make this a button
as well so we'll put it right over here and let's say we call this one something like highlight
let's make sure we link it to the Highlight there hit OK and we're gonna call it highlight two
great so from here let's suppose I want to highlight the Canada region so I'm just going
to select it so Ctrl shift right and all I need to do is click on highlight and you can see how it's
been able to emphasize that and it doesn't have to be for all the rows it can just be safe for
the country itself I can just click on highlight and you can see how it's been highlighted there as
well so now whenever you send this new file to each respective manager you just need to
select a particular area and hit highlight for now we've only looked up buttons to make the
macros work but they also work with something like a shape or an image so here I've got the
logo of my company let me just right click on it and you can see here that it says to assign
a macro so I click on that and let's say I want it to highlight hit okay there and so every time
I have a cell selected let's say I have this one here selected and I click on my logo you
can see that that's kind of formatted like so one scenario that we haven't considered is what
happens when the data set is not of equal size so in this case you can see it goes all the way
to row 11 but if you go control page down you can see that we've added three more rows over
here so we want to find out if the macro is still going to work so if it's dynamic in other words
now to do so let's go to the developer tab as we don't actually have the button here anymore
click on Macros there and we're going to want the format one first so we're just going to hit
on run and you can see that if we look at the chart over here it's actually accounting for
Portugal Belgium and Austria now if you're curious to see why that I why that is why it's dynamic in
this scenario we can just go click inside macros here and then for the format one which is the
one that we just used we're gonna click on edit that's going to show us the steps that we took
click on that and bear with me I know this looks a bit daunting this is basically a VBA which is
the programming tool within Excel so you can see that here we have all of the steps that we
actually took so on the horizontal alignment and the vertical alignment here you can see that it's
saying like Ctrl shift bottom here so Ctrl shift down and now the reason why it's been working for
us is because it's seen as something Dynamic here if we actually had a range like say I don't know
B2 to B10 then it wouldn't quite work because it would be limited to that but because we used a
shortcut it's able to detect that all the way down to the bottom dynamically let's get out of that by
pressing X there and one final thing to note about Excel files when they have macros is that you
can't save it as a regular file instead we're just gonna go to save as so press the F F12 key there
and here towards the bottom when it says save as type we don't want a regular Excel workbook but
we want the next thing which is the Excel macro enabled workbook click on that and
then just hit save from there for more on Excel check out this video over here going over Excel
shortcuts hit the like and that subscribe button and I'll catch you in the next one