Filter Pivot Tables Using Source Data Helper Columns

Filter Pivot Tables Using Source Data Helper Columns

When working with pivot tables you often need to filter out certain items. You can of course do this directly in the fields’ filter dropdowns, and often that’s good enough. But if you change the filters repeatedly this can be tedious and error-prone. For this reason, and to make my workbooks more flexible and maintainable, I often add helper columns to the source data. This post explains three types of source data helper columns that I use to filter pivot tables.

The live workbook below contains a simple data set of All-Star baseball games, where they were held, and which league won. It also contains three helper columns, each using a different method to determine the rows included in the pivot table. All three techniques refer to tables that spell out the criteria for inclusion.


(You can switch tabs, edit cells and refresh pivot tables. Reloading this page reverts it to its starting state. Note the buttons for downloading or opening in a full web page.)

You can click into the formulas in the helper columns and see that they are referring to tables – a different one for each column. Each formula uses a COUNTIF or COUNTIFS function to determine if the venue for that row meets certain criteria.

When you click on the “pivot and helper tables” tab you’ll see the pivot table on the left and the three helper tables on the right. The pivot table has a report filter for each of the three helper columns. Note that the report filters, table headers and helper columns are color-coded to show which ones go together.

Okay, let’s look at the three methods in order.

Table Containing Only Values to Include

Back on the data tab, the helper column “Venue In Table” looks at the first table, the one titled “Venues to Include”. The formula is a simple one:

=COUNTIF(tblVenuesToInclude[Venues to Include],[@Venue])>0

It returns TRUE if the ballpark for that row is found in the table. Its report filter is already set to TRUE in the pivot table, so you can test it by adding or deleting a stadium name in the table, right-clicking the pivot table and clicking “refresh”.

Table With All Values and an “Include” column

The second helper column/table pair are very similar, but instead of a table listing only the baseball fields you want included, you list all of them and add another column that contains TRUE if the ballpark should be included. I might use this method if I had a table with all the venues that I was already using for another purpose:

=COUNTIFS(tblVenuesIncludeCol[Venue],[@Venue],tblVenuesIncludeCol[Include],TRUE)>0

The COUNTIFS formula checks both the Venue and the Include columns. (You can use a SUMPRODUCT formula if you’re using Excel 2003 or earlier). You can test this formula by changing its report filter to TRUE and then changing the values in the Include column of the 2nd table.

Table Containing Words to Look For

The final helper column is “Venue Word in Table”. It looks into the “Word to Find” table and uses a COUNTIF array formula that searches the venue’s name for words from the table. Changing the report filter to TRUE will find the Kingdome, Polo Grounds and any park whose name contains “field”.

Here’s the formula, entered with Ctrl-Shift-Enter:

{=MAX(COUNTIF([@Venue],"*" & tblWordToFind[Word to Find] & "*"))>0}

This formula takes advantage of the ability to use wildcards in COUNTIF functions, by putting asterisks before and after the table column reference. Being an array formula, it tests the venue name against each word in the “Word to Find” table. The 1934 data row returns TRUE because “Polo Grounds” contains the word “polo”, which is in the table.

helper 3 and table

If we use the F9 key to successively evaluate parts of the function it looks like this:

=MAX(COUNTIF([@Venue],"*" & tblWordToFind[Word to Find] & "*"))>0
=MAX(COUNTIF([@Venue],"*" & {"king";"polo";"field"} & "*"))>0
=MAX(COUNTIF("Polo Grounds","*" & {"king";"polo";"field"} & "*"))>0
=MAX({0;1;0})>0
=1>0
TRUE

One final note: In actual practice, I always put the helper tables on a separate sheet, not next to the pivot table as done here. Expanding pivot tables, among other things, makes this layout impractical in real use.

6 thoughts on “Filter Pivot Tables Using Source Data Helper Columns

  1. Hi Doug. Here’s some code I wrote to do filter a pivotfield programatically based on an outside range.It’s very quick on small pivots, but if your pivotfield has say 10,000 items in it it will likely take half a minute, and if your pivotfield has say 20,000 items it will likely take a minute and a half.

    I’ve got some ‘top secret’ code I’ve written that uses a very creative approach, and only takes a second or two for suh big pivots in most cases. Can’t share, because I plan to turn this into a commercial add-in.

    Sub FilterPivot()

    'Code by Jeff Weir
    'email jeff.weir@heavydutydecisions.co.nz
        Dim pt As PivotTable
        Dim pf As PivotField
        Dim pi As PivotItem
        Dim pis As PivotItems
        Dim rngFilterItems As Range
        Dim Time_Taken As Date
        Dim strMessage As String
        Dim varPI As Variant
        Dim lFilterItems As Long
        Dim lFound As Long
        Dim bFinished As Boolean

        On Error GoTo ErrHandler

        On Error Resume Next
        Set pf = ActiveCell.PivotField
        Set pt = ActiveCell.PivotTable

        If Err.Number <> 0 Then    'Selected cell is in a pivotfield, so we can't continue
            On Error GoTo ErrHandler


            Err.Raise 999, "No PivotField Selected"
        End If
        On Error GoTo ErrHandler

        If ActiveCell.PivotField.Orientation = xlDataField Then    'Can't filter datafields, so can't restore filters either
            Err.Raise 998, "Can't filter Datafields"
        End If



        'Prompt user for SearchTerms table related to the pivotfield of interest
        On Error Resume Next
        Set rngFilterItems = Application.InputBox(prompt:="Please select the range where your filter terms are", Title:="Where are the filter items?", Type:=8)
        Err.Clear
        On Error GoTo ErrHandler
        If rngFilterItems Is Nothing Then Exit Sub



        'Turn off calculation and screen updating
        Application.ScreenUpdating = False
        Application.Calculation = xlCalculationManual

        pt.ManualUpdate = True    'Dramatically speeds up routine, because Pivot doesn't recalculate after each item is hidden.

        Time_Taken = Now()

        Set pis = pf.PivotItems
        If Not pf.AllItemsVisible Then pf.ClearAllFilters
        lFilterItems = rngFilterItems.Count

        On Error Resume Next
        For Each pi In pis
            If bFinished Then    'We've found all items, so can just blindly hide all remaining
                pi.Visible = False

            Else:
                'For some reason, if pi is a number, then even though IsNumeric(pi.Value) = True,
                ' pi.Value is treated as a string during the check Application.Match(pi.Name, rngFilterItems, 0)
                ' and as such will not be found if rngFilterItems contains the number equivalent.
                'So I have to test if pi.value is numeric, then explicitly cast it as a long.
                If IsNumeric(pi.Value) Then
                    varPI = CLng(pi.Value)
                Else: varPI = pi.Value
                End If
                If Application.Match(varPI, rngFilterItems, 0) = "Error 2042" Then
                    pi.Visible = False
                Else:
                    lFound = lFound + 1
                    If lFound = lFilterItems Then bFinished = True
                End If
            End If  ' If bFinished Then
        Next pi
        On Error GoTo ErrHandler

        Application.ScreenUpdating = True
        Application.Calculation = xlCalculationAutomatic
        pt.ManualUpdate = False


        Time_Taken = Now() - Time_Taken
        strMessage = "Match_Function_Iteration completed " & vbNewLine & vbNewLine & _
                     "Time Taken: " & Format(Time_Taken, "HH:MM:SS")


        Debug.Print strMessage
        MsgBox strMessage

    ErrHandler:

        Select Case Err.Number
        Case Is = 0:    'No error - do nothing
        Case Is = 998:    'Can't filter Datafields
            MsgBox "Oops, you can't filter a DataField." & vbNewLine & vbNewLine & "Please select a RowField, ColumnField, or PageField and try again.", vbCritical, "Can't filter Datafields"
        Case Is = 999:    'no pivotfield selected
            MsgBox "Oops, you haven't selected a pivotfield." & vbNewLine & vbNewLine & "Please select a RowField, ColumnField, or PageField and try again.", vbCritical, "No PivotField selected"
            '   Case is = #:    'Add specific error handling here

        Case Is = vbIgnore: Resume Next

        End Select
    End Sub
  2. Forgot to subscibe to comments. Am leaving this 2nd comment purely so I can subscribe. You got a dedicated comments feed for this blog, Doug?

    • Jeff, thanks for sharing your second-rate code with us :). Just kidding, good luck with the addin.

      There’s a comments RSS link at the top-right of most pages, like “blog”, just not on the individual post pages.

    • Hi Jeff,
      I need assistance , I have a pivot table data from cube that I need to filter the date base on two date field outside the pivot (Begin Date and End date)
      What can you do to assist me.
      Thank you

  3. Doug: It strikes me you could replace those COUNTIFS with a combination of SEARCH, INDEX, MATCH, and LOOKUP that would be much quicker.

    So instead of this in column D of the data tab:

    =COUNTIF(tblVenuesToInclude[Venues to Include],[@Venue])>0

    …use this:

    =NOT(ISERROR(MATCH([@Venue],tblVenuesToInclude[Venues to Include],0)))

    And instead of this in column E of the data tab:

    =COUNTIFS(tblVenuesIncludeCol[Venue],[@Venue],tblVenuesIncludeCol[Include],TRUE)>0

    …use this

    =IFERROR(INDEX(tblVenuesIncludeCol[Include],MATCH([@Venue],tblVenuesIncludeCol[Venue],0)),FALSE)

    And instead of this in column F of the data tab:

    =MAX(COUNTIF([@Venue],"*" & tblWordToFind[Word to Find] & "*"))>0

    …use this:

    =ISNUMBER(LOOKUP(6^5,SEARCH(tblWordToFind[Word to Find], [@Venue])))

    (Doesn’t need to be array entered).

    And instead of this in column H

    =COUNTIF([@Venue],"*o*")=0

    …use this:

    =IF(ISERROR(SEARCH("*o*",[@Venue])),TRUE,FALSE)

Leave a Reply to AChuks Cancel reply

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

To post code, do this: <code> your vba here </code>