Using Cell Text to Filter PivotTables


Sometimes it would be useful to be able to use cells to select items in a PivotTable. For example, let’s say we have a PivotTable containing a field called Region. Instead of using the filter dropdown to select the region from a list it would be nice if we could use a cell to specify the region. That way, the user could just type a region, such as Europe, into a cell and the PivotTable would only show data for Europe.


In this post, I’ll present some VBA code that does just this.


Let’s take an example.


We have a PivotTable based on some census data in our workbook.  We have Occupation on rows and Education Level on columns and our measure is average age. So we are looking at the average ages of different occupations and education levels.



We then want to filter this data by region. So we add the Region field to report filters. Now we can see the different values for different regions by changing the in-built filter dropdown.



But let’s say that, instead of using the filter dropdown, we want the PivotTable to get the region selection from a specific cell. In other words, we want the user to be able to type Europe into a cell and the PivotTable updates to show the value for Europe, just as if the user had used the filter dropdown to select Europe.


To do this, we need to do two things:




  1. Detect when the user has entered a value into the cell;


  2. Update the Region PivotField object with the value of the cell;

For the sake of clarity, let’s assign a name to the cell where the user will enter the new selection for the field (i.e. let’s make the cell a named range). Since we’re changing the Region field, let’s give the cell a name of RegionFilterRange.


To detect when RegionFilterRange has changed, we handle the Workbook.SheetChange event. This event passes in (as a parameter) a Range object which represents the changed range. If this range intersects with the RegionFilterRange then we know that RegionFilterRange has changed.


Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Not Intersect(Target, Application.Range(RegionRangeName)) _
        Is Nothing Then
            UpdatePivotFieldFromRange _
            RegionRangeName, PivotFieldName, PivotTableName
    End If

End Sub 


PivotFieldName is the name of the the field we are updating (in this case Region) and PivotTableName is the name of the PivotTable we are changing.


The code for updating the PivotField object is implemented in the function UpdatePivotFieldFromRange. I’ve made the code generic so that it can update any field in any PivotTable (in the active workbook) to the value of a specified range (these three things are passed in as parameters).


Public Sub UpdatePivotFieldFromRange(RangeName As String, FieldName As String, _
PivotTableName As String)


    Dim rng As Range
    Set rng = Application.Range(RangeName)
   
    Dim pt As PivotTable
    Dim Sheet As Worksheet
    For Each Sheet In Application.ActiveWorkbook.Worksheets
        On Error Resume Next
        Set pt = Sheet.PivotTables(PivotTableName)
    Next
    If pt Is Nothing Then GoTo Ex   
    On Error GoTo Ex
   
    pt.ManualUpdate = True
    Application.EnableEvents = False
    Application.ScreenUpdating = False
   
    Dim Field As PivotField
    Set Field = pt.PivotFields(FieldName)
    Field.ClearAllFilters
    Field.EnableItemSelection = False
    SelectPivotItem Field, rng.Text
    pt.RefreshTable
   
Ex:
    pt.ManualUpdate = False
    Application.EnableEvents = True
    Application.ScreenUpdating = True
   
End Sub


Public Sub SelectPivotItem(Field As PivotField, ItemName As String)
    Dim Item As PivotItem
    For Each Item In Field.PivotItems
        Item.Visible = (Item.Caption = ItemName)
    Next
End Sub


UpdatePivotFieldFromRange first of all gets a reference to the required PivotTable and stores it in pt. Then it gets a reference to the required PivotField and stores that in Field.


pt.ManualUpdate = True stops the PivotTable from automatically updating as we make changes. It basically means that we have to tell the PivotTable when it should update. Application.EnableEvents = False stops events like Workbook.SheetChange from firing while our code is executing.


SelectPivotItem loops through all the items in the field and sets each one’s Visible property to False except for the item specified by ItemName which is set to True. This ensures that only ItemName will be visible in the field.


Notice that in UpdatePivotFieldFromRange we set Field.EnableItemSelection = False. This hides the dropdown filter window from the user so they can’t click on the PivotTable and change the field selection using the filter dropdown list.


So, our code now enables us to enter an item name in the RegionFilterRange named range and use that value to filter our PivotTable.



It doesn’t matter where the Region field is. It could be on Report Filters, Columns or Rows.  In the following screenshot, we have moved Region to Columns.



Summary




  • Cells can be used to filter PivotTables by programmatically setting the field selection to the value of the cell;


  • Use the Workbook.SheetChange event to detect changes to the cell;


  • Set Application.EnableEvents = False and PivotTable.ManualUpdate = True to avoid Excel events from firing and the PivotTable automatically updating itself while your code exeutes;

I have only talked about PivotTables that are based on data inside Excel. This code won’t work for OLAP PivotTables. I might post a OLAP version in a later article if there is interest. 


Download the Source Code 


An example Excel 2007 workbook containing the VBA, data and PivotTable for this post has been published to the MSDN Code Gallery and can be downloaded by clicking on the link below (go to the Releases tab to download the workbook). 


Click here to download


 

Comments (10)

  1. Jon Peltier says:

    Why can’t you display code as text? The picture is truncated in the display, and it can’t be pasted into a module.

  2. Gabhan Berry says:

    Hi Jon,

    Sorry about that … I’ve changed the code snippets to text.

  3. Colin Banfield says:

    I’ve used a similar technique for some client reports and it’s great to compare other code approaches.  I’ve never bothered setting manual update for pivot tables but it’s perhaps a good idea.  Here’s one example of how I’ve used this filtering technique in a report:

    This specific client prepares a monthly report for its major customers.  On the report summary sheet (among other things), there is a drop down list which is used to select the customer.  The selection triggers a series of actions: 1) a query table, which provides detailed monthly sales activities, uses the drop-down selection as a parameter input and refreshes the query table accordingly; 2) the selection is used to filter a pivot table that summarizes year-to-date sales;  3) The selection is used to filter a pivot table that summarizes year to date delivery performance (early, on time, late); 4) The selection is used to put the appropriate title on associated pivot charts.

    One area where I’m finding that I have to create a lot of pivot table code is in custom calculations.  Essentially, I create a calculated range adjacent to pivot table and the range adjusts as the pivot table expands or collapses.  The range attempts to match the pivot table format so that it appears to be part of the pivot table.  These calculated ranges are necessary to work around many limitations in pivot table calculated fields, such as:

    1) Inability to create a calculated field based on the various summary calculations in other fields e.g. suppose you have a field named Ave Standard Cost and another field named Ave Actual Cost.  Both fields use an average summarization. You can’t create a calculated field like (Ave Standard Cost – Ave Actual Cost).

    2) Inability to use external references in calculated fields.

    3) Inability to create a calculated field based on some criteria e.g. IF(Qty>10,…,…).

    Of the above, (2) is usually the biggest problem.  In many cases, the other two problems can be addressed by creating calculated fields in the source sheet or external query.

    I’d be interested to know if you have any thoughts on the above.

    Colin

  4. Jon Peltier says:

    Gabhan –

    Thanks for providing the code as text. Such a simple change, such an increase in usability.

  5. Gabhan Berry says:

    Hi Colin,

    There’s a recent article over on the Excel team blog about custom calculations in PivotTables. It doesn’t cover all your points but it may be of some use to you.

    http://blogs.msdn.com/excel/archive/2008/02/05/common-questions-around-excel-2007-OLAP-PivotTables.aspx

    As for external references, by that do you mean using cell references in calculations (such as Sales Avg * A$1$)? or do you mean something outside of Excel?

  6. Colin Banfield says:

    "There’s a recent article over on the Excel team blog about custom calculations in PivotTables. It doesn’t cover all your points but it may be of some use to you."

    Yes, I read that article.  I found it very useful but it addresses issues specific to PTs created from OLAP sources.  I’m thinking about non-OLAP sources in this instance (e.g. PTs created from relational data sources)

    "As for external references, by that do you mean using cell references in calculations (such as Sales Avg * A$1$)? or do you mean something outside of Excel?"

    The former (external to the PT but within the same workbook).  One other thing I forgot to mention.  Another reason I’ve had to write code outside the pivot table is for  calculating running totals that run over multiple years.  A typical scenario would be to keep running totals of individual shipped product quantities over months and years. In the case of the PT running totals custom calculation, the best that you can do is use month as the base field but the running totals are reset at the beginning of each year.  That’s the nature of the beast.  

    Colin

  7. Vito says:

    Can’t get code to work in Excel 2003.  What am I missing?

    Thx.

  8. kelly says:

    may I ask a question?  I need to use pivot table to generate 12 month rolling data, is it possible to: every time, we open the file, the pivot table with use date ( one year ago)  — today as the filture range to do so?  for example, 2008/05/14 is : 33896,  how to add the date into the pivot table to generate automatically new data each day???

    thanks

  9. Gerry says:

    Having the same trouble as Vito – cant get it to work in Excel 2003.

    Have even gone as far as replicating the entire data set, recreating the pivot, defining the names etc and it just doesnt work. No error, just nothing.

    Your thoughts on getting it ticking?

    Cheers

  10. Jason says:

    Have you been able to modify the code for OLAP pivot tables?  If so, can you please post it.  Thanks.

Skip to main content