How to Create Macros in Excel Tutorial

hi everyone my name is Kevin today I want to show you how you can write a simple macro in Microsoft Excel and his 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 20 19 20 16 2013 or any previous version Macker 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 doing the other and let's say that my manager wants me to format this data in a specific way so I'm gonna 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've 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 a 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 sounds sound good well how do we get a macro to do this well the first thing that we want to do is we're gonna 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 macros so what we need to do is first we need to turn on the pivot that'll have a view that lets you add a macro and how do we do that well the first thing that we're gonna do is click on the file menu so that'll open up file and then we're gonna go to the very bottom of the file menu two 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 probably have no idea what half of them do but anyway once we're in options here what we're gonna do is we're gonna scroll down and then we're gonna 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 mackerel option lives under developers so we want to check that box and then we're gonna click on ok what you'll see happened is now we have a new pivot called developer and so I'm gonna 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 gonna 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 gonna record a macro that does all the formatting here to get it to look like this formatted sheet so what we're gonna do is I'm gonna first click on record macro and I'm gonna 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 can use underscores kind of however you want to handle that I'm just gonna use understood 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 can click on to stop recording once I'm done but I'm not done yet cuz I haven't done anything so the first thing that we're gonna do is I'm gonna insert another column here and I'm gonna call this one first name and then we're gonna 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 view and we're gonna click on text to columns and I'm gonna say delimited next and we're gonna 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 gonna go to conditional formatting and I'm gonna highlight any cell that has a value greater than 0 so we're gonna say greater than and I'm gonna say 0 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 gonna do now is I'm just gonna undo all the changes that I just made so we're gonna go back and I'm also just gonna 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 gonna 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 sheet so 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 have 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 gonna 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 used relative references if I have the active cell here it would have simply apply all the formatting in this location so that's what I mean that's what it would do I'm gonna get rid of the formatting for just a moment and I'm gonna 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 can click on macros and then you could run it from here when you can also do is if I click on insert what I can do is I could insert a button now so we're going to insert a button and I'm gonna when when I click on this button I'm gonna run this macro the format customer data so I'll click OK and now I can title this button maybe I maybe like 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 can use a button or you can 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 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 C 20 or maybe you see 100 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 can go ahead and you can that'll just automatically be saved then so 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 can 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 computer computers are great at doing that you know predictable same routine tasks again and again 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 can 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 enjoy it I'll see you next time bye

Channel: Kevin Stratvert

Views: 500,074

Rating: 4.9601431 out of 5

Keywords: macro, macros, excel, microsoft excel, office 365, office, microsoft office, developer, analyze, format, data, tutorial, guide, help, office.com, filter, repeat, automate, script, program, visual basic, vba, button, edit, beginner, o365, microsoft, row, column, excel 2019, 2016, excel 2016, example, sample, for dummies, training

Id: uyj_OljPlcU

Length: 9min 16sec (556 seconds)

Published: Fri Aug 30 2019