Excel 2010 is tightly integrated with SharePoint which allows users to author their reports in Excel and then simply save them in SharePoint so that they can be subsequently rendered to everybody else as a web page using Excel Services. Unfortunately, not all features of Excel can be rendered; some of them generate warnings but unfortunately, some of them prevent SharePoint from rendering the excel document all together. Data Validation drop downs are one such unfortunate features.
I have been searching high and low for a workaround and I think I may have just found one. OLAP PivotTable Extensions project on CodePlex has a FilterList feature that allows one to specify what values should be available in the PivotTable filter. I am yet to test all this myself, but the documentation claims that these changes will be available even to users who do not have the Extensions installed and also after the workbook is deployed to SharePoint.
One thought on “How to limit values in PivotTable filters in Excel”
all this does is select designated choices in the full filter list… it does not hide the un-designated choices.. it does not change what choices are seen on the filter dropdown list.