Transcripts
1. Introduction : What's going on, everybody, Thank you so much for stopping by to check out my Macron's master course here on skill share. Might even Steven with my finger. And if you're watching this video, you most likely have experience using Microsoft Excel. And if you're anything like me in about your mind, you know how powerful macros can really be and how much time and effort they can help save for you. But to be quite frank for me, for a number of years, I avoided using Macron's because I was intimidated, right? I thought that I had to be great at programming, and I had to be this techie guy figure out how to use macros and so that fear and intimidation really prevented me from. A lot of the benefits of map arose, and I really miss out over a number of years because I didn't know how to use them. And so eventually, when I forced myself to learn and figure out how to actually use macros, came to a couple of realizations. One. They are actually way more powerful than I initially thought. They save a lot more time, and number two, it's not as hard as I thought. And so with this course, one thing I want to do is take all the information out there and to still it down into the basics and fundamentals so that you can get started using macros immediately. One sort of pain point I found when I was watching a ton of videos and figure out how to do use. It is most of people or teaching how to use Matt grows. They really know how to use them. And they're really techie, smart developers, which is great. But a lot of times they tend to focus more on the actual code of it, right, the visual basic aspect of it, and tend to over complicate things for the average person who's just trying to get started . And I think a lot of times that complexity can scare people away from getting started. So with this quarter's I'm gonna take, you know, all the information that you need to know and distill it down into something that's a lot more simple and basic and is in layman's terms so you can understand exactly what we're talking about here, and then I'm also gonna give you sample data which I'll actually using in the course, So that way you can follow step by step with everything that I'm doing. With that being said, I'm very excited. You've decided to stop by Let's go and jump into the course.
2. Why use Macros? : What's up, guys? Thanks again. So much for stopping by to check out this course. This is of course, I'm very excited and passionate about because I've seen firsthand experience how powerful Mac Rose can really be and how big an impact they can have on your productivity or efficiency and making you either a business, better business owner or a better employees. And I think Macron's could be a little intimidating to people. At first. They think they need to know a ton of programming or code or it's just really confusing. Doesn't make a lot of sense. And so this whole course, I'm just gonna clear things up, you know, make things very clear, straightforward. We're not gonna go into every single nuance of Mac Rose but teach you 80 to 90% of what you need to know. So that way you can walk away from this knowing exactly what to do. The first thing What are macros? Macro Zehr Simply a series of recorded steps that you take right. So how that works is you click record macro, and then every single action that you do with your mouse. The computer is recording it, so if you click in a specific cell. It's recording that if you change the color of words, it's recording that If you change the font of something, it's recording that if you delete a row, it's recording that, and it records that in these acts steps that you do it. The cool thing is you can customize and name them so you can create multiple grows. And it's also great because it allows for consistency and steps. So if there's some type of process that you're doing where you're doing the exact same steps every single time, Macron's could be great to first a safe time but also reduce any types of errors, right? If we're doing the same thing, let's say you're doing a 20 step process, and it's the same thing every single day, day after day. It's very possible that one of those days you might miss a step. But if you create a macro for it and it automates that process, you're reducing any type of potential errors you could make. Why do we want to use them well, for one, it's going to save you a lot of time. As mentioned before it can reduce any possible errors. So make your work overall a lot more accurate. And another thing to consider is this can help make things easier for others. Well, whether it be your employees, your co workers, it's very common for companies, businesses to work on projects together. You may have a lot of people going in and out of an Excel document making their calculations at its comments. What Whatever the case may be real world example of this was years ago I was working for financial institution, and the job included doing a lot of accounting and financial analysis of different stocks and trades. One part of the process is we had to do kind of a complicated calculation on the interest of the securities. And so instead of us having to go through that complex calculation every single time, one member on the team created a macro that would automatically calculate the interest where all we had to do was input the raw data and then execute the macro. The macro automatically did all those calculations for us, right? So imagine the amount of time an effort that that one team member help save for everybody else and ultimately for the company
3. Set up developer tab: the first thing we're gonna want to do is make sure that we install what's called the developer tab. So if you take a look at the screen, I'm showing screenshots of what it's gonna look like by default when you first log in to excel. If you've never done anything with macros, so if you take it, look on the top, right? Notice how the tabs they go, you know, home insert draw page layout formulas, data review view. But there's nothing to the right of that where if we take a look at the picture below, that's how we want our Excel toe look. And so there's a couple ways for us to go ahead and add this developer tab. The first way is if you go to excel preferences, then you click the ribbon and toolbar button. Then on the bottom, right there, you'll see the button for developer that's not checked. So what we're gonna want to do is check that box and then click safe, and then if you take a look on the ribbon there on the top, right are Developer Tab will be there, so that's one way to go about it. Another way to go about this is take a look. You'll click on this button, which is illustrated here, that will give you a drop down. You can then go to more commands and then notice this kind of similar to before except we'll click ribbon and again as before, developers not checked. So we're gonna want to make sure we check it and click safe. You can get that developer tab at it using either one of those methods. And I know we went through that a little fast. So I have added a copy of this presentation down in the show notes below. So that way you can actually go ahead and download it. And you have the exact screenshots that I went through in case you needed to take a little bit more time. Teoh, get it at it. But now that we got the developer to have added, let's go ahead and actually learn how to cree and run macros
4. How to record macro: Now we got the developer to have added There's a couple main areas that we're going to focus on. The 1st 1 is this macros button in this is gonna simply display a list of macron's that you've already created. And this is one way where you can execute or run a Mac er that you've already saved. So taking a look, we haven't created a any macron's yet, so obviously there's gonna be nothing in this box, so we'll close out of that. The next button is record, Macro, and I'm gonna click it just to show you what it does here. So we click record macro, and before we actually start recording the steps, we want toe, name it something. So I'm gonna say example. And we're gonna store it in this workbook, and they also give the option for a keyboard shortcut. So, as mentioned before, after you save the macro, one way to run it is by clicking macros, and then it will be displayed in this box and you click it and then you would run it. So that's one way to do it. But they also give you the option. If you don't wanna go all the way in there. You can do Ah, keyboard shortcut. Right? So similar to If you want to copy something, you know control, See is a keyboard shortcut. Control V is paste for keyboard shortcut. So it's cool that they give you the keyboard shortcut option to run Mac Rosa's Well, something to keep in mind is, if you do decide Teoh, choose something it can't conflict with an existing keyboard shortcut. Right? So if you're on a a windows watching this and you want to use control, see for a macro, that's not the best idea, because Control C is already a common keyboard shortcut for copy, right? So Ah, but we're not gonna do anything with this for now, so we'll just click OK now a couple things to notice. It's not very obvious, but if we go back to where we initially clicked, it's a stop recording. So that implies, that were currently recording a macro. Another way to tell that you're according to macro is on the bottom. Left here, it displays this little square, and once we're done with all of our steps, we could stop it by their clicking up here or by clicking this spot, but known here and then notice how that changes and says record macro. So now, since we technically recorded a macro, even though we didn't really do anything, we go to the macron's button up here and there we go. Now that we see this macro here, we could actually, if we don't want it, we could click this minus button to delete it. And then if we go back, notice how we have no more macros.
5. Example 1: already. So now that we know where the developer tab is in some of the main buttons within it, let's go ahead and record our first macro. So what we're gonna do is we're gonna pulling some sample data, and I've actually included this file in the show notes or attachments below. So that way you can use this exact same data and follow along with me as we create it. And this data I just made up myself, right? So for this example, this is a bunch of fake employee information, names of employees, the years about the company. What building? They work in their phone number and their salary. And I apologize in advance for some of the employees names. They're kind of ah, kind of out there. But, um, use the random name generator for those. So with this, Like I said, we went to our sample data. We copied it. E m p. Sited in. In this macro that we're creating, it can be good for real world situations because a lot of times some. So a lot of times you may get a report of just raw data, but you need to clean things up and you need to make it in a way. But you need to make it presentable before management or somebody else can better review it and understand what's going on. So the whole goal of this macro here is to better format the data and make it look better. What we're gonna do is go back up to record. Macro, We're gonna name this so we'll just call it format. Save it in this workbook. We're not gonna do a keyboard shortcut. Click. OK, so take a look. It's has stopped recording. Excel is currently recording every step that we take. First thing we're gonna do is make these headers bigger. So we're gonna change that to 16 font, and we're gonna make sure all the columns fit expand that we are going to delete the building number that the employees in because that's not relevant information. Next, we're going to change the salary to actually dollar amounts instead of numbers. Here we go. And for this column, years with company. Let's say that's too long and we just wanna have it say years make that fit, and then we will at a box around everything all borders and then we'll change the color to make it stand out just a little bit more. All right, so that is it for the macro. Well, then go to Developer Tab. Click. Stop recording. And you just successfully recorded your first macro. So a couple things we can take a look at here. Number one. Now that we've created a macro, if we click this button here, it now list the macro that we recorded so before, Like I mentioned, let's say you didn't like it or you wanted to delete it. You could just click this minus button, and that would delete it. Now that we recorded the macro, you can actually see the code of what happened. So if you click the visual basic button, this section here is basically the back end code for every single step that you took. So if you remember, we one of the first things we did was we changed the size of the headers from 12 or 11 font up to 16 and that's reflected right in here. And so as we go through, I'm not gonna break down the actual code because there there's a whole programming language called Visual Basic and so that's not the goal of this course. The main thing to understand here, though, is when you are recording a macro on the back end, there is code that's being recorded, and then you can actually go back. If this is some day that you're interested in, you can go back and actually edit things. Or if the macro doesn't work for you, you could just delete it and then create a new one. So kind of, however you want to approach it, but mostly just wanted toe make you aware that this exist, so we'll exit out of that XO. Now, the real test of this is to make sure that our macro works moving forward. So what we're gonna do is we're gonna delete all the data here. We're gonna format it back to just a regular Excel doc like it was before. Now that we're now, we're going to go back to our sample data. Gonna re copy it, pieced it back, reduce the size of these columns again, gonna paste it back in Now, go to developer Mac rose and run. And if you take a look, it automatically executed all those steps that we recorded before
6. Common road blocks: now. One thing to be mindful of when creating and running back Rose is it records everything you do down to the exact cell. So I want to show you an example of how this could go wrong. So we're gonna delete this. We're gonna kind of reset things for you. Okay, so if we remember when we first recorded or macro and when we pasted it, we always pasted the data in this first cell, right? A one. What would this look like, though, if we and it a pasting it the same data into sell A to pace that developer run back notice It didn't form at the way that we wanted it to. And that's because the macro was running or doing all the steps that we told it to under the assumption that the data started in 81 So this is something to be mindful of and just to keep in mind, right? So if you're creating macros, safer other folks to pay state A in Eudes best practice to make sure that you're really clear about where they insert the data. So that way you're not running into issues like this. Let's say we've always had the same 20 employees, and then recently a new employee joined the company. Chris Smith. So we have extra data or information in our report. Let's take a look and see what happens when we in Put this into the macro that we just created. It's all piece that again. Remember, there's one extra line compared toa when we first recorded the macro developer Macro, run! Okay, so notice how the macro only did what we told it to. It only went down that number off cells and it didn't include this. This is where it could be good to do one of two things. You could either re record the macro or you could go into the existing macro and update it . So one that will be mindful of here is notice how the last cell is de 22. Okay, so we'll go back. Tarmac Rose format. We can now click edit. That's going to take us back into that visual basics that I showed you earlier. And one thing we can do is go through here and take a look at these previous commands rights when it's talking about range D to two d 21. So we go in and change that to 22. Here, change that 22. Okay. And I I don't I don't see any other range talking about D 21 so we will then save it. Say yes. So we updated the macro to include for this last cell. Let's go ahead and run it again. Developer Mag Rose. And again, if we just want to take a look at it, we can click at it. Scroll down, Notice how the ranges including D to two D 22 right de to two d 22. So macro, run! And there we go. We are now accounting for Chris Smith. So I know the I know there's kind of a long example So and so the main thing I wanted to point out here was macro Zehr really good for repeating. So the main point I wanted to get across here is that back rows are really good for automating steps where you're doing the exact same thing every single time or where you're working with a data set that is the same every single time or it's the same size macro zehr Great for helping you not do a lot of these repetitive steps over and over again. And I think in all honesty, once we get into the whole visual basics, that's the big thing. That scares a lot of people and can really kind of turn them off to Macron's in general, because it's not uncommon for you, too. Create a macro, you try to run it. And then there's some type of an error that pops up, and the moment that happens that can discourage people from using it. They think it's too hard, and then they end up quitting. So I although we're able to create a macro, I also wanted to point out some of the main sort of pitfalls or roadblocks you might run into when creating your own. So that way you know not to get discouraged, and you know how to best navigate them and also know what situations may be best to create macros. So that's it for this first example, we're gonna take a look at a second example to really hammer home the idea of how we can best use macros
7. Example 2: in the last section, we covered an example of how you could create and use macros for reports, which would be pretty helpful when you're in the work setting. But now let's cover an example of how you can use macros even for personal use. So the cool thing about Excel is they offer different templates. So for this example, we're gonna go file new from template. And let's say that you're trying to take better control of your finances. And to do that, you are gonna create a monthly budget. So from these templates, let's select manage my money that opens a new spreadsheet here for us. And if we notice it's ah, it's a pretty cool template, very simple, but gives you different areas to input your in income, your monthly expenses and savings. Well, let's say this is something we're going to create every single month, right? And to help save time, instead of having to create a new one every single month, we're gonna create a macro to help save just a little bit of time. So one thing we could do is let's say this is already January, Okay, but now we're going to create a macro for the next month. So that way, once February 1st rolls around, we can just click a button, and it's already ready for us to fill in information. So a good way to do this is go back up to Developer Court Macro, we're gonna call this budget click, OK? And again, it's recording. So the first thing we're gonna do is right, click mover copy. And we're gonna make a copy of this spreadsheet, and we're gonna move it to the right limit current month. Then we will, right? Click. We're gonna change the color to indicate that it's different month. Something else we're gonna do is delete out the fields for the information that's gonna change. All right, so let's say for this example, the persons on salary, so their income sources are gonna be consistent month over month, But some of the expenses air gonna be variable, right? So well, say, rent mortgage that stays the same. Electricity that could change from month to month gas that will change cell phone stay the same groceries will change credit cards, change and miscellaneous. Then we'll say savings will remain the same. All right, so what? I'm doing is I'm all right. So I'm just deleting out the areas that are variable. So that way, after they run the macro when they come to this February 1st, they don't have to go through and delete thes, but they're already empty. Just ready to be filled in. Right? So that is it for the macro stop record. Now, just to make sure it works here or rename this 1 February and then let's say we want to create one for March. Macro budget run. And there we go. We now have one for the next month. With those sections already deleted and ready for us to fill in in the next section, we're gonna show you how to create buttons. Lincoln back rose two buttons to create a better overall user experience.
8. Adding buttons : Now that we know what macro czar and how to create them, I'm gonna introduce another idea which can help make the user experience that much easier. So what you can do is under this developer tab. Notice how there's different options, but group box combo box label etcetera Click this button and you simply drag the button onto the spreadsheet. No, when you do that by default, it wants to give you the option to associate or a sign of macro to the biden. So if we click the format Makro, which we created before, click OK, notice. Now when we hover a mouse over top of the button, it gives us the The icon changes right? And that simply means if we click this button that's gonna execute or run the macro, which we previously created. So from user experience standpoint, this could actually be more helpful because let's say you created this workbook for other team members to use. So instead of them having to no to go to the developer tab, click macro click run, you could just put this button by adding a button. You make it really easy for other people to just do one step toe run the macro. So going back to our example Data. We'll put this back in here and we're gonna move this button over a little bit and we will click, Run, Notice how it does all that automatically for us. So that's one cool way. Now let's say you want to customize this. You don't like the way this button looks. If you add any of one of these options up here under the developer to have, you do have some limitations when it comes to formatting it. So let's say we want to delete this. You just right Click click Cut. The really cool thing about Excel is you can basically assign anything you want to a macro . What I mean by that is, let's say we want to add a shape and we want to add this smiley face. All right, so with the smiley face, we have a little bit more options. When it comes to formatting, the way it looks, let's so it's bright yellow and will change the color of the eyes to black. We'll go ahead and delete all of our our reform metal, our data here, then what we could do with our shape Here is right. Click. Go to a sign, Macro. We just click our format button. Okay, so now we have her over and notice our smiley icon is linked to or associated with that macro. So again, bring the the data over paste. Click it. There we go. So you can do this with I mean virtually anything, right? You could use shapes. You could use pictures. Um, you could use the buttons like I mentioned before. It's really up to personal preference.
9. Class Project: now we learned the basics. Let's go ahead and put this information into action. So for the class project, I want you to use the sample data that I've provided right, the employee information, their names, salary all that and create your own simple format Macro. Now I don't want you to create it exactly like I did, but put your own twist on it if you could make the size of the font different or the color or whatever unique spin you want to put on it. But for the glass project, go ahead and create your own format. Macro used that, and then what you should do is go ahead and upload the results of the macro to the course. So that way everybody else can take a look and see your own unique spin that you put on this and again. The main goal this year is to get the reps get the basic understanding of how to access it , how to record, how delete, and once you get the fundamentals, it's much, much easier and quicker to scale up to a lot more complex and bigger data sets
10. Final thoughts: all right, so that wraps things up for this course here. I know it's kind of quick and really straight to the point, but wanted to give you Ah, brief introduction into Macron's give you a couple good examples of how you can get started with this, and especially when we're dealing with stuff like Macro is where it can be intimidating, right? Once we get into that visual basic and you actually start taking a look at the code that can be overwhelming. And so I think it's very important when it comes to stuff like this is to keep it as simple as possible and first start with the basics, understand how to develop a basic macro, and then as you get more comfortable with it, you can start to build upon it afterwards. But I think a lot of people make it make the mistake of they see all these complicated Mac Rose and all these crazy steps, and they think, Well, I'm not a programmer, so I can't do that. But the really the whole goal that scores here is to, uh, gets you taken action and just starting small and then building upon that
Mind Favor
