Bottom line: Learn how to create macro buttons that apply filters to multiple columns in a filtered range or Table.

Skill level: Intermediate

Download the Excel File

Download the example Excel file to follow along.

Macro Buttons For Filters.xlsm (4.8 MB)

Macros for Filters

If you regularly apply the same filters to multiple columns in Excel, then you know how time consuming it can be.  In the video above it took me 24 mouse clicks and key strokes to apply filters to three columns.

Create VBA Macro Buttons to Apply Filters in Excel

Fortunately I didn't have to scroll horizontally to find the columns.  That can add additional time to the task when we are filtering a large data set with a lot of columns.   My Filter Mate Add-in helps with this task of navigating filtered ranges.

Let's see how we can turn those 24 steps into one single click or keyboard shortcut by creating a macro to filter our data.

Step 1: Record or Write the Macro

The first step is to create the macro.  In the video above I use the macro recorder to create the code.

The macro recorder creates the VBA code for all the actions we take in Excel.  It is a great tool for creating macros for filters.  The code can get complex, especially as we apply filters with multiple criteria, and the macro recorder can create most of the code for us.

Macro Recorder to Create VBA Code for Filter Drop-down Menus Excel

Here's how to record the macro.

  1. Clear the filters on your sheet or Table.
  2. Turn the macro recorder on (Developer Tab > Record Macro button)
  3. Give the macro a name.
  4. Choose to Store macro in: This Workbook
  5. Click OK
  6. Apply filters to one or more columns using the Filter Drop-down menus
  7. Stop the recorder (Developer Tab > Stop Recording button)

We now have a macro that will apply all the filters.  You can look at the code in the VB Editor if you'd like.  I explain more about that in step 3 below.

Step 2: Create a Macro Button

Next we will create the button on the sheet and assign the macro to it.  I like to use Shapes for macro buttons, but you can also use the Command Button control from the Insert drop-down on the Developer tab.

Here are the steps to create the macro button.

  1. Draw a shape on the sheet (Insert tab > Shapes drop-down > Rectangle shape).
    Use a Shape for a Macro Button VBA Excel
  2. Add text to the shape (Right-click > Edit Text | or double-click in the shape).
    Draw the macro button shape and style it
  3. Assign the macro (Right-click the border of the shape > Assign Macro…)
    Right-click Assign Macro Excel Shape
  4. Select the macro from the list.
    Assign Macro Window for Macro Button Shape
  5. Press OK.

When you hover your mouse over the shape it will now turn into the hand pointer cursor.  Clicking the shape will run the macro.

Left Click Shape to Run Macro - Ctrl to Select Edit Shape

Woohoo!  We have just turned dozens of steps into a single click! 🙂

If you need to select and move/edit the shape, hold the Ctrl key while clicking the shape.  This prevent the macro from running and allow you to modify the shape.

Step 3: Modify the Macro

Eventually you will want to modify this code to change the filters.  Of course you can re-record the macro to get the new code, but it's also good to understand how the code works.

We can look at the code in the VB Editor by clicking the Visual Basic button on the Developer tab (keyboard shortcut: Alt+F11).  Then double click the module that was added to the workbook by the macro recorder.  See the video above for details.

View and Modify VBA Code in VB Editor

Each line of code uses the AutoFilter method to apply the filters.  The AutoFilter method has 5 parameters.  These parameters allow us to specify the column that the filter will be applied to, and the criteria.

VBA AutoFilter Method Applies Filters to Columns - Filter Drop-down Menu

I have a series of posts on this topic, starting with The Ultimate Guide to Excel Filters with VBA Macros: AutoFilter Method.  There are additional articles for different filters and data types including: blanks, text, numbers, dates, colors & icons, and clearing filters.

Important Things to Know about AutoFilter

I recommend checking out the Guide to VBA Filters mentioned above, but there are a few important things to know about the AutoFilter method.

VBA AutoFilter Method Important Things to Know

  • The AutoFilter method can be used on both regular ranges or Excel tables (VBA ListObjects).  I explain more about that in the Guide to VBA Filters post.
  • Each line of code clears and applies a filter to a single column.  The code first clears any existing filters to the column, then applies the criteria specified in the line of code.  Therefore, we cannot use multiple lines of code to apply multiple filters to the same column.  All criteria for the column must be in one line of code.
  • The ShowAllData method is used to clear all filters.  This line can raise an error if the filters are already cleared from the sheet.  Checkout my post on clearing filters with VBA to learn how to handle the error.
  • The values for Criteria1 and Criteria2 are wrapped in quotation marks.  Any comparison operators for greater than/less than filters (= < >) or wildcard characters (* ?) are also included inside the quotes.

Give Your Users More Buttons

Once the users of your spreadsheet use the button, they will want more of them!  Fortunately it's easy to replicate the button.

Step 1: Make a copy of the macro button

The easiest way is to hold the Ctrl key, then select the shape, then drag it right or left.  This will duplicate the shape.

If you hold the Ctrl and Shift keys during the process it will keep the new shape aligned with the original.  Here's a quick screen cast animation that shows how to duplicate the shape.

Duplicate Macro Shape Button Ctrl Shift Drag

Step 2: Create a new filter macro

You can record a new macro with different filter criteria.  Or, you can copy the existing macro in the VB Editor and paste it below.  Just remember to change the name of the macro and also modify the AutoFilter lines of code.

Step 3: Assign the macro

Assign the new macro to the new button.  You can continue to replicate this process for commonly used filters.

Multiple Macro Buttons to Apply Filters

The screenshot above shows an example of 5 macro buttons that apply different filters.  I made it look like a slicer, but it's actually shapes assigned to macros.  The file that contains this example is the file on the The Ultimate Guide to Excel Filters with VBA post.

Your users (co-workers) will love the time-saving convenience of these buttons, so expect some high fives coming your way. 🙂

Adding Macro Buttons to the Ribbon

We can also add macro buttons to the Ribbon toolbar in Excel.  Checkout my 4-part video series on the Personal Macro Workbook to learn more.  In that series I explain how to add custom buttons to the ribbon for use on any open workbook.

Create the Personal Macro Workbook and Add Buttons to the Ribbon

Those ribbon buttons won't necessarily travel with the workbook, unless you use Ribbon XML to modify the workbook.  I'll do a follow-up post on the differences between those techniques.

Free Webinar on Macros & VBA

If you are interested in learning more about macros, I'm currently running my free webinar called “The 7 Steps to Getting Started with Macros & VBA”.  It's running all next week, and it's absolutely free to register.

During the webinar I explain why you might want to learn VBA, and a lot of the basic coding concepts that will help you get started.  I jump into Excel and the VB Editor and walk through how to write and run our first macro.  Even if you have been using VBA for awhile, I'm sure you will learn some new tips.

Macros and VBA Webinar Banner - Join Me - 550x200

Click here to learn more and register for the webinar

Please leave a comment below with any questions or suggestions.  Thanks! 🙂


Your email address will not be published. Required fields are marked *

  • Jon,

    I would like to filter data for a school district by school. I would like to create this auto filter and copy to a new spreadsheet in macro.

    We have 32 schools and this is a tedius task sharing test data with our schools.

  • Hi there Jon
    Thanks for the tutorial.
    I am looking to filter multiple columns in a simple sheet which is simple enough with the tick boxes for some columns but want to know how to filter by several job keywords within a single column – i.e. ‘sustainability’, ‘environment’, ‘csr’, ‘materials’, etc. Is this possible? Basically any job title with any of the above keywords, I wanted filtered.
    Equally, on one of the other columns, I want to filter by company name but just programme in a load of leading electronics manufacturers, so anyone with ‘cisco’, ‘apple’, ‘ibm’, etc in their company name.
    If this is possible is there any limit to the number of keywords I can programme the macro to filter?
    Thanks very much in advance.

  • Great job once again Jon.

    I do not find the excel you prepared attached anywhere here for me to download.

    please help

  • Hello Jon,

    I went through the webinar, It is really very interesting and knowledgeable.

    I am pleased to learn from you as I find you very clear and helpful towards teaching with clear goals.

    I need some help in writing a specific macro for my process. It is easy but i dont know how to do that. Can you please guide me in doing that so that i learn from you and teach others on behalf of you.

    Please revert back to me through email – if you are okay to extend the helping hand.

    Thank you very much.

  • Hi,

    I wanted to use a Checkbox instead of a Button so that it works the same way when its Checked and returns back to the Start when Unchecked.


Generic filters
Exact matches only
Filter by Custom Post Type


Learn 10 great Excel techniques that will wow your boss and make your co-workers say, "how did you do that??"
Plus weekly updates to help you learn Excel.

Download the eBook

About Me

Jon Acampora Profile

Hello and welcome! My name is Jon Acampora and I'm here to help you learn Excel.

This blog is updated frequently with Excel and VBA tutorials & tools to help improve your Excel skills and save time with your everyday tasks. Subscribe above to stay updated. More about me...