SubscribeWhat are Pivot Table Report Filters 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:
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:
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 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:
- Excel Pivot Tables – Introduction & Video
- Excel Pivot Table Tricks
- How to Group Dates in Pivot Reports
- Show Top 10 Values in Dashboards using Pivot Tables
- Dynamic Dashboard using Pivot Tables & Slicers [Excel 2010]
- More articles on Pivot Tables
| ||||
|
| ||||
|
Leave a Reply
 |
Are You Ready for Online VBA Classes? I need your help! | There is an Easter Egg in Excel! |  |
At Chandoo.org, I have one goal, "to make you awesome in excel and charting". This blog is started in 2007 and today has 450+ articles and tutorials on using excel, making better charts. 
20 Responses to “What are Pivot Table Report Filters and How to use them?”
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!
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.
Thank you, Chandoo! I am new to Excel and was working on a Pivot Table. Your tip on report filters has really helped.
@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
[...] Last week, we have learned what Pivot Table Report Filters are & how to use them. [...]
this is very good and use ful for every one
Sadly it seems that this option isn’t available for pivot tables from OLAP data cubes
[...] 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 [...]
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
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!
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!
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
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?
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
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,
Hi,
Is there anyway that you can ‘Show report filter pages’ but for column labels.
Thanks
[...] 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 [...]
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
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.
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)?