Filter a Pivot Table or Slicer for the Most Recent Date or Period

Bottom line: Learn how to filter a pivot table, pivot chart, or set a slicer for the most recent date or period in the data set.

Skill level: Intermediate

How to Automatically Filter for the Most Recent Date in a Pivot Table or Slicer

Pip has a set of pivot table based reports that she updates frequently (daily, weekly, monthly).  She wants to automatically filter the reports for the most recent date in a column in the data set.  This filter will select an item in the slicer to filter the pivot tables and charts.

Like everything with Excel, there are a few ways to solve this problem.  We can use a macro, or add a calculated column to the data set…

Download the Example Files

The Setup: CRM Sales Pipeline Data

In this example we are going to use sales pipeline data.  The data table contains weekly snapshots or exports of the data from the CRM system (, Dynamics CRM, HubSpot, etc.).

To update the data we export a pipeline report every week, and paste the data to the bottom of the existing table.  There are other ways to automate this process, but we won't go into that here.

CRM Sales Pipeline Source Data Updated with Weekly Snapshots

The data set has a “Report Date” column that contains the date the report was run for each row.  You can see in the image that there are 4 sets of data appended (stacked) together to make one large table.

The pivot table shows a summary of revenue by pipeline stage, and there the Report Date field is in the Filters area.  This allows us to filter on any report date to see the pipeline summary for that week.

Pivot Table for Pipeline Revenue by Stage - Filter for Most Recent Period

When new data is added to the data table, we want to automatically filter all the connected pivot tables, charts, and slicers for the latest report date.

Solution #1: VBA Macro to Filter a Pivot Table for a Specific Date or Period

We can use a simple macro to set the filter in the pivot table for the latest date in the source data table.  Filtering the Report Date field in the pivot table will also select the filtered item in the slicer, and filter any connected pivot charts.

The macro below might look like a lot of code, but it is actually really simple.  To use it, you will just need to specify all the variables for the worksheet name, pivot table name, pivot field name (Report Date), and filter criteria (latest date).  These variables will represent the names of the objects in your own workbook.

If the macros look like a jumbled mess in you email client, click here to view in your browser.

Sub Filter_PivotField()
'Description: Filter a pivot table or slicer for a specific date or period

Dim sSheetName As String
Dim sPivotName As String
Dim sFieldName As String
Dim sFilterCrit As String
Dim pi As PivotItem

    'Set the variables
    sSheetName = "Pivot"
    sPivotName = "PivotTable1"
    sFieldName = "Report Date"
    'sFilterCrit = "5/2/2016"
    sFilterCrit = ThisWorkbook.Worksheets("Data").Range("G2").Value
    With ThisWorkbook.Worksheets(sSheetName).PivotTables(sPivotName).PivotFields(sFieldName)
        'Clear all filter of the pivotfield
        'Loop through pivot items of the pivot field
        'Hide or filter out items that do not match the criteria
        For Each pi In .PivotItems
            If pi.Name <> sFilterCrit Then
                pi.Visible = False
            End If
        Next pi
    End With

End Sub

The macro is currently setup to use a value from cell G2 on the Data sheet for the filter criteria (latest date).  Cell G2 contains a formula that returns the latest date from the column using the MAX function.

Calculate the Latest Date in the Column with the MAX Function

Of course, this could be changed to calculate the latest date in the macro code.  It's nice to be able to see it on the sheet though.

How does the macro work?

The macro first clears all the filters for the Report Filter pivot field with the ClearAllFilters method.

It then uses a For Next loop to loop through all the pivot items in the pivot field to apply the filter.  Each unique item in the field is a pivot item.  The macro checks to see if the pivot item name is NOT equal to (<>) the criteria.  If not, then it hides the item, or filters it out.  That leaves us with only the filter criteria being visible.

I have an article that explains For Next Loops in more detail.

Pivot Items in the Filter Drop-down Menu of a Pivot Table

What about filtering two pivot tables for different time periods?

Here is another example with the variables setup as parameters of the macro.  This allows us to call the macro from another macro.

Sub Filter_PivotField_Args( _
    sSheetName As String, _
    sPivotName As String, _
    sFieldName As String, _
    sFilterCrit As String)
'Filter a pivot table or slicer for a specific date or period

Dim pi As PivotItem
    With ThisWorkbook.Worksheets(sSheetName).PivotTables(sPivotName).PivotFields(sFieldName)
        'Clear all filter of the pivotfield
        'Loop through pivot items of the pivot field
        'Hide or filter out items that do not match the criteria
        For Each pi In .PivotItems
            If pi.Name <> sFilterCrit Then
                pi.Visible = False
            End If
        Next pi
    End With

End Sub
Sub Filter_Multiple_Pivots()
'Call the Filter Pivot macro on multiple pivots

Dim sFilter1 As String
Dim sFilter2 As String

    'Set the filter criteria
    sFilter1 = ThisWorkbook.Worksheets("Data").Range("G2").Value
    sFilter2 = ThisWorkbook.Worksheets("Data").Range("G3").Value

    'Call the filter pivot macro to filter both pivots
    Call Filter_PivotField_Args("2 Pivots", "PivotTable1", "Report Date", sFilter1)
    Call Filter_PivotField_Args("2 Pivots", "PivotTable2", "Report Date", sFilter2)

End Sub

In this example we have a sheet with two pivot tables for a week-over-week comparison.  So we need to filter one pivot table for the most current date, and one for the prior week's date.

The Filter_Multiple_Pivots macro calls the Filter_PivotField_Args macro twice.  Notice that the pivot table name and filter criteria values are different for each call.  This is an easy way to reuse the filter pivotfield macro multiple times without having to repeat a lot of code.

Checkout my free video series on getting started with Macros & VBA to learn more about writing macros.

Macros and VBA Training Banner 633x153

Solution #2: Add a Calculated Column to the Data Set

If you can't use macros then you can solve this problem with a calculated column.  This solution is NOT as flexible as the macro.  It does NOT really work well if you want to have slicers where the user can select other report dates.

We can add a column to the source data to check if the report date in each row is equal the most recent date.  We will call this column “Current Wk”.

Add a Calculated Column to the Source Data to Determine the Current Period

If it is then the formula will return a TRUE, if not it will return a FALSE.

This is a very simple formula.  We could use an If function, but it is not necessary here.  The equal sign will evaluate the match and return a TRUE or FALSE.  Checkout my article on If Statements in Excel formulas for an explanation on this.

We can now add this new column to the Filters area of the pivot table, and filter it for TRUE.  This means that anytime the source data is updated with new data, the formulas will recalculate the new current week rows.  Refreshing the pivot table will automatically apply the filter for the current week rows, and display those in the pivot table.

Add the Current Week Field to Filters Area and Filter for TRUE

The drawback here is that the user cannot really use a slicer to filter for other dates.  Technically they can, but we will have to reapply the Current Wk filter next time the data is updated.  We could use a macro to automate this, but then we are really back to solution #1.

Although it's not as dynamic as solution #1, the calculated column might be all you need to display the most recent period's data in a static report.

Two Ways to Automate Pivot Table Filtering

Filtering pivot tables for the most current date or period is definitely a good process to automate if you do this task frequently.  This can save us a lot of time, and prevent errors that usually occur with these boring repetitive tasks. 🙂

Please leave a comment below with any questions or suggestions.  Thanks!


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

  • Excellent article.

    Could you please explain how will be the solution (if any) using a calculated field in the pivot table?

    Thanks a lot.


    • Thank you Carlos! I’m not sure of a solution for a calculated field in the pivot. It might be possible, but I don’t think you would be able to filter or slice the pivot table. Let me know if you find a solution though. Thanks!

      • hello Jon, i have already try this script. the result was work correctly but there is pop up notification as follows.
        “unable set visible property pivot item class”

        could you please pick me out from this problem ?


  • Hello, Jon! I am writing you just to say thanks for your great and useful web-site and Youtube channel. Thank you very much!

  • Hi Jon,

    thanks for posting this its very helpful, quick question whats the best way to set the format of: sFilterCrit = ThisWorkbook.Worksheets(“Data”).Range(“G2”).Value

    as when it gets to the pi.Name sFilterCrit my formats are different eg 31/05/2016 vs 31May2016.


    • Hi Pip,
      Just so I understand your question, are you saying that the cells in the data table are formatted 31/05/2016, and cell G2 is formatted 31May2016? If so, you will probably want to change the format of cell G2 to match the format of the cells in the data table.

      If cell G2 contains a date value then the comparison between and sFilterCrit should work, regardless of the format. But to be safe you might want to change the formatting to match.

      You can check the value that VBA interprets in the Immediate Window. Select cell G2, then type the following in the Immediate Window and hit Enter.


      This will return the value of the cell that VBA interprets for the comparison. Let me know if you have any questions. Thanks!

      • Hey Jon,

        I did try changing the cell format that but it still read the cell as a date like 31/05/2016 regardless of the format applied to the cell.

        Setting a format on my variable seems to work:
        ” sFilterCritFor = Format(sFilterCrit, “ddMmmyyyy”)”

        Thanks for your help

  • I had a slightly different problem. Well, still partially have.

    I wanted to show the recent trend for some data by week, but after a time the chart started being crowded after 10 weeks of data. I then created a calculated column called “Status” to filter out past the most recent 8 weeks:

    =IF([@[Start Date]]<(TODAY()-63),"INACTIVE","Active")

    I just added the "Status" to the filter section and only had the "Active" visible in the charts. The only problem is that the slicer still shows all of the weeks, not just the most recent, so when someone selects 9 weeks ago or earlier the charts show no data.

    Is there a way to filter the slicer to only show the most recent weeks as well?

    • Hi Dave,

      You can hide the items with no data.
      1. Right-click the slicer and choose Slicer Settings…
      2. Check the box that says: Hide items with no data
      3. Press OK.

      When the status field is filtered for Active, it should only show the weeks that are included in the Active rows.

      I hope that helps. Thanks!

  • What I’m trying to ask is how do I apply the above filter to my pivot tables? I’m not sure how to tie it in.

  • Hi John
    Regarding “Of course, this could be changed to calculate the latest date in the macro code” .
    Could you please help with showing this as well.
    Thanks in advance.

    • Hey Lund,
      Sure thing. We can change the value of the sFilterCrit variable to the following.

      sFilterCrit = Format(Date, "m/d/yyyy")

      The “Date” function returns today’s date in VBA. We can then use the Format function to convert the date to a string in the proper date format. You will want to use the date format that matches the date format used in the source data. In this case, the dates column G of the Data sheet in the example file are formatted with the “m/d/yyyy” format. If your dates are formatted differently, then you can change this format to match your dates.

      The pivot table is going to view these dates as text, not dates, in the list of pivot items. So it’s important that the formats match. I hope that helps. 🙂

  • Hello Sir,

    Need urgent help. I am getting error in following code

    Sub FilterPivotTable()

    Dim startdate As Date
    Dim enddate As Date

    startdate = “4/1/2016”
    enddate = “4/15/2016”

    startdate2 = “4/1/2017”
    enddate2 = “4/15/2017”

    ActiveSheet.PivotTables(“PivotTable1”).PivotFields(“Date”).PivotFilters.Add2 Type:=xlDateBetween, Value1:=startdate, Value2:=enddate, Value1:=startdate2, Value2:=enddate2

    End Sub

    I have big database in ms access,i have created pivot table in excel and connected with that database. I want the vba code for selecting multiple dates in pivot table to enable me to create comparison report on daily basis.

    Example of comparison report given as follows

    sales April 15 2016 April 15 2017

    X product 5000 6000

    Expecting prompt response from you on this.

  • Hello, Jon!

    Could you please explain me how can I convert following raw data into desired format with the help of pivot table

    Raw Data:

    Brand JAN Sale FEB Sale MAR sales Quarter
    Proudct A 1698 1246 1632 Q4
    Proudct B 1605 1321 1235 Q4
    Proudct C 1533 1107 1794 Q4
    Proudct D 1594 1869 1153 Q4

    Desired format with the help of pivot table:

    Brand Avg.Q4 AVg.Q4 in %
    Proudct A 1525 26%
    Proudct B 1387 23%
    Proudct C 1478 25%
    Proudct D 1538 26%

    I am believe only you can crack this problem.

  • Hey i need help, i have an error on calling Filter_PivotField_Args, it tell me “non facultatif argument, Thanks in advance .

  • Hi, Jon!

    I’m new to using Pivot Tables and your articles are a big help. I need to track down our inventory of bottles weekly whether it’s in our stockroom or it was last given to a client (bottles are numbered and they should be returned once client order again for use by whoever orders first). I have an in and out column of the bottle numbers on my data with client names and date of transaction. My problem is when I put the bottle number and client name on the row label and then on the value, I use the Max setting on the order date, there are several client names with order date that comes out on the pivot table. I only want the latest date with the client name. How do I do that?

  • HI,
    If i will try to select Top three current month in pivot filter then why it taking random selection data. please do need full

    This is code
    ActiveSheet.PivotTables(“PivotTable1”).PivotFields(“Invoice Month”).AutoShow xlAutomatic, xlTop, 3, “Sum of Subtotal”
    And i want it filter current three month Like: April, May & June
    and i have all 12 months in my filter but i want it select only current three month

  • Hi Jon,

    I try to macro filter pivot based on range of dates by referencing to 2 cells. Is it possible? Currently what you show for specific date. How about range of date?

  • Hi Jon!

    How would i do this but with multiple selections on one pivot table? or a range of dates on one pivot table?

  • What happens if the value I am looking for isn’t there in the filter? I have a case where I need to filter A out of A,B,C,D and E. If my data doesn’t contain B, how can I write an “IF” condition then ? Thanks

  • Hello,
    I tired to use “Solution #1” but I got an error #438 (use a property or method that does not support by that object). Only one way that I can manage it is:

    With .PivotFields(“YEARS”)

    .PivotItems(“2020”).Visible = False
    .PivotItems(“2021”).Visible = True

    End With

    but it also works in a limited functionality, to get choose current “2021” I need to turn off all others year values before. If would do it I have nothing, no error no action.

    I would appreciate any help or suggestions concerning my issue.

    Best regards,

Generic filters
Exact matches only

Excel Shortcuts List

keyboard shortcuts list banner

Learn over 270 Excel keyboard & mouse shortcuts for Windows & Mac.

Excel Shortcuts List

Join Our Weekly Newsletter

The Excel Pro Tips Newsletter is packed with tips & techniques to help you master Excel.

Join Our Free Newsletter