Block Selection of All in Report Filter

With a pivot table’s Report Filters, you can select one or more items, and see the summarized results for those items only. For example, you might want to see the sales in one region, or on a specific date.

You can also click on “(All)”, at the top of the items list, to see the overall results.

Block Selection of “(All)”

In some pivot tables, you might want to prevent people from selecting the "(All)" option in a Report Filter.

In this example, the worksheet shown below has GetPivotData formulas that refer to the Report Filter selection in cell B1. If (All) is selected in cell B1, the formulas show a message, instead of the dollar amounts.

Use Programming to Block “(All)”

Unfortunately, you can’t remove the (All) option from the report filter’s drop-down list, but you can use Excel VBA to block users from selecting it.

The code shown below will undo the report filter change, if (All) is selected or if someone checks multiple items in the filter. They’ll also see a message that explains what to do — "Please select a single date."

This code is stored on the pivot table’s worksheet module, and can be activated by selecting from the filter drop down list.

If someone selects “(All)” from the OrderDate report filter’s drop-down list, they’ll see the warning message.

Download the Sample File

To download the sample file, which contains the pivot table and VBA code, please visit my Contextures website: Pivot Table Report Filters VBA.  The zipped file is in Excel 2007/2010 format, and you will have to enable macros when you open the file.

_________________