Filter an Excel PivotTable by a Date in Another Cell
Question details
The user needs an Excel PivotTable to filter records automatically based on a target date entered in cell J2.

- Product
- Microsoft Excel PivotTable
- Device & OS
- not provided
- Scenario
- A worksheet contains a Settled Date field, and the PivotTable should show only records whose Settled Date is greater than or equal to the date entered in J2.
- Observed behavior
- A standard PivotTable filter does not automatically use a changing worksheet-cell value as its filter criterion.
Confirm the PivotTable name and the exact date-field name, such as PivotTable1 and Settled Date. Enter a valid date in J2 and save a copy of the workbook before adding VBA code.
Use a worksheet-change VBA event to filter the PivotTable from cell J2
Use this solution when the PivotTable should refresh its Settled Date filter automatically whenever the date in J2 changes.
A regular PivotTable filter cannot directly reference a changing worksheet cell. A Worksheet_Change event can monitor J2, validate the date, clear the existing filter, and apply a greater-than-or-equal-to filter to the Settled Date field.
In Excel, right-click the worksheet tab that contains J2 and the PivotTable, then select View Code to open that sheet's VBA module.
Paste this code into the worksheet module: Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Me.Range("J2"), Target) Is Nothing Then On Error GoTo CleanUp Application.ScreenUpdating = False Application.EnableEvents = False With Me.PivotTables("PivotTable1").PivotFields("Settled Date") .ClearAllFilters If IsDate(Me.Range("J2").Value) Then .PivotFilters.Add2 Type:=xlAfterOrEqualTo, Value1:=Format(Me.Range("J2").Value, "Short Date") End If End With CleanUp: Application.EnableEvents = True Application.ScreenUpdating = True End If End Sub
Replace PivotTable1 with the actual PivotTable name and Settled Date with the exact field caption used in the PivotTable. Keep J2 as the monitored cell unless the target date is stored elsewhere.
Return to the worksheet, save the file as an Excel Macro-Enabled Workbook (*.xlsm), enable macros when prompted, and enter a new valid date in J2. The PivotTable should show records on or after that date.
Use a Data Model measure for a model-driven date condition
Use this approach when the workbook already uses the Data Model and the target date should be selected from a separate target-date table.
Automate WPS Spreadsheet PivotTable date filtering with VBA
WPS Spreadsheets can help automate this workflow on desktop editions with VBA support. Add a worksheet-change event that watches J2, applies the Settled Date filter, and save the workbook in a macro-enabled format for repeated use.
- 1. Open the worksheet code window: In WPS Spreadsheets, right-click the worksheet tab containing J2 and the PivotTable, then choose the option to view or edit the sheet's VBA code if VBA is available in your edition.
- 2. Add the date-change event: Insert a Worksheet_Change event that monitors J2, clears the Settled Date filter, and applies a greater-than-or-equal-to date filter to the correct PivotTable field.
- 3. Check names and date input: Verify the PivotTable name and Settled Date field caption, then enter a valid date in J2 to test the automatic update.
- 4. Save as a macro-enabled file: Use Save As to select the Excel Macro-Enabled Workbook (*.xlsm) format, then allow macros when reopening the file.

Frequently Asked Questions
Can an Excel PivotTable use a date stored in another cell as a filter?
Not with a standard PivotTable filter alone. Use a Worksheet_Change VBA event to read the date from J2, or use a Data Model measure with a target-date table.
Why does the VBA code use Application.EnableEvents = False?
It prevents the code from triggering another Worksheet_Change event while the PivotTable is being updated. The code should restore events to True even if an error occurs.
What should I change if my PivotTable is not named PivotTable1?
Select the PivotTable, open the PivotTable tools, and check its name. Replace PivotTable1 in the VBA code with that exact name.
What happens when J2 is blank or contains text?
The sample code checks IsDate(J2). If the value is not a valid date, it clears the existing Settled Date filter and does not apply a new date condition.
Do I need to save the workbook as XLSM for the automatic filter to work?
Yes. Save the workbook as an Excel Macro-Enabled Workbook (*.xlsm), and enable macros when opening it so the worksheet event can run.




