fbpx
Search
Close this search box.

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

Share

Facebook
Twitter
LinkedIn

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:

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Excel School made me great at work.
5/5

– Brenda

Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

Weighted Average in Excel with Percentage Weights

Weighted Average in Excel [Formulas]

Learn how to calculate weighted averages in excel using formulas. In this article we will learn what a weighted average is and how to Excel’s SUMPRODUCT formula to calculate weighted average / weighted mean.

What is weighted average?

Wikipedia defines weighted average as, “The weighted mean is similar to an arithmetic mean …, where instead of each of the data points contributing equally to the final average, some data points contribute more than others.”

Calculating weighted averages in excel is not straight forward as there is no built-in formula. But we can use SUMPRODUCT formula to easily calculate them. Read on to find out how.

32 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!

    • Mahesh says:

      Hi Cory,
      Did you get the response for your query, because even i have posted similar question. If yes, please share the same, it would be really great help.

  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 😀 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 […]

  23. Denise says:

    First, let me say that this website is outstanding. So much excellent information! I do however have a question regarding Pivot Tables. I haven't found an answer yet so hoping someone can help. Is it possible to hidden items in a pivot table but still include them in the grand total? I am currently using Excel 2010. Basically, I have a PT that includes sales for both associates and mangers. I would like the managers totals to be included in the grand total but do not want their names to be seen in the PT. I have numerous PT's that I manually hide the managers on a daily basis. It has become both time consuming and leaves room for errors. Any ideas??

  24. Raj says:

    Can we group values of a variable in the Report Filter? For example if I have countries in report filter and I want to group them like say Eastern, Western etc.. can I do that? I know this grouping can be done in Row/Column Labels. But not sure if this can be done in Report Filter too.

  25. loes says:

    Generating Multiple Reports from One Pivot Table is limited to my data and I do not know how to solve it

    I have data for each day entered as a number.

    then i want to create 12 tabs for each month.

    Just changing the date to custom setting mm does not work also not pin pivot tables. When you do this and then do Generating Multiple Reports from One Pivot Table. Excel starts to create a tab for each DAY. How can I convert this?

    thank you!

  26. José Gonçalves says:

    Hi,

    I would like to know if its possible to make some kind of filter on the "grand Total" by line on a pivot table.
    So basically the grand total would be an additional filter

    Thanks in advance for the help

  27. Mahesh says:

    Hi Everyone,

    I am in a need of some help in pivot table.. I have multiple report filters, For ex : Country, State, City... If I select 1 particular country, I need to see only those states that are existing in that country instead of all states of all countries, followed by relevant cities existing under selected "state" instead of all cities of all states / countries.

    Thanks in advance...Will be waiting for your response.

  28. Zak Kandrachoff says:

    very helpful. thanks!

Leave a Reply