What are Pivot Table Report Filters and How to use them?

Posted on April 20th, 2011 in Pivot Tables & Charts - 22 comments

Pivot Table Report Filters - What are they and how to use them?Today we will learn about Pivot Table Report Filters.

We all know that Pivot Tables help us analyze and report massive amount of data in little time. Excel has several useful pivot table features to help us make all sorts of reports and charts.

Report Filters are one such thing.

How do Report Filters help you?

Let us say, you are an analyst at ACME Inc., that has 3 products – Fastcar, Rapidzoo and Superglue. You have 4 salespersons – Joseph, Lawrence, Maria & Matt. You operate in 3 regions – West, North and Middle.

Now, you are given the data for all sales from Jan 2007 to July 2009 and your boss asks you, “I need a report on sales by product and salesperson in each region”.

This is where a Report filter would help you.

You can put Salesperson in Row label area, Product in Column area, net sales in value field area and region in report filter area of the pivot table. Then, you get a report like this:

Pivot Table Report Filters - how to add them?

You can immediately switch the report filter to other regions (or a combination of them) to produce the region-wise reports.

Generating Multiple Reports from One Pivot Table:

Using Report Filters, we can quickly generate multiple pivot reports. For this,

1) Click anywhere inside pivot table, and go to Options ribbon.
2) From here, click on little down arrow next to options, choose “Show Report Filter Pages”.
3) Select the filter field for which you want multiple pages.
4) Done! Excel produces multiple worksheets, one each for a report filter setting.

See this demo:

Generating Multiple=

Few more tips on using Report Filters

Add Multiple Report Filters

You can add more than one report filter to a pivot table. This is a very useful way to slice and dice your data when you have lots of columns (dimensions). For eg., you could add report filters on Month, Region & Product.

Show Report Filters in rows or columns

From Pivot Table Options, you can set how Excel should layout the report filters. This setting is available in Layout & Format Tab.

Select More than one value for Report FilterSelect More than one value for Report Filter

By default, Excel allows you to specify only one value per filter. But you can over-ride this by using the “Select multiple items” check-box in report filter.

Download Report Filters Demo Workbook

I have made a demo workbook showing how you can generate multiple reports from same pivot table. Go ahead and download the workbook.

Click here to download Report Filter Demo Workbook.

How do you use Report Filters

I often use Report filters to generate reports for a specific time-window or product group for my small business. I generally do this while analyzing sales or something. For eg. I would make a pivot chart with sales data and add a trend-line to it. Then I would change the report filter to instantly understand the trend for a different product. I like the power report filters give me in situations like this.

What about you? Have you used Report Filters before? In what situations do you find report filters help-ful. Please share your experience & tips using comments.

More Articles on Pivot Tables

If you would like learn more about Pivot Tables, go thru these articles:

Your email address is safe with us. Our policies

Written by Chandoo
Tags: , , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

22 Responses to “What are Pivot Table Report Filters and How to use them?”

  1. Excelophile says:

    Hi, I’m relatively new to this wonderful site–just signed up for the newsletter yesterday, and the timing of this topic couldn’t be more perfect for me! I’ve been using the “Show Report Filter Pages” option in Excel for a while now (as described in the “Generating Multiple Reports from One Pivot Table” section). It’s a great tool, but what would be even greater is if a similar function exists to create multiple charts from one pivot chart! Does anyone know if this feature exists in Excel? Thanks!

  2. Fred says:

    Thanks! I like pivot table, too. I am heavy user of pivot on the 2003 version, more so than the 2007 version which I’m currently using. Personally, I find 2007 pivot a bit less intuitive and user friendly than 2003, but just by a little bit. Not a lot.

    My challeng of using pivot is that sometime (may be I’m to blame) I can’t get pivot to display the way I want it to be. Sometimes it is the count not sum, sometimes it’s other things.

    Because I’m not using pivot lately with 2007, I wonder if it still explode the file size with every new pivot table created? Most of us deal with thousands of lines and we’ll be tasked with creating multiple pivot tables for others to read, or use the data from the pivot tables created for other things.

    And I have difficulties to make columns with sub-columns. For instance, I would be asked to display in the form of product families by sales.
    Row = Salesman
    Columns = Major Product Family A with 3 products and Major Product Family B with 4 products and so on.
    And boss doesn’t want to have any product families filtered out. He wants to see the whole product lines.

  3. Satya says:

    Thank you, Chandoo! I am new to Excel and was working on a Pivot Table. Your tip on report filters has really helped.

  4. Chandoo says:

    @Excelophile: Welcome to Chandoo.org & thanks for joining our newsletter.

    Unfortunately, you cannot create multiple pivot charts from one. You need to just manually copy / paste the pivot charts or use a macro to do that.

    @Fred: You are welcome. I think the file size grows as you add pivot tables to it. You can set some options to clear pivot cache, but still Excel needs to calculate and store the results.

    For sub-columns problem, you can use grouping option in Pivot Tables. It is quite powerful.

    @Satya… You are welcome :)

  5. [...] Last week, we have learned what Pivot Table Report Filters are & how to use them. [...]

  6. amit kumar says:

    this is very good and use ful for every one

  7. Julian says:

    Sadly it seems that this option isn’t available for pivot tables from OLAP data cubes

  8. [...] for multiple salespersons. Since you want to show the report by one person at a time, you used report filters in pivot tables to display this. But you find that switching between regions is a pain using the [...]

  9. Ros says:

    hi Chandoo,

    Your blog is awesome!
    I’m currently working on Report Filters and found a limitation to it that it cannot function like Data Filters in Excel. I need to limit pull-down choices to non-zero results. Hope you could help me. Thanks,

    Ros

  10. Jae says:

    Hi,
    I just found your website and let me congratulate you! All this information is very powerful and it will make a great difference in the way I do my work. Im looking forward to browsing more and learning. Thanks!

  11. Cory says:

    Chandoo,

    Great blog! I’m having the same problem that Ros is having with the limitation on multiple filters. Is it possible to have Pivot Filters be additive in Excel 2007? I.e. If I had 3 Filters (Country, State/Province, City) and I select USA for country, can the second filter only have relevant states listed in it and not states/provinces from other countries? Thanks!

  12. Ria says:

    I created a couple of report filters and made some output files from the pivot table by using these report filters.

    The problem is my main data source is updated every week and I have to refresh my pivot table which is fine, i am just worried that if there is a shift in the columns and rows due to new additions in the pivot table the output files can not be just refreshed and have to be created again.

    Is there any option in which the output files created from the report filters will be automatically updated even when there is shift in the cells? please help

  13. I like this “Report Filter”… They are very useful. What I am currently looking for is: How do I create a report filter via VBA in Excel 2007? I have been looking out for this for a while but did not get anything satisfactory. Any support out here?

  14. Lukas_K says:

    Master :D Chandoo,

    your website is one of the best things I found in the net. Great job and keep it up!

    I have one issue with this subject. Imagine you have 2 report filters in one Pivot table ( Region and City ) and some values for this data. You want to create separate sheet for every City, but only for filtered Region. Example: I only want to create each city sheet for North Region.

    I’m asking if there is any way to do this with Show pages option?

    Again – thanks for huge input in this site.
    Cheers

  15. Dennis says:

    Chandoo,

    Can the Slicers option of 2010 be forced in Excel 2007?

    Eg, if I have a list of Sales persons and regions, if I select one sales person, I only want to see his regions in the next report filter.

    Thanks,

  16. Nikki says:

    Hi,
    Is there anyway that you can ‘Show report filter pages’ but for column labels.
    Thanks

  17. [...] to manipulate. Next begins your love affair with pivot tables to slice and dice the data. (See: What’s a pivot table and how do you use [...]

  18. Harshit says:

    Is there anyway where i can copy all the available option from pivot filter to specific range? I want to show them in listbox rather than using pivot filter directly and then pass the selected values from listbox to pivot filter directly.

    I am able to pass values from listbox to pivot filter but not able to copy value or choices from pivot filter to range 

  19. Muhawat says:

    Sir,
    When i use Report Filter then all records show in row labels for filter more.
     
    Is there any option available that When i use Report Filter and select one record then show only those records in row labels which fall only under that selection record, for filter more instead of all records in a sheet.
     
    thanks.
     

  20. erin says:

    I use this function in a report regularly for a similar purpose – so show individual reports by staff members.  Where I’m stuck is when I add new staff members. Is there a way to add a new person and have excel just create a tab for the new person (rather than regenerating all of the individual pages)?

  21. Debasis says:

    I wanted to check about “report filter” and your page gives very clear explanation. Great job. Many thanks.

  22. […] to manipulate. Next begins your love affair with pivot tables to slice and dice the data. (See: What’s a pivot table and how do you use […]

Leave a Reply