Do you ever need to produce a report for every customer? This is a snap with pivot tables in Excel 2007. In Episode 711, we take a look at how to use Show Report Filter Pages to replicate a report for every customer.
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey welcome back to the MrExcel netcast. I'm Bill Jelen We've been talking about pivot tables, and this is by far the most amazing trick in pivot tables I'm going to use "Insert", "Pivot table", OK.
and I'm going to build a report. Let's say "Products" down the side, "Regions" across the top, "Revenue" in the data area of the pivot table.
And maybe I''ll apply some formatting, so we'll go to the design tab and choose a nice format here.
maybe that one Great. OK. Now I want to produce this report for every single customer.
So I'm going to take the customer field and drag it to the report filter section.
And initially what I could do is, I could open the drop-down and choose one customer and produce report for just that customer, but I want to produce a report for every single customer.
So I'll choose all. On our pivot table tools "Options" dialog I go to the "Options" drop-down.
I'm going to choose "Show Report Filter Pages" and it says "show all pages of".
Now you notice you have to have the customer field up in the report filter area Click OK and very quickly excel goes through and inserts one new worksheet for every customer.
You'll see here's the AIG worksheet and then AT&T is selected that and then Bank of America and Boeing and so on.
It just created 50 new worksheets in my workbook in a matter of seconds.
The show Report filter Pages is an amazing trick in Excel 2007.
Back in excel 2003, you would go to the pivot table drop-down and choose show pages.
Same basic concept though it worked in either version.
Well hey, thanks for stopping by. We'll see you next time for another netcast for Mr Excel.
and I'm going to build a report. Let's say "Products" down the side, "Regions" across the top, "Revenue" in the data area of the pivot table.
And maybe I''ll apply some formatting, so we'll go to the design tab and choose a nice format here.
maybe that one Great. OK. Now I want to produce this report for every single customer.
So I'm going to take the customer field and drag it to the report filter section.
And initially what I could do is, I could open the drop-down and choose one customer and produce report for just that customer, but I want to produce a report for every single customer.
So I'll choose all. On our pivot table tools "Options" dialog I go to the "Options" drop-down.
I'm going to choose "Show Report Filter Pages" and it says "show all pages of".
Now you notice you have to have the customer field up in the report filter area Click OK and very quickly excel goes through and inserts one new worksheet for every customer.
You'll see here's the AIG worksheet and then AT&T is selected that and then Bank of America and Boeing and so on.
It just created 50 new worksheets in my workbook in a matter of seconds.
The show Report filter Pages is an amazing trick in Excel 2007.
Back in excel 2003, you would go to the pivot table drop-down and choose show pages.
Same basic concept though it worked in either version.
Well hey, thanks for stopping by. We'll see you next time for another netcast for Mr Excel.
