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
                Else
                    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!

54 comments

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

  • Hi Jon

    I am working on a kpi report for our customers. The report has a Dashboard. Slicers control the various options by which the user can filter the data (eg. Year, Month, Transport Mode etc) but I would also like users to have the option of how to view the data i.e Graphs or Data – I believe the above code would be useful to do this. However, rather than using a header row, can I do this by using a named range instead?

    Eg. Currently the graphs are from A11-M82 and the range is named as “Graphs”
    The data tables are from A83-N351 and the range is named as “Data”

    The entire dashboard range is A11-O351 and is named as “Dashboard”

    I have 2 slicers at the top of the dashboard with View options I.e. Data or Graphs. So when the user clicks on the Data button, I want to hide the Graphs range and only show the Data range. Similarly when the user clicks the Graphs button, I want to hide the Data range and show only the Graphs range. Can this be done using the above code? I created the Pivot table View with the options Data and Graphs but as I am new to vba I do no understand how to manipulate the code to look for named ranges instead of individual items in a header row so appreciate your suggestions.

    Thanks!

  • Hello Jon,

    First: This is fantastic, thank you for this!
    Second: I am a novice, how can I modify this to work with dates? I don’t think that format works with this code. I would like to add about 4 slicers to work with dates while keeping 2 working with names.

    Thanks!

  • Hi Jon

    I had my sheet set up perfectly with the above code – THANKS. However now I’m trying to do the same – but for rows.

    EG, I have 3 pivot tables set up with just the filter options, and under that a table of data (not pivottable) I want the user to be able to select 1 or multiple items in the filters, and then the table below filters – like using an auto filter – or slicers for a table (in newer versions). Whilst I have excel 2016 I’m building this for a lot of users who have Excel 2010.

    Thanks!

  • Jon,

    I’ve implemented your column hide macro on a spreadsheet that deals with data that has a month header row and a year header row. I’ve been able to get the month filter to work, but I am not able to get the year filter to work and I don’t understand why as the ranges are set up the same and the pivot tables are set up the same. Is there any chance I could send my spreadsheet to you to help me figure out why the year filter isn’t working? Thanks. Mike

  • 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?

  • 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.

      • Hi again Jon, Cheeky follow-up question, if you don’t mind!

        Would there be a way to modify the macro to automatically apply a row height autofit to the report each time a user changes which columns they’re choosing to display (I.e. autofitting based on the columns that the macro is unhiding)?

        Thank you, Matt

  • 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!

    Dave

  • Hi,

    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?

    Thanks
    Paul

  • Hi,

    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
    TS

  • 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.

  • 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!

      • 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?

        =SUMIFS(D76:EC76,$D$73:$EC$73,$E$73)

  • 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!

  • 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,
    Sofía

    • 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.

  • 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

  • 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.

  • 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?

  • 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?

    Regards
    Eusebio

  • 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.

  • 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?

    • Bas,

      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.

  • 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.

    • 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.

  • Hi,

    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 ?
    Regards,
    Logonna

  • Hi,

    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,
    Like

    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”

  • 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?

    Thanks!

  • 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!

    • 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.

  • 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

    • 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.

  • Jon,

    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?

  • 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.

    • 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!

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

  • 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

    • 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!

      • 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!

JOIN US & LEARN EXCEL

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...

MVP_Horizontal_BlueOnly