Excel: Limit a Report to Just One Region

This page is an advertiser-supported excerpt of the book, Power Excel 2010-2013 from MrExcel - 567 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.

Problem: I need to send a customer report such to each regional manager in my company. I want each manager to see only sales in his or her region.

Strategy: You can use the Report Filter area of the pivot table to create such a report. You drag the Region field to the Report Filter drop zone in the lower half of the PivotTable Field List dialog. It seems like nothing has really changed. All the numbers in the pivot table are the same. But, there is a new Region dropdown in row 1.

  1. Initially, the report still shows all regions.

    Open the dropdown next to Region. You can select any one region, or using the new checkbox, select multiple regions.

  2. Select one region.

    Choose East from the Region dropdown. The report will update to show just the customers from the East region. You can print this report and send it to the East regional manager.

  3. The report shows only sales for East.

    To produce the report for Central, you simply change the Region dropdown from East to Central. You can repeat for each other region.

For more resources for Microsoft Excel