In this tutorial, Kevin Stratvert walks you through the process of creating macros in Microsoft Excel to automate repetitive tasks. He begins by explaining the utility of macros in formatting data as required consistently. Kevin demonstrates how to enable the Developer tab in Excel, record a macro to automate formatting tasks, and highlight balances due through conditional formatting. Furthermore, he introduces how to use relative references for macros and the option to run macros using a button. Kevin concludes by explaining how to edit macros using Visual Basic and the correct method to save macro-enabled workbooks.
Highlights
Enabled the Developer tab in Excel to access macros. 🛠️
Recorded a macro to format customer data efficiently. 📊
Used conditional formatting to highlight balances due. 🔍
Introduced macro execution through a button for convenience. 🚀
Edited macro scripts in Visual Basic to customize actions. 👨💻
Key Takeaways
Automating repetitive Excel tasks with macros can save you a lot of time and effort. ⏳
Excel macros involve recording actions, so they are repeated automatically. 🎥
You can modify macro details like names and ranges in the Visual Basic Editor. 📝
Overview
Kevin Stratvert starts by explaining the importance of macros in Excel, particularly for automating recurring data formatting tasks. He describes a typical scenario where a manager requires data to be formatted in a specific way, and performing these tasks manually every month can become tedious and time-consuming.
He then walks through the initial steps of enabling the Developer tab in Excel to access macro features. Kevin guides us through the process of recording a macro, which involves executing the desired formatting once to capture the steps. This includes splitting customer names into two columns and using conditional formatting to highlight outstanding balances.
Finally, Kevin demonstrates more advanced macro features such as using relative references and editing the macro code in Visual Basic for further customization. He explains how to save an Excel workbook as a macro-enabled file to retain these automations, illustrating the powerful potential of macros to simplify Excel tasks and boost productivity.
Chapters
00:00 - 00:30: Introduction to Macros in Excel The chapter introduces macros in Microsoft Excel, presented by Kevin, an employee at Microsoft. It starts with a discussion on the reasons for writing macros and proceeds with a demonstration on the desktop using the latest version of Microsoft Excel available with Office 365.
00:30 - 02:00: Setting Up the Excel Environment for Macros Macros have been a feature for some time, and it's recommended to use the latest version of Excel due to numerous improvements over time. In this chapter, the user is guided on starting with Microsoft Excel specifically for working with macros.
03:20 - 07:00: Creating a Macro to Format Data The chapter discusses creating a macro to streamline the process of formatting data. It addresses the need to split first names from last names into separate columns and format the balance due for customers. The speaker considers the manual method of performing these tasks monthly, which is time-consuming and labor-intensive, leading to the decision to automate this process with a macro.
05:00 - 09:00: Running and Testing the Macro In this chapter, the concept of using a macro to automate repetitive computer tasks is introduced. Instead of manually performing these tasks over and over, one can employ a macro to execute them automatically. The chapter begins with a discussion on the purpose and benefits of macros, subsequently guiding the reader on how to record or create a macro for such tasks. This process streamlines workflow by outsourcing repetitive actions to the computer, allowing for efficiency and time savings.
09:00 - 11:30: Advanced Macro Features The chapter titled 'Advanced Macro Features' delves into Excel's macro functionalities, specifically focusing on enabling the macro view that isn't available by default in standard Excel tabs like PivotTable tabs. To access this feature, you must navigate to the 'File' menu, scroll to the bottom to select 'Options', which leads you to a detailed settings page with various checkboxes and options to customize your Excel experience, including enabling macros.
11:30 - 14:30: Saving and Sharing Macro-Enabled Workbooks In this chapter, the narrator guides the user through configuring the Excel ribbon to enable macro functionality. The process begins by accessing the options menu and selecting 'Customize Ribbon'. Here, the user needs to find and check the 'Developer' option, which might be unfamiliar due to the numerous controls available. Once enabled, the 'Developer' tab appears on the ribbon, allowing access to macros. The chapter illustrates how to navigate to this point, concluding with instructions on viewing available macros now accessible from the 'Developer' tab.
14:30 - 16:00: Conclusion and Additional Resources The chapter titled 'Conclusion and Additional Resources' focuses on recording macros within a workbook. The speaker starts by acknowledging the absence of any existing macros in their current workbook. They express an intention to record a macro to automate formatting tasks, specifically for customer data. The process begins with a walkthrough on how to record a macro, which includes clicking on 'record macro' and naming it 'format customer data.' As the chapter progresses, additional options related to macro recording are discussed, illustrating practical usage of macros in workbook management. The chapter serves as a guide for users to enhance their productivity through macro automation.
How to Create Macros in Excel Tutorial Transcription
00:00 - 00:30 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,
00:30 - 01:00 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
01:00 - 01:30 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,
01:30 - 02:00 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
02:00 - 02:30 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
02:30 - 03:00 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
03:00 - 03:30 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.
03:30 - 04:00 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
04:00 - 04:30 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.
04:30 - 05:00 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
05:00 - 05:30 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,
05:30 - 06:00 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.
06:00 - 06:30 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
06:30 - 07:00 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
07:00 - 07:30 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.
07:30 - 08:00 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
08:00 - 08:30 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
08:30 - 09:00 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,
09:00 - 09:30 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.