Patricia asks how to replicate a frequency distribution for many questions in a survey. If your data is set up correctly, this is a snap using the Show Report Filter Pages feature in a pivot table. Episode 1021 shows you how.
This video is the 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 video is the 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.
Basically, you start out with massive amount of data.
How we gonna analyze as well let's file up a pivot table.
Let's see how we solve this problem.
Hey! Welcome back to the MrExcel netcast.
I'm Bill Jelen.
I want to send a shout out to Ailly Ailly!
I think it's probably the youngest MrExcel podcast viewer.
Ally's dad, Michael was in my power Excel boot camp a few weeks ago.
He says his daughter Ailly likes to watch the MrExcel netcast and I was amazed.
Ailly is like five or six.
So, Ailly I hope you're having a great memorial day.
That's right in the United States.
It's memorial day, today.
Most of you are not at work, you're home.
Grilling out burgers and dogs.
You'll catch this, I'm sure on Tuesday.
When you back to work.
For the rest of the world, welcome to another Monday.
Today's question sent in by, Patricia.
Patricia is trying to use pivot tables to analyze some survey data, and I imagine her data might look like this.
Where she has each survey as a unique ID.
The question and then they answer to that question.
Now, let's build a pivot table first, and then we'll get to Patricia's question.
So, we click [ ok ], what we want to see is the various answers going down the side.
Look at me, like it's Excel 2003.
What we want to see is the answers going down the side sorry and then I'm going to count the ID.
Now, of course it's going to SUM the ID here but I'm going to double click that and value field settings change that to Count.
So, I can see how many times each answer came along and then I'm going to take the question and move that up to the report filter.
Alright! Now, a couple things we might want to do here.
I'm going to change, how count of ID is shown or go to field settings and say I want to show this as a percentage of the total summarized by Count percentage to the total.
So, it looks pretty evenly distributed there.
Now, we can come in and choose one particular question, click [ ok ] and we get to see how that particular question was answered.
Now, here's Patricia's question.
She says, "Hey! I have this pivot table but it becomes horrendous for me to create this for every single question. I have to go back in and recreate and recreate and recreate." Well you know, the one thing we can do is take question makeup be the first row label field and then we get all the questions going down.
But, I'm going to assume that Patricia has some nice little chart or something that's being driven from this pivot table and we just want to replicate the whole thing.
Check this out is an amazing feature, hardly anyone ever uses it in Excel 2007.
We're going to go to the options tab and then the options drop-down and then something called Show Report Filter pages.
Now, in 2003 this was in a different spot.
You go to the pivot table toolbar, left side of the pivot table toolbar, open the pivot table drop down, all the way the bottom is show pages is what's called show pages.
You get the same dialog box where you choose one of the fields, that's up in the page area, click [ ok ] and Bam! Just like that.
We now have one worksheet for every single question.
An amazing way to take your one pivot table and replicate it for every, whatever you have.
In this case, in Patricia's case, That's questions.
But it might be every cost center, every operating unit, every whatever.
Using that show report filter pages.
A great way to go a feature that's been there for a long time.
Most people though don't know about it at all.
Thanks to Patricia, for sending that question in.
Thanks to Ailly for our youngest podcast watcher and thanks to you for stopping by.
See you next time for another netcast from MrExcel.
Thanks for stopping by, we'll see next time for another netcast from MrExcel.
I'm Bill Jelen.
Basically, you start out with massive amount of data.
How we gonna analyze as well let's file up a pivot table.
Let's see how we solve this problem.
Hey! Welcome back to the MrExcel netcast.
I'm Bill Jelen.
I want to send a shout out to Ailly Ailly!
I think it's probably the youngest MrExcel podcast viewer.
Ally's dad, Michael was in my power Excel boot camp a few weeks ago.
He says his daughter Ailly likes to watch the MrExcel netcast and I was amazed.
Ailly is like five or six.
So, Ailly I hope you're having a great memorial day.
That's right in the United States.
It's memorial day, today.
Most of you are not at work, you're home.
Grilling out burgers and dogs.
You'll catch this, I'm sure on Tuesday.
When you back to work.
For the rest of the world, welcome to another Monday.
Today's question sent in by, Patricia.
Patricia is trying to use pivot tables to analyze some survey data, and I imagine her data might look like this.
Where she has each survey as a unique ID.
The question and then they answer to that question.
Now, let's build a pivot table first, and then we'll get to Patricia's question.
So, we click [ ok ], what we want to see is the various answers going down the side.
Look at me, like it's Excel 2003.
What we want to see is the answers going down the side sorry and then I'm going to count the ID.
Now, of course it's going to SUM the ID here but I'm going to double click that and value field settings change that to Count.
So, I can see how many times each answer came along and then I'm going to take the question and move that up to the report filter.
Alright! Now, a couple things we might want to do here.
I'm going to change, how count of ID is shown or go to field settings and say I want to show this as a percentage of the total summarized by Count percentage to the total.
So, it looks pretty evenly distributed there.
Now, we can come in and choose one particular question, click [ ok ] and we get to see how that particular question was answered.
Now, here's Patricia's question.
She says, "Hey! I have this pivot table but it becomes horrendous for me to create this for every single question. I have to go back in and recreate and recreate and recreate." Well you know, the one thing we can do is take question makeup be the first row label field and then we get all the questions going down.
But, I'm going to assume that Patricia has some nice little chart or something that's being driven from this pivot table and we just want to replicate the whole thing.
Check this out is an amazing feature, hardly anyone ever uses it in Excel 2007.
We're going to go to the options tab and then the options drop-down and then something called Show Report Filter pages.
Now, in 2003 this was in a different spot.
You go to the pivot table toolbar, left side of the pivot table toolbar, open the pivot table drop down, all the way the bottom is show pages is what's called show pages.
You get the same dialog box where you choose one of the fields, that's up in the page area, click [ ok ] and Bam! Just like that.
We now have one worksheet for every single question.
An amazing way to take your one pivot table and replicate it for every, whatever you have.
In this case, in Patricia's case, That's questions.
But it might be every cost center, every operating unit, every whatever.
Using that show report filter pages.
A great way to go a feature that's been there for a long time.
Most people though don't know about it at all.
Thanks to Patricia, for sending that question in.
Thanks to Ailly for our youngest podcast watcher and thanks to you for stopping by.
See you next time for another netcast from MrExcel.
Thanks for stopping by, we'll see next time for another netcast from MrExcel.
