Excel 2013 Advanced Filter using a Macro

the purpose of this video is to show you how to take a range of data and create an advanced filter over that range in addition to just doing an advanced filter we'll also be making our data look like this we're gonna have a couple of buttons one to run a filter one to do reset and then we'll have macros attached to those buttons so that we'll be able to do something like this where I can put in a company name or part of a company name and an industry and then hit filter data that runs our filter narrows the list I can then do reset and that'll put everything back the way it was so let's get started coming back to my portfolio tab and in this case I've got a range of data what the range of data is is not super important it just needs to be a range of data that you want to have filtered the first step is that I need to create some room above this range of data so that I can insert a criteria range so I'm going to create six extra rows above this data and I'm going to do that by going to my row headings on the side I'm going to click hold down my mouse button and select six rows and then I will do a ctrl + to insert six rows above my data if you do not have a numeric keypad with a plus sign you might have to do ctrl shift + to get ctrl + to work the next thing I'm going to do is change my range of data into a table and I need to do this for the advanced filter so I'll click anywhere inside my range of data and then I'm going to click the insert tab on the menu and then click table this will select the data it will also ask me do you have headers on your data and in this case I do these are the header names and if you're going to do an advanced filter you will pretty much need to have some header names on your data as well I'll go ahead and click OK and I now have a tail for my data the next thing I want to do is turn off these Auto filter arrows because we don't need them for the advanced filter so I'm going up to the data tab and then I will click the filter button and that turns those arrows off next we need to copy these headings so I'm going to select all of my table headings I will right click and say copy and then I'm going to come up to about row 3 click in a3 and then right click and paste this creates an exact duplicate of my table headings and that's important because when we run the advanced filter we're going to have a criteria range which is up here and the headings of the criteria range must exactly match the headings in our data or list range which is down here that's what ties the two parts together so that the advanced filter can work now that my headings are here I now need to create a range which I'm going to name criteria so I'm selecting the new headings I'm also selecting a blank row of data directly underneath those headings this is my criteria range again notice that the headings are going to match the headings in my list I need to select the headings in both the criteria range and the list range so here my criteria range is selected I'm going to go to the name box and I'm going to type in the name criteria and hit enter so I've typed in criteria I hit enter and Excel assigns a name to this range which is criteria I'm gonna do the same thing to my list range again I'm selecting all of the data and also the headings this is why I can't use a table name in this case is because the table name would not incorporate the headings into the advanced filter so I've selected this range I'm going to come up to my name box and I'm going to type in lists and then hit enter so I I have now named my criteria range and my lists range I can check that by going back to the name box I can pull it down and choose criteria and there you can see that my criteria range is selected I do have the headings and I have one extra row that's good I can also come back over here and choose list and when I do that you can see my list range is now selected including the headings so everything is good to go for an advanced filter let's test the advanced filter so I'll go up to company name and I'm going to type in the letter A which should give me both Apple and Amazon so once I've typed in a letter A I'll run my advanced filter so I clicked out of that cell now I'm going to do the data tab and then I'm going to click on the advanced filter button when it comes up I'm gonna give up my list range and you can see now why I named my rages list and criteria is so that I wouldn't get them mixed up so under list name or list range I'm going to type list and then I hit tab under criteria range I'll hit criteria and then I'll click OK notice that it narrows my list to just the companies that start with a this is good it's working I'm going to click on the a under company name and delete that and then we'll rerun the advanced filter just so you can see again how to run the advanced filter so I click data advanced filter my list ranges list my criteria range is criteria and I'm going to click OK and you can see that the list is back now I'm ready to go ahead and record a macro to do the same thing now if you are in a situation where you've never recorded a macro again a macro is just a list of commands or keystrokes and things that you do and then Excel will play those back for you now to record a macro you have to have the Developer tab on your ribbon and as you can see I don't have a Developer tab on my ribbon so I am going to have to add that to add the developer tab I'm going to the file tab I'm going to come down to the options and then from options I'm going to choose customize ribbon and here you can see I have a Developer tab but it's not checked so I'll go ahead and check that and then I'll say ok and now my Developer tab shows up on my ribbon so I'm good to go I can now record a macro that's record one so I'm gonna go to the Developer tab and then I'm gonna click record macro he wants to know what my macro name is and I can use letters or numbers but I can't use spaces I'm going to tell it that the macro name is filter data notice that I don't have any spaces in that macro name I could use an underscore if I wanted to separate those a little bit now I'm gonna say ok at this point my macro is recording every keystroke and every mouse click that I do let's click data advanced filter and at this point you're screaming wait wait wait you didn't type in any filter criteria and you're correct I did not but since I'm just recording the macro it doesn't matter at this point if I have filter criteria the macro will work whether there's criteria there or not so I'll go ahead and keep going the list range is list the criteria range is criteria I'll click OK now notice that my screen didn't change but it did run the advanced filter so I'll go ahead and stop recording and I can stop recording down here at the bottom of the screen or I can click developer stock recording and I now have a macro we can test that macro let's go ahead and put an industry in here so I'm going to say medical and then I'll click off and now I'm going to go to developer macros and here's my filter data macro that just recorded and then I click run you can see that it did work it ran the ran the the advanced filter just fine let's record a second macro this macro will be to reset the advanced filter resetting it will to reset it I'm just going to select this row of inputs and hit delete and then rerun the advanced filter okay so I'm ready to create my reset filter macro I'm going to click record macro the macro name will be reset filter this time I will use an underscore so you can see how that looks but I cannot use a space here and then I will say okay now I'm recording again I'm going to select a4 through j4 which is my criteria range but not the headings and then I'll hit my delete key so that will clear all those once those are clear I can go ahead and do the advanced filter again so I'll click data on my ribbon click advanced my list range is again list my criteria range is criteria and now I will say ok everything is back the way it was don't forget to go back to developer and stop recording the only piece we have left is to create a couple buttons and assign our macros to the buttons so I'm gonna make Row one a little bit taller so I have room for some buttons and let's do insert and then here I have my shapes I'm gonna choose the rounded rectangle so I'll go ahead and choose that and I'll draw a rounded rectangle up here there's going to be my filter button once it's selected I can start typing so here we go filter if you want that to be centered you could do home and then center horizontally center vertically and now it's centered on the button so that's good I'm gonna click off that button and then I'm gonna click the button and then hold down my control key click it again and drag it and I just made an identical button and then I'm going to select the text on this button and type reset so now I have two buttons and I just need to assign the macros to the buttons to assign a macro to a button click on the button right click and choose assign macro you'll look for a filter data and then say okay we now have a macro on that button let's go to reset right click I'm going to assign macro and choose reset filter and say ok now they have the macros attached to them let's test it so I'm going to come in under industry let's put energy I can click filter and reset it's working great if you've created a workbook with the macro in it you're gonna need to save it as a macro enabled workbook so the last step will be to save this file save as and I'll just go ahead and put it on my desktop for now it was here where I say save as type I'm gonna pull that down and choose Excel macro enabled workbook so that it will retain the macros now I'm ready to save I simply click Save and we're on our way

Channel: Rick Koontz

Views: 201,568

Rating: 4.8720236 out of 5

Keywords: Excel 2013, Advanced Filter, Macro, Button, Microsoft Excel, Tutorial

Id: OlLCuclgf3A

Length: 12min 25sec (745 seconds)

Published: Thu Nov 05 2015