Quick TIp 6 How to Record a Macro in Excel - Microsoft Excel 2007, 2010, 2013, and 2016 Tutorial

now let me show you how to record a macro the macro this macro is going to um automate um something in Excel that's called the advanced filter now I want to show you something in in in the main sheet here I have a bunch of data kind of like we just saw a few minutes ago and then in sheet two this is where I have the criteria for the advanced filter and that is how the uh field names are in the first row and the field names are the same as in the main data that's really important for that criteria uh I'm going to record a macro usually the the um Advanced filter is about eight steps I'm going to reduce it down to one step by making a macro so here's what we'll do no matter what the macro actually does they all get started the same way so I'll pick on The View menu then under view on the right hand side I'm going to click on the pull down where it says macros and then I'll pick on record macra now you want to give your macro a name now I'm going to call it run filter notice how I'm not using spaces in the name for whatever reason the name of the macro does not like spaces so you want to avoid those if you want a a a spa if you want a separator between the names you can use a an underscore instead of a space it just doesn't like spaces for whatever reason now I'm going to click on this little box where it says shortcut key and uh this way you can give it a keyboard shortcut so I'm going to type in the letter R so to run this macro I'll use controlr I can also use uppercase letter so now I just did control shift R so to run that macro I would use control shift R so you can use uppercase letters or you can use lowercase letters why is this important for you well you see as long as this workbook is open then controlr is now going to run the macro instead of what it usually does it's going to replace what it usually does when this workbook is open now don't worry about that you might think is it going to replace my uh copy or is it going to replace my paste well not if I don't use control C here then it's not going to replace my copy so use a letter that you don't use for something else I can't think of anything I use controlr for so you know that'll be fine now here's why this is also important I'm going to come over here if you just want this macro to work on this workbook only then I'm going to store in this workbook however if you store it into the personal macro workbook uh so um if you're starting in a personal macro workbook that means every time you go into Excel on any workbook then the macro will work it becomes kind of a universal so if you store into this workbook it'll only work when the current workbook is is open if you store it into the personal macro workbook that means it'll work every time you're in Excel no matter which square that you're in it becomes kind of a universal so if you think of something that you want to use all the time then store it into the personal here are the store in this workbook now you can put anything in the description for comments or for documentation purposes but that's really optional more importantly I would give it a good name I call mine run filter uh with no spaces uh controlr will be the keyboard shortcut and we're going to start in this workbook now all macers are going to start that way no matter what they actually do at this point I want to click on okay now I want you to not at the bottom of the screen here on the left hand side you see that little blue square that means you're recording your macro now in Excel 2013 they changed that square to white but the square will still be down there as long as you see that square you all now you're now recording your macro so everything you do with your keyboard everything you do with your mouse will be record it as long as you see that square there now I'm going to do the steps for the advanced filter as I'm doing them I'm going to count them so you can see how many steps it's going to save us at the end so to run the advanced filter I'm going to pick on the sheet that's called Europe expenses go back to the main data that's step one uh I'm going to pick on one of these cells within the block of data that's step two then I'll pick on the data menu up top data that's step three and I'm want to pick on the word advanced righta there which means Advanced filter and that's step four so this is the advanced filter screen now everything is fine here except for the criteria range so I'm going to pick where it says criteria range and that's step five I'm going to go to sheet two sheet two is really where my criteria is pick on sheet two and I'm going to highlight this information A1 through F5 okay so um that was uh step six is where it went to the sheet now I'm on step seven highlight the data and I'm going to click on okay and what's going to happen is that just ran the filter on this data based on that criteria all right so now I can keep on doing more and more steps for my uh for my macro I'm still recording I could do as many steps as I wanted to maybe next is copy and paste or delete a row or format whatever you wanted to do but now I'm done with my macra so when you're done with your macra now you're going to pick on that square look what the square says a macro is currently recording click here to stop recording so I'm going to I'm actually going to click on the Square click it and now you've just recorded your macro so let's see what that really means I'm going to pick on sheet to now I'm going to change my criteria everybody so U instead of Italy and lunch I'll type in Austria there and instead of France uh dinner items perhaps I'd like to have Spain there and for United States I'd like to have uh United States Hotel items and now I want Germany's that are less than 25 so I'm changing the criteria I'd like to rerun the advanced filter I just recorded a macro about that now there's two ways I can run the macro I can either say view macros and then you say view macros and I can just pick on that and it says the word run there however as you saw I made that a keyboard shortcut so uh I used that as controlr now when I did the advanced filter the first time it was 8 steps right now on my keyboard I'm really going to do controlr now that's going to rerun the advanced filter except with the new criteria except I made it down to one step instead of being um instead of being eight steps so notice how it just ran out for the new criteria you can clearly see I have Spain in there and then we should have some other United States to their hotels so watch what we did again I'm going to go to sheet two uh let's change that criteria again so I'll say um a instead of Austria I'll type in France and instead of Spain I'll type in Italy I have new criteria now I want to rerun my Advanced filter except I don't have to rerun the advanced filter the full way I made a macro out of that so I'll do control R again now in one step it reround the advanced filter with the new criteria and I boil it down to one step so you have to think what's repetitious in my Excel spreadsheets what do I do every single day that could be a great macro