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.

macsmith

Posted 2017-11-24T18:31:23.730

Reputation: 125

If possible, I would prefer non-VBA solutions, but if it is the only way then please feel free to share. – macsmith – 2017-11-24T18:33:58.863

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.

Create a New Column E.
write a formula like =and(D2<=1000,D2>=5000), press Enter.
Drag down the formula till end,(You find True & False as per Data).
While building the Pivot table, move New Field(Column) to Report Filter OR to Slicer (if you have Excel 2010 or higher version).
Choose True/False from the Filter.
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.

Rajesh S

Posted 2017-11-24T18:31:23.730

Reputation: 6 800

I think this can work. You're proposing I create a formula for my table that produces a boolean value, and then filter on that value. So if I want to filter on an attribute "City" equal to "Toronto", I could point the formula to a reference cell somewhere where the user enters the city value. I'd then have to refresh my pivot table to see the adjusted values. – macsmith – 2017-11-28T16:56:04.077

Nice to here from you, yes for the Formula you can use the Cell reference also from any where in the Work Book. Keep asking,,. – Rajesh S – 2017-11-29T06:27:26.300

Since this works, I'll mark this as the answer. I think it's ideal in cases where conditions are simple, but if things are more complex (e.g. multiple filter values), it could get cumbersome. I think this is just a limitation of pivot tables, though. Thanks for the help! – macsmith – 2017-11-29T15:30:51.447

@macsmith, nice to hear from you again. If you need extremely complicated Data Filter, better use Power or MS-Query, or even Advance filter can help you. If you feel I can suggest you how you can use it in better way. – Rajesh S – 2017-11-30T06:28:33.433

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 http://dailydoseofexcel.com/archives/2014/08/16/sync-pivots-from-dropdown/ and don't have time to reproduce them here.

jeffreyweir

Posted 2017-11-24T18:31:23.730

Reputation: 426

Thanks for the tip, unfortunately my workplace uses 2007, so I can't test out this answer (I guess I should have specified the version in my question). I appreciate the help though! I'll try it out if we ever upgrade. – macsmith – 2017-11-29T15:32:53.867