Hi everyone, my name is Kevin. Today I want to show you how you can write a simple macro in
Microsoft Excel, and as full disclosure before we jump into this, I work at Microsoft. So,
first off, why would you possibly want to write a macro? Why would you want to do that? Well,
let's jump on the desktop and I'll show you a quick example of when macros might be helpful.
Here I am on my desktop, and I have Microsoft Excel. I have the latest version of Microsoft
Excel. This is what comes with Office 365. If you have 2019, 2016, 2013, or any previous version,
macros have been around for a little while, so you should be able to follow along, but
hey, you should get the latest version. A lot of improvements have come over time.
Here I'm going to click on Microsoft Excel, and why would you want to do macros? Well, let's say
that every single month my manager gives me this spreadsheet that has customer names in one column,
and then the balance due in the other, and let's say that my manager wants me to
format this data in a specific way, so I'm going to click on this other tab, and this shows how
my manager wants to get the data back, so you see that I had to split the first name from the last
name into two separate columns, and then I have another column here that has balance due, and what
I've done is for any customer who has a balance due, I formatted the balance in a specific way.
So, you'll see, you know, here's my customer data, and then here's where I need to land, and so, what
I could do is I could just manually do this every single month where I just go split the columns,
I apply the formatting, and it's lots of manual effort to do that, and it'll take me some time,
but what we could do is instead of me manually doing it, I could just outsource that work to
the computer and I could have the computer do it for me. So, that's what you do with macros.
You could take those repetitive tasks that you do again and again and again, and instead, you could
have a macro do it for you. So, sound good. Well, how do we get a macro to do this? Well, the first
thing that we want to do is we're going to want to record or create a macro, but what you could do is
you could jump through all these different pivots on the top of Excel and you won't find anything
related to macros, so what we need to do is first we need to turn on the pivot that will have a view
that lets you add a macro, and how do we do that? Well, the first thing that we're going to do is
click on the file menu, so that'll open up file and then we're going to go to the very bottom of
the file menu to options. OK, so let's click on options, and I know this is the last place anyone
ever wants to go. You just land in this view with lots of little checkboxes and lots of different
controls. You probably have no idea what half of them do, but anyway, once we're in options here,
what we're going to do is we're going to scroll down, and then we're going to click on customize
ribbon, so let's click on that, and what you'll see is on the right-hand side, these are all the
different pivots that you see on the ribbon. The macro-option lives under developers, so we want
to check that box, and then we're going to click on OK. What you'll see happened is now we have
a new pivot called developer, and so I'm going to click on that and there's an option now, so within
here you have something that says macros, so I could click on that. That'll show me all macros
that currently exist as part of this workbook. I don't have any yet because I haven't created any.
What I could do is I could record a macro and then I have a number of other options,
so we're going to walk through a few of these as we go on, but the first thing that we want to do
is we want to learn how to record a macro, so I'm going to record a macro that does all
the formatting here to get it to look like this formatted sheet. So, what we're going to do is
I'm going to first click on record macro, and I'm going to call it format customer, customer data.
One of the things that you'll see is for the macro name you can't have any spaces, so you either have
to just connect all the words or you could use underscores. Kind of however you want to handle
that. I'm just going to use underscores here. OK, so click OK and now what you'll see is the
macro is recording and so I have this button that I could click on to stop recording once I'm done,
but I'm not done yet because I haven't done anything, so the first thing that we're going to
do is I'm going to insert another column here and I'm going to call this one first name, and then
we're going to call this column last name, and then what I want to do is I'm going to highlight
all these names and what I'm going to do is go to the data view and we're going to click on text to
columns and I'm going to say delimited, next, and we're going to separate these by a space, so I'll
click on next and finish and there you go. All my first names are now separated from my last name
and then for the balance due, I'm going to highlight these. I'm going to click on home.
We're going to go to conditional formatting and I'm going to highlight any cell that has
a value greater than 0. So, we're going to say greater than and I'm going to say zero. And so,
what you see is now this highlighted any single cell that had a balance due, and so now one thing
you'll see is, so I've matched the formatting, so it looks like that other sheet that I have.
I'm going to go back to the developer and now I'm going to click on stop recording.
So, what I'm going to do now is I'm just going to undo all the changes that I just made,
so we're going to go back and I'm also just going to copy the customer data over to another
sheet just so I have a copy of it, and so what you'll see now is I'm going to go click on macros
and so you see, I have this existing macro here called format customer data,
and now I could run it, and when I run it, it does all the formatting for me and so let's say
next month when I get a different set of data, I could go ahead and just run that
same macro again and that'll apply all the formatting to my sheets. It's pretty easy.
Now what I can also do is there's an option called use relative reference. What that means is,
let's say that I had my customer data sitting over here instead of in cell A1. Well, right now if I
run the macro, it's not going to do anything because it's not going to format this data
because it's in a different position. If instead I say use relative reference, it'll
simply use the starting point as the position where it should start running the macro. So,
let's say I recorded that macro and I use relative references. If I have the active cell here,
it would simply apply all the formatting in this location, so that's what I that's what it would.
I'm going to get rid of the formatting for just a moment, and I'm going to bring the data back in,
and I want to show something else that you can also do, which is kind of fun.
So, one way to run your macros, you could click on macros and then you could run it from here.
What you can also do is if I click on insert, what I can do is I could insert a button,
so we're going to insert a button, and I'm going to when I click on this button, I'm going to run
this macro. The format customer data, so I'll click OK, and now I can title this button. Maybe
I maybe I for the button I change the text to say format data. So, now what will happen is
check out this. Now when I click on the button, it'll simply format all the data for me. So,
kind of cool. You could use a button, or you could click into macros. Either way you want to do that.
You can either use a button or simply run it from there, and that way you could apply your macro.
One of the things you could also do is if you click on this visual basic view,
what we're going to do is I'll click into that and then here you can see the macro
that I created. So, this is the actual code behind the macro that you're running, and you
could kind of work your way through it. Let's say instead of saying first name you meant to say,
first name of customer and maybe you want to say the other one, so you could go ahead, and
you can modify some of the details of the macro, how it runs. Or let's say that you wanted to,
you know, select more cells instead of going through C11, you really want it to go to C20.
Or maybe C100. So, let's say your data always ranges between 50 and 70, maybe you want to modify
that, so you just always capture however much data you have and then you could go ahead, and you can.
That'll just automatically be saved then. I'm going to close that, go back to the sheet and
what you need to do then is if you want to save this sheet, you can't just save it as a normal
Excel spreadsheet because it has a macro in it. So, when you go to save as, what you'll see is
within the save as list, you have your typical just Excel workbook and then you also have
something called an Excel macro enabled workbook. If you want the macro to be attached with your
workbook, you're going to want to save it as this type, and then you could go ahead and save,
and so this is how you create simple macros in Microsoft Excel. It really simplifies work,
especially if you're doing those repetitive tasks again and again and again. There's nothing more
draining than having to do a task many, many, many times, let's say every week, every month,
and instead what you could do is, why don't you offload that work to a computer? Computers
are great at doing that. You know, predictable, same routine tasks again and again and again and
all you have to do is you just have to record what the computer should do,
and the computer will do it. For you now, if you're at work, you could tell your
manager that you came up with this awesome macro and you want to take on more work,
or you can just kick your legs back up, tell your manager how hard it is, and then have the
macro do the work for you and get a little bit of free time. Whichever approach you prefer.
Well, that's all I had. I wanted to give you a quick tutorial of how you could create macros
in Microsoft Excel. If this was helpful and you were able to create a macro, please give
this video a thumbs up. You want to see more videos like this, hit that subscribe button,
and if there are any other videos or any other topics that you'd like to learn about,
feel free to leave a comment down below, and I'll take a look at it, and add it to my list of
videos to create in the future. That's all I had. Hope you enjoyed it. I'll see you next time, bye.