Hide & Unhide (Filter) Columns with a Slicer or Filter Drop-down Menu

Bottom line: Have you ever wanted to use a slicer or filter to hide/unhide columns on a regular worksheet range or table?  Normally our data has to be in a pivot table to make use of the column filtering capabilities, but this simple macro allows us to hide and unhide worksheet columns with a slicer or pivot table filter.

Skill level: Advanced

The Filter Columns Macro Thumbnail 640x360

Pivot table slicers and filters are a great way to hide and unhide columns in a pivot table.  This allows us to display only relevant columns for the current time period, project, category, etc.  Hiding and unhiding columns can help reduce horizontal scrolling, which can be time-consuming on large worksheets.

However, pivot tables aren't a good solution for all applications and reports.  We cannot use pivot tables for input sheets, and reports with customized or complex column structures.

So, I wrote this macro that combines the best of both worlds.  The Filter Columns macro allows us to use a slicer or pivot table filter to hide and unhide columns on a worksheet.  This means our data does NOT need to be in a pivot table to use the functionality of a slicer or filter for columns.

Example Uses for the Column Filter Macro

One simple example is a report with a month/quarter/year column structure, where you want to hide/unhide all month columns for a specific quarter.  This report might be a forecast/budget input sheet, or any worksheet where you would NOT use a pivot table.

The animated screencast below shows the macro in action.  The slicers are filtering (hiding/unhiding) regular worksheet columns.

Filter Columns with Slicer Macro - Quarterly Report Example

Click here to view the animation in your browser.

Another example is an inventory or time allocation sheet that has a lot of columns for projects, locations, regions, etc.

Filter Columns with Slicer Macro - Project Allocation Sheet Example

How Does the Filter Columns Macro Work?

The Filter Columns macro uses a simple pivot table for the interactive controls only.  That pivot table contains one field with a list of the unique values from the header row range (column criteria) for the report.

When the user clicks a slicer item or changes the pivot's filter drop-down menu, the Worksheet_PivotTableUpdate event is fired.  The event automatically triggers a macro to run any time a user interacts with a slicer or pivot table drop-down filter menu.  This is awesome because it gives us unlimited possibilities for using slicers as interactive controls.

The Filter Columns Macro Explained

The worksheet event then calls the Filter Columns macro.  The macro loops through each cell in the header row range (column criteria) and checks if that item is selected in the slicer/filter.  If the pivot item is checked (visible), then the column is made visible (unhidden).  If the item is not checked then the column is hidden.

The end result is similar behavior to what you would expect if you filtered the columns area of a pivot table.  However, our data does NOT need to be in a pivot table.  It can be in a regular worksheet range or Excel Table.

The Filter Columns Macro Code Setup

There are two macros at work here.  The first is the Worksheet_PivotTableUpdate event.  This macro runs whenever the user makes a change to the slicer or pivot table filter drop-down menu.  The PivotTableUpdate event is stored in the code module of the worksheet that contains the pivot table.

PivotTableUpdate Event in Sheet Code Module

The Filter_Columns macro contains parameters/arguments that are passed through from the PivotTableUpdate event.  These parameters specify the header row range, report sheet name, pivot sheet name, pivot table name, and pivot field name.  Using parameters allows us to reuse the Filter Columns several times throughout the workbook.

Here is the code for the PivotTableUpdate event:

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
'Run the filter columns macro when the pivot table or slicer is changed
    Select Case Target.Name
        Case "PivotTable1"
            Call m_FilterCol.Filter_Columns("rngQuarter", "Report", "Report", "PivotTable1", "Quarter")
    End Select
End Sub

Here is Filter_Columns macro code.  The macro is located in the m_FilterCol code module in the example workbook file.

Sub Filter_Columns(sHeaderRange As String, _
                    sReportSheet As String, _
                    sPivotSheet As String, _
                    sPivotName As String, _
                    sPivotField As String _

Dim c As Range
Dim rCol As Range
Dim pi As PivotItem
    'Unhide all columns
    Worksheets(sReportSheet).Range(sHeaderRange).EntireColumn.Hidden = False
    'Loop through each cell in the header range and compare to the selected filter item(s).
    'Hide columns that are not selected/filtered out.
    For Each c In Worksheets(sReportSheet).Range(sHeaderRange).Cells
        'Check if the pivotitem exists
        With Worksheets(sPivotSheet).PivotTables(sPivotName).PivotFields(sPivotField)
            On Error Resume Next
            Set pi = .PivotItems(c.Value)
            On Error GoTo 0
        End With
        'If the pivotitem exists then check if it is visible (filtered)
        If Not pi Is Nothing Then
            If pi.Visible = False Then
                'Add excluded items to the range to be hidden
                If rCol Is Nothing Then
                    Set rCol = c
                    Set rCol = Union(rCol, c)
                End If
            End If
        End If
        'Reset the pivotitem
        Set pi = Nothing
    Next c
    'Hide the columns of the range of excluded pivot items
    If Not rCol Is Nothing Then
        rCol.EntireColumn.Hidden = True
    End If
End Sub

How Do I Setup Filter Columns in My Workbook?

I included setup instructions in the example Excel file that you can download below.

Setup Instructions for Filter Columns Macro

There are about six steps to setting this up in your own workbook.  You should have knowledge of how to use and modify macros.  If you are new to macros, checkout my free 3-part video series on getting started with Macros & VBA.

I also have an article and video on how to run event based macros when the user takes an action in the workbook.

Download the Example File

The example file contains the setup instructions, macros, and the two examples I showed above.

Filter Columns Macro Examples.xlsm (36.5 KB)

Here is another example of using multiple slicers on the same sheet, based on a question from Michael in the comments below.

Filter Columns Macro Examples - Multiple Slicers.xlsm (41.6 KB)

What else can we use this for?

I feel like there are a lot of possibilities with this macro, and the use of the slicers or pivot filter drop-down menus as interactive controls.  What will you use this for?  Please leave a comment below with suggestions or questions.  Thanks!

Please share
Jon Acampora

Welcome to Excel Campus! I am excited you are here. My name is Jon and my goal is to help you learn Excel to save time with your job and advance in your career. I've been an avid Excel user and VBA developer for 10+ years. I am also a Microsoft MVP. I try to learn something new everyday, and want to share this knowledge with you to help you improve your skills. When I'm not looking at spreadsheets, I get outdoors and surf. :) more about me

Click Here to Leave a Comment Below 47 comments
Mike - November 28, 2018

With the Worksheet_PivotTableUpdate code, the m_FilterCol.Filter_Columns list 5 items:

(“rngQuarter”, “Report”, “Report”, “PivotTable1”, “Quarter”)

Where does “rngQuarter”, “Report” come from?

Thank you!

I am trying to use locations instead of quarters but my column header is a merged cell and I guess that might cause issues?

Matt - October 18, 2018

Thank you, this is awesome! I have basically no VBA experience, but your instructions were so clear that even I managed to get this working in my workbook.

David O. - October 4, 2018

Hi Jon,

This looks amazing and I can’t wait to use it… just hoping it will work for my needs.

Rather than filtering by Quarters, I need to filter my columns by location (i.e. Hospital, Home, Rehab). Some columns though, I always want visible (i.e. Last Name, First Name, DOB etc.) no matter which location I’m showing the columns for… and these columns will not all be beside each other.

Can this be achieved using the Slicer as well? What code would I need to modify and how?

Thank you very much!

Looking forward!


Paul Muldoon - July 11, 2018


This seems to work fairly well, and I understand most of code working to create a hide/unhide columns script myself.

But I am having trouble with one piece so far and that this choosing “All” in the filter. When I chose All, all of the columns get hidden. The work around so far is to Select Multiple Items and choose them all, but shouldn’t you be able to check choose All by itself?


TS - July 3, 2018


Thank you for this – it really helps me do what I needed. However, is it possible to adapt the code so that if I copy a sheet multiple times (we have a weekly sheet which we fill in), it updates the code. Right now, I have seen that it is linked only to the sheet titled “Report”. However, If I copy this sheet and then change it to Report 2, the code will not work.

Appreciate your help with this!

Kind regards

Zachie Rojas - June 10, 2018

I am having trouble getting this to work. I get a 424 error for: Call m_FilterCol.Filter_Columns(“rngPracticeName”, “Insurance”, “Insurance”, “PivotTable4”, “Practice”)

I labeled my columns rngPracticeName. The sheet where the data and PivotTable are located is called Insurance.
Name of PivotTable is PivotTable4 and the field name is Practice.

I placed the code in the Insurance Sheet. I don’t know what object I am missing.

Anna - May 28, 2018

Hi Jon

Just come across this – and it looks exactly what I’m after … BUT
my header fields are merged cells – can this still work for that?

Thanks heaps in advance!

    Jon Acampora - May 29, 2018

    Hi Anna,
    It’s best to unmerge those cells first. VBA doesn’t really like merged cells…

    I hope that helps. Thanks again!

      Anna - May 29, 2018

      Thanks Jon – have it working perfectly, just added some extra rows for the header information.

      Now I am trying to add Totals, and averages for just the columns that are shown with the filters – however I need to also do a sumifs

      This is my SUMIFS formula, however I only want it to calculate the visible columns, is this possible?


        Jon Acampora - June 1, 2018

        Hi Anna,

        You can use a combination of the SUMPRODUCT and SUBTOTAL formulas for this. Here is a post from my friend Dave that explains more.


        The post is for Count, but you can just change the SUBTOTAL function from 103 to 109 for Sum. I think the rest of the formula should be the same.

        I hope that helps get you started.

Dante - April 13, 2018

HI there again Jon,

This is just what i need!, thanks for sharing!

But I just cannot make it work!

Is there a way I can send you the workbook so you can point me in the right direction?

Thank you again!

Sofía - March 28, 2018

Hi Jon,

I have used your macro and it works wonderfully. The only thing is that I’ve been trying to adjust it and have been unable.

I have 65 sheets that have the same exact format. And I would like to have an only pivot table that when I change it, it filters the columns in all those sheets. The problems that I have encountered is that I always have to change the VBA code with each sheet name, define names for the column range, pivot table name to make it work and to connect all the pivot tables through a slicer with pivot table connections.

Hope you can help me.

Best regards,

    Jon Acampora - May 29, 2018

    Hi Sofia,

    The macros could be modified to loop through all sheets in the workbook and run the code on sheets that contain the report. You could test to see if a sheet has a specific value in a specific cell to determine if it is a report sheet. This would be done with an If statement in the code.

    You can also use regular range references “B2:B10” instead of named ranges.

    In this case you would want to use the Workbook_SheetPivotTableUpdate procedure in the Thisworkbook module of the workbook, instead of a specific sheet’s code module. This will run the macro when any pivot table in the workbook is updated.

    I hope that helps get you started.

Zach - March 12, 2018

How do you hide columns based on the slicers button that is clicked ?
I am using slicers to filter the data (Slicer 1: Exam Year, Slicer 2: Exam’s name) and I need to hide /unhide columns within the pivot based on which slicer is selected

So for example: when I click for ‘2016’, i want columns C to E to hide and columns F to H to be displayed. If I click on “maths”, i want columns f to h to hide and for columns c to e to be displayed.

Thanks again

Suril - January 12, 2018

Thanks Jon for this code. I didnot know this function even existed in excel until you mentioned this. However my concern is I want to hide rows instead of column and if you can make a video about it that will be a great help. Thanks in advance. Keep doing the great work you are doing.

Anthony - November 27, 2017

Thanks for this tutorial.

How do I apply this VBA code to all worksheets and subsequent worksheets I create without having to update the code every time?

Eusebio Gomez - October 31, 2017

Excelent work Jon!

I am trying to connect the slicer to a pivot table and also to the records table that the pivot feeds from so that every time the pivot is updated I can also see the filtered records that make up that selection but there seems to be no way to do this. By any chance would you happen to know of a way to do this?


Andres - October 29, 2017

Hi Jon,
Thanks for this macro and technique. It’s been really helpful. I’m looking for a way of doing the same but using a timeline instead of a slicer. In this way, I could choose different ranges of dates: either months, quarters, years, etc. I hope you understand what I’m trying to do and I also hope there is a way.

Bas - August 17, 2017

Hi Jon,
Thanks for the macro but I still have problems to implement it in my excel file. The problem i observe is with the “rngQuarter”. What do i have to do to my header row so it will be recognized as rngQuarter? Also when i use a manual rang like A1:HM25 i see the macro doesn’t filter out columns where the cells are merged. Is there a workout?

    Tom - September 22, 2017


    Highlight the cells in your header row that you want to be considered by the function for filtering. Right click -> Define name -> This is where you can type in the equivalent to “rngQuarter”. Go to the Formulas Ribbon -> Name Manager. Here you can validate if your version of “rngQuarter” has been defined.

    In Jon’s excel file he attached to this post, you can go to the Name Manager and see “rngQuarter” listed there.

Sakiko - August 13, 2017

Hi Jon,
Thanks for sharing this technique! I am using your codes to hide and unhide columns based on selections made in multiple slicers. It takes 3 seconds for updating slicer selections for a table with 3000 lines and I am wondering if there are any technique I can use to significantly reduce the time taken to run these codes. I set Application.ScreenUpdating = False
to stop flickering of the screen when the codes are being run.
Whilst the time taken to update slicer selections is quite reasonable for my own use, I am sharing the file with several other people, and I’d like to reduce the code running time as much as possible for a better user experience. I am wondering if a looping could be consuming time… I’d greatly appreciate your suggestions on this matter.

    Sakiko - August 14, 2017

    Hi Jon,

    Subsequent to my message last night, I figured out the way to reduce the code running time of the hide & unhide VBA. In order to improve the speed, I used a combination of excel and VBA interface. First, get a range of columns to hide using the Excel text formula and then reference that cell in the VBA code.
    Range(Range(“D19”).Value).EntireColumn.Hidden = True

    Having made this change, the code running time has been reduced from the previous 3 seconds to 1 second.

    Thank you very much for the great post which got me thinking on this.

Logonna - June 13, 2017


I have a question in this context.

I managed a file (checklist) used by different people where are listed lots a items (in sveral sheets).
In each sheet, certains items concerned the whole group but some of them concern specific groups. (Precision : all items for group A, some items for group B, others (different than linked to B) for Group C, … It can have until 7 groups. Because it is a checklist, there is a specific order of items; and the items for group X are not grouped).
Because list of items are important by sheet, it is asked to hide not applicable items. For example, items of group A must be seen (if A selected), all the others must be hidden.

I need to use autofilter. But I don’t know how to define rows to be hidden.
I tried to dim RowUp and RowDown the 1st and last lines under filter line. Then I tried to define range(RowUp:RowDown).Select and the function to hide. The problem is to set the “range(RowUp:RowDown).Select”… Could you help me to solve it or to propose another solution ?



I have a query.

I want to consolidate some monthwise data from 15 sheets to one sheet.
I want to consolidate one by one data in one sheet of respective month

How to create macro for this.

I’m trying to record macro but I’m facing some issues,

My consolidation sheet is “Sheet1”

To copy current month’s data from “sheet 2” I need to filter for specific month then select some columns & need to copy & paste in “sheet 1″.

This thing works perfectly.

But problem is that how to copy data of” sheet 3, 4,5&so) in “Sheet 1”

Timo - February 26, 2017

Hi Jon,

Thanks for all the info! I was wondering if there is an easy way to add multiple filters for each column.

For example I have a list of projects (across the columns) which I want to filter by fee total and type of contract. Is there an easy way to do this?


Michael - January 19, 2017

Hi Jon,

This is a fantastic tool; thank you so much! Question for you: how would I go about setting up two of these in the same worksheet? I have 50 different entities with 12 different items I am looking at over the course of 15 years.

Thank you again!

    Jon Acampora - January 20, 2017

    Thank you Michael! You can setup multiple slicers in the sheet. I added a file in the downloads section above that does this. It basically calls a macro to unhide all the rows first, then loops through each pivot table on the sheet and runs the Filter_Columns macro for each pivot table.

    If your sheet has other pivot tables that you do not want to run the filter on, then you can add an IF statement with some logic to test if the pivot table name starts with a prefix that you want to use for the pivot tables that apply filters. The Instr function can be used to test this, or the Left function.

    I hope that helps. Let me know if you have any questions.

Hampus - January 16, 2017

Hi, I get a runtime error when I click on the slicer.

Application-defined error or object defined error

When I try to debug it highlights this code

Worksheets(sReportSheet).Range(sHeaderRange).EntireColumn.Hidden = False

    Jon Acampora - January 20, 2017

    Hi Hampus,
    That is probably due to the variables for the report sheet (sReportSheet) and header range (sHeaderRange) not being set. You will need to change these in the Worksheet_PivotTableUpdate event code to match the names of your sheet and range reference.

    The following line of code calls the Filter_Columns macro and sets those parameters with sReportSheet = “Report” and sHeaderRange = “rngQuarter”. You will need to change these to match the name of the sheet that your pivot table is on, and the range reference to the range where the filter criteria are stored. I use a named range for this (rngQuarter) but you can use a regular range reference as well (“A1:F1”). The problem with the regular range reference is that you will need to change the code every time you move criteria range on the sheet. Named ranges help prevent this maintenance and errors.

    I hope that helps.

    I hope that helps.

AL - December 12, 2016


Thanks so much for posting this! I have been stumped on my current project and this seems to be the fix!

Any chance of a video being made for this one?

    Jon Acampora - December 19, 2016

    Thanks Al! Yes, I will add a video for this to my to-do list. 🙂

      Aris - January 7, 2017

      This is a great idea. I can’t wait for the video 🙂

Raheel Raaj - September 7, 2016

Thank you for sharing Jon its really a great Tip…

Tri Teguh - September 7, 2016

Hai Jon,

Thanks, is very helpfull

Chaminda Basnayake - September 7, 2016

Hi Jon,

Thank you for sharing, This is great.


Todd M. Paxman - September 7, 2016

This is great! Seems you could also use this along two or total columns: one that totals all columns (or by quarter, by year, etc.), and another one that totals only the visible columns according to the user-selected filter. Then the selection could be used to include or exclude certain months, categories, etc. from the subtotals/totals from the evaluation.

    Jon Acampora - September 7, 2016

    Hi Todd,
    Great suggestion! Since we can’t use the SUBTOTAL or AGGREGATE functions to ignore hidden columns, we could use your approach to display subtotals for the visible columns only. Thanks!

    Anna - May 29, 2018

    Hi Todd. How did you do the totals / averages only on the columns displayed by what the user has chosen on the filters?

Mark Howell - September 7, 2016

Hi Jon,

I love the possibilities for this! I can’t wait to put it into use. I did notice that the downloaded file does not show the Q1 – Q4 headers in Step 4. Instead, it shows ellipses (three dots). Any ideas how to fix that?

Thanks again,
Mark in Austin

    Jon Acampora - September 7, 2016

    Hi Mark,
    Are you using a Mac version of Excel by chance? Unfortunately, these macros will not work with the Mac versions of Excel. Excel 2011 for Mac does not support slicers, and Excel 2016 for Mac doesn’t seem to support worksheet events. The file should work on all Windows versions of Excel from 2007 to 2016. Let me know if you have any questions. Thanks!

    Daryl Duren - September 7, 2016

    Mark/Jon my file showed ellipses also. I resized the frame in rows C6:F6 to see the quarter names.

      Jon Acampora - September 7, 2016

      Thanks Daryl! I didn’t fully comprehend Mark’s comment. I will update the file and resize the slicer to make it larger.

      This can happen with Windows scaling and different monitor resolutions, but making the slicer slightly bigger should prevent most of those issues.

      Thanks again and have a good one!

Ben - September 2, 2016

Thank you for sharing !
What app do you use to make your nice gifs ?

    Jon Acampora - September 2, 2016

    Thanks Ben! I use Camtasia for the GIFs images and videos.


Leave a Reply: