excelpivot-table

Lock Filter in Pivot Table

Is there a way to filter the pivot table to only include certain rows of the original data and protecting the filter from changes?

All users are allowed to view all data, but filters should not be changed for consistent reporting. Sheet protection does not work, because then the pivot-table cannot be updated.

Additionally: is it possible to filter a pivot table based on another cell? (Such that the pivot table is restricted to data where one field is equal to the value of a Cell.)

Solution

  • For filtering, sure! Use Filters:

    enter image description here

    Solution to protect filtering the PT.

    You need to use the intermediate worksheet which can be protected:

    enter image description here

    [A1]=VSTACK(Sheet1!A1:B1,FILTER(Sheet1!A2:B13,Sheet1!C2:C13="include",""))

    enter image description here