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:
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:
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:
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.
If we use the F9 key to successively evaluate parts of the function it looks like this:
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.
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.
'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
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
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:
…use this:
And instead of this in column E of the data tab:
…use this
And instead of this in column F of the data tab:
…use this:
(Doesn’t need to be array entered).
And instead of this in column H
…use this:
Thanks Jeff. Next time I’m doing something like this I’ll come back and try your suggestions.