Is it possible to set an Excel Pivot Table filter value to a cell reference?

I'm trying to make a Pivot Table more dynamic in Excel by filtering not on checked values from a list (as is the default method) but to filter on a value contained in a cell reference. Is this possible and if so, how?

The pivot table supplies data to a model somewhere else in my workbook, so being able to update a value and have the pivot table automatically refresh would be ideal.

2 Answers

YES it's very much possible that you can use a Cell Reference to Filter records while using the Pivot Table.

let me discuss one simple example. Suppose you have database in Range A2 to D20. Contains Headers for example Date, City, Name & Sales. Now to work with Cell reference to filter, steps shown below should be followed.

1. Create a New Column E.
2. write a formula like =and(D2<=1000,D2>=5000), press Enter.
3. Drag down the formula till end,(You find True & False as per Data).
4. While building the Pivot table, move New Field(Column) to Report Filter OR to Slicer (if you have Excel 2010 or higher version).
5. Choose True/False from the Filter.
6. Go to Pivot-table Tools Options & hit Refresh.

You find Excel has included the New Field's effect and as per it shows the result.

NB: Remember in Place of TRUE you can use FALSE option also, you can use any possible method (Formula is better way) which can help Excel to Filter record.

Hope this help you. In case my solution differs, just put a comment that where you need correction.

Adjust cell references in the formula as needed.

If you have Excel 2010 or later, then you can fake this without VBA because:

• a PivotTable with nothing but one field in the Filters pane looks and behaves pretty much exactly like a Data Validation dropdown does; and
• that PivotTable can be hooked up to the other PivotTables via slicers, so that it controls them.

I've written up exhaustive instructions at and don't have time to reproduce them here.