Learn Excel MACROS in 10 Minutes Using Real World Examples
Learn Excel MACROS in 10 Minutes Using Real World Examples
Learn how to use Excel Macros to automate any repetitive task on Excel.
📈 Get 25% OFF all Financial Edge Courses Using Code KENJI25: https://bit.ly/3CcV6ba
🆓 DOWNLOAD Free Excel file for this video: https://view.flodesk.com/pages/633fe6…
In this video you’ll learn how to record a macro, add a macro button, and edit the macro code in VBA in less than 10 minutes.
Using macros, you’ll be able to save hundreds of hours by automating any repetitive or boring task in your day to day job. To make the tutorial realistic, we’ll simulate a real-life scenario where we need to format a dataset and create a chart every week. As this is a repetitive taks, you can record a macro so you only have to click one button to format what would otherwise take 5 or more minutes.
Finally, you’ll learn to save the Excel file as a Macro enabled workbook so the Macros remain active.
LEARN:
📊 Get 25% OFF all Financial Edge Courses Using Code KENJI25: https://bit.ly/3e697iK
SOCIALS:
📸 Instagram - https://www.instagram.com/careerprinc…
🤳 TikTok - https://www.tiktok.com/@career_princi…
🧑💻 LinkedIn - https://www.linkedin.com/company/care…
GEAR:
📹 My Favorite Books \u0026 Gear: https://kit.co/kenjiexplains
▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬
Chapters:
0:00 - Record a Macro
3:17 - Adding a Macro Button
5:06 - Highlight any area with Macros
7:07 - Edit Macro using VBA code
8:35 - How to save Excel files with Macros
Disclaimer: I may receive a small commission on some of the links provided at no extra cost to you.
Content
0 -> if you work in any of these roles odds are you'll
come across some boring and repetitive tasks from
6.18 -> time to time now to help you eliminate those
in this video you learn to automate them in a
11.82 -> click of a button like this using macros let's
go so here's the scenario we're working with
18.06 -> let's suppose that every week we get a data set
like this one which you can actually download
22.32 -> for free in the video description and as you
can see we have the week one financials by
27.06 -> country and our goal here is to format it and
make it look nice so that we can send it to the
32.52 -> management team suppose by the end of the day now
because this task requires the same steps every
39 -> week we can actually automate it using a macro
so to enable the macros we're just going to go
44.58 -> up over here to any part of the ribbon right
click there and click on customize the ribbon
50.88 -> from here you should get a pop-up like this
and we want to go down to the developer
55.38 -> area here make sure that's ticked if you
haven't already and from there we'll hit OK
61.38 -> so that's going to open up this new tab
called the developer up top here so make
65.82 -> sure you click inside of it what we're gonna
do is record a macro so let's click on that
72.42 -> from here let's name it something like format
hit OK there and this is going to record a set
79.14 -> of steps that we take and so every time you want
to run it you can just hit run so first let's
84.54 -> format this so we'll press shift right arrow
here all the way to the end and to center it
90.18 -> would use a shortcut alt h m c which is to merge
and center over here then let's say we also want
97.26 -> to highlight it in dark blue alt H H for that
and I'm just gonna hit on the dark blue over
103.74 -> here then we want to change the font color to
something like a white here so we'll go alt h f c
111.9 -> and here on the white color hit enter there Ctrl
B to bold in and for the row below just press Ctrl
119.4 -> shift and right arrow and here we're gonna make
it in a light blue say so alt H H again and this
126.48 -> time we're just going to select a light blue like
so so suppose we're happy with this and on top of
131.88 -> that let's also add a chart down on the bottom so
they can see it more visually what that's going to
137.04 -> look like so we'll select all of this area here
by going to Ctrl shift down Ctrl shift right
144.12 -> then we're gonna go to the insert Tab and we're
gonna insert a chart let's say we go for a combo
150.3 -> chart to see both the revenue and the profit
so combo here this is the first one that we
157.02 -> want and let's say we move this down over here we
also going to remove that title layer so just hit
162.78 -> delete and there you go now to stop recording
that macro we'll go back to the developer tab
169.68 -> and from here we're gonna go to stop recording
and there you go now it's recorded that set
175.32 -> of steps let's suppose we're now in week two
so go to control page down over here and you
180.6 -> can see all of the unformatted data so now to
actually make it work we just have to hit macro
187.08 -> and you can see that under macro we have the macro
name the format and we're just going to hit run
192.72 -> and just like that you can see it's fully
formatted and we also have the chart right below
197.76 -> now to make this a bit faster instead of having
to go through the developer tab and hitting
202.14 -> macro we can actually just add a button on the
side so on insert here you can see that we have
208.26 -> a ton of different options but we want to go
with that first one which is simply a button
213.12 -> so we're just gonna put it say over here like so
and let's make sure that we link it to the format
219.48 -> so every time we click that button it's gonna
do the format macro that we recorded earlier
224.88 -> we'll hit OK there and let's rename this something
like macro actually let's rename it format there
233.22 -> 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
239.1 -> gonna click on format and now we only need to
press that button and it's going to do the exact
243.72 -> same action as you can see here and even though
you can automate tasks on Excel there's more
249.36 -> powerful tools like power bi which allows you to
create awesome visuals in a fraction of the time
256.08 -> to learn about it I recommend checking out
Financial Edge which provides certified
261.24 -> online courses and is also sponsoring this
video their data visualization and power bi
267.72 -> course goes from the basics of data cleaning and
progressively moves to harder topics like creating
274.38 -> power bi charts customizing visuals and finally
creating some awesome dashboards in power bi
282.9 -> and if you're interested in other areas of
business or Finance they've also got courses
288.3 -> on investment banking private Equity trading
and more so if you're interested in checking
295.2 -> them out go to the link in the description
below where you can get 25 off using Code
301.14 -> kanji 25 all right back to the video on top of
that what if we want to highlight the country
307.92 -> we're sending things to so for instance if we're
sending this to the Canada country manager then
313.86 -> we would like Canada to be emphasized for that
we can also create another macro so just click
319.62 -> on any random cell here we're gonna go to record
macro and let's call this something like highlight
326.76 -> hit OK there and from here let's do the set of
steps to highlight on this cell in particular so
333.3 -> 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
340.26 -> 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
346.5 -> the top and the bottom border and let's also bold
them by pressing Ctrl B so once we've done all of
352.74 -> this we'll go back to the developer tab and hit
stop recording and again let's make this a button
359.16 -> as well so we'll put it right over here and let's
say we call this one something like highlight
364.26 -> let's make sure we link it to the Highlight there
hit OK and we're gonna call it highlight two
373.2 -> great so from here let's suppose I want to
highlight the Canada region so I'm just going
378.18 -> to select it so Ctrl shift right and all I need to
do is click on highlight and you can see how it's
383.82 -> been able to emphasize that and it doesn't have
to be for all the rows it can just be safe for
388.56 -> the country itself I can just click on highlight
and you can see how it's been highlighted there as
393.42 -> well so now whenever you send this new file
to each respective manager you just need to
398.4 -> select a particular area and hit highlight for
now we've only looked up buttons to make the
403.74 -> macros work but they also work with something
like a shape or an image so here I've got the
408.78 -> logo of my company let me just right click on
it and you can see here that it says to assign
413.46 -> a macro so I click on that and let's say I want
it to highlight hit okay there and so every time
419.4 -> I have a cell selected let's say I have this
one here selected and I click on my logo you
424.92 -> can see that that's kind of formatted like so
one scenario that we haven't considered is what
430.02 -> happens when the data set is not of equal size
so in this case you can see it goes all the way
435.06 -> to row 11 but if you go control page down you
can see that we've added three more rows over
440.04 -> here so we want to find out if the macro is still
going to work so if it's dynamic in other words
445.5 -> now to do so let's go to the developer tab as
we don't actually have the button here anymore
451.56 -> click on Macros there and we're going to want
the format one first so we're just going to hit
456.06 -> on run and you can see that if we look at the
chart over here it's actually accounting for
460.8 -> Portugal Belgium and Austria now if you're curious
to see why that I why that is why it's dynamic in
466.98 -> this scenario we can just go click inside macros
here and then for the format one which is the
472.98 -> one that we just used we're gonna click on edit
that's going to show us the steps that we took
478.08 -> click on that and bear with me I know this looks
a bit daunting this is basically a VBA which is
483.72 -> the programming tool within Excel so you can
see that here we have all of the steps that we
488.88 -> actually took so on the horizontal alignment and
the vertical alignment here you can see that it's
493.8 -> saying like Ctrl shift bottom here so Ctrl shift
down and now the reason why it's been working for
499.26 -> us is because it's seen as something Dynamic here
if we actually had a range like say I don't know
504.54 -> B2 to B10 then it wouldn't quite work because it
would be limited to that but because we used a
509.58 -> shortcut it's able to detect that all the way down
to the bottom dynamically let's get out of that by
515.58 -> pressing X there and one final thing to note about
Excel files when they have macros is that you
521.46 -> can't save it as a regular file instead we're just
gonna go to save as so press the F F12 key there
529.68 -> and here towards the bottom when it says save as
type we don't want a regular Excel workbook but
535.68 -> we want the next thing which is the Excel
macro enabled workbook click on that and
541.38 -> then just hit save from there for more on Excel
check out this video over here going over Excel
547.26 -> shortcuts hit the like and that subscribe
button and I'll catch you in the next one
Source: https://www.youtube.com/watch?v=IZY0SpSpYTM