Hey welcome back to the Mr. excel net cast, I’m Bill Jellen.
Face it, we start out with massive amounts of data, so how are we going to analyze this; well lets fire up the pivot table, and see if we can solve this problem.
Hey welcome back to the Mr. excel net cast, I’m Bill Jellen, I’m going to send this shout out to Ally. Ally I think is probably the youngest Mr. Excel pod cast viewer, Ally’s dad Michael was in my power excel boot camp a few weeks ago, he says his daughter Ally likes to watch the Mr. Excel net cast, and I was amazed. Ally is like five or six, so Ally I hope you’re having a great memorial day. That’s right United States is Memorial Day today. Most of you are not at work, you’re home curling out burgers and dogs. Your catch is I’m sure on Tuesday when you’re back to work, for the rest of the world, welcome to another Monday.
Today’s questions and invites, Patricia. Patricia’s trying to use pivot tables to analyze some survey data, and I imagined her data might look like this where she has each survey as a unique id, the question and then the 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 we want to see 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 a 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 of things we might want to do where I’m going to change how count of id is shown. I’ll go to field settings and say, I want to show this as percentage of the total. Summarize by count, percentage of the total, and so it looks pretty evenly distributed in 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 his 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 you can do is, take question, make that 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, it’s an amazing feature. Hardly anyone 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 tool bar, open the pivot table, drop down all the way to the bottom is shown pages. You get the same dialogue box where you choose one of the fields is up in the page area, click ok and 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 it’s questions, but it might be every call 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 Patricia for sending that question in. thanks to Ally for being our youngest pod cast watcher and thanks to you for stopping by. See you next time for another net cast, from Mr. Excel.
Transcription by:
Scribe4you Transcription Services