How to Generate Multiple Reports from One Pivot Table

hello and welcome back to chips and time-savers in today's lesson I'm going to demonstrate how you can generate multiple reports from just one pivot table here's the scenario let's imagine in this scenario that you're the National Sales Manager and your vice president has asked you to prepare a report to show the progress that each territory manager has made towards reaching their quota we're going to have our national sales meeting so each of these territory managers is going to be invited to attend so the IT department has given you this raw data and now you're going to create a pivot table so go to the insert tab on the ribbon and choose a pivot table we'll take all the defaults to begin creating our pivot table on a separate worksheet now in this case we're going to select each of the four field so when we check territory because it's a text field it automatically gets added into the row labels drop area sales rep is also a text field so that's now added in as the second field in the row in other words the inner row when we select a numeric field such as quota or sales they go automatically into the values drop area and their subtotal using the sum function now we look at this from it and we say you know I really need another field in there I really need to see what percentage of the quota has been reached so rather than going back to the source data and adding in that calculation adding in another field and then going back and starting your pivot table from scratch no you don't have to do that you can create what's called a calculated field in a pivot table so select one numeric cell and then over here on pivot table tools what we want to do is we want to use the drop down for formulas to create a calculated field and it's going to be a formula so let's first give it a label let's call it P CT of quota in other words we know that it's going to be percentage of quota reach now how do we create the formula all formulas you know begin with the equal sign but this time it's going to be a calculation of field so it's going to be year-to-date sales and by double-clicking excel at in a single quotation mark to surround that divided by the quota now let's click OK and there is our new calculated field it's been added into the field list and it's put on to the pivot table well we notice that we have course have to change the formatting here it's not currency we need to change that to percentage so let's right mouse click and we choose value field settings and let's change the numeric format for that not as one of the customs but we want to make it a percentage so it's going to be a percentage with zero decimal places now before we click OK that's a long word and it's kind of misleading it's not the sum of the percentage of quota it's the percentage of quota now notice over here the percentage of quota has been added into our field list so in order to have this in our pivot table without getting an error message we have to trick Excel by adding a space after that name that we've now customized click OK and there you go so now we can see the percentage of quota that each sales rep is has reached now we might want to go through and change the name and the format for these other two calculated field are these other two fields that use some in the pivot table so what we'll do is we'll change the numeric format to currency with zero decimal places and we'll do the same changing the name to get rid of some of year to date sales but make sure that we add a space after that and now we'll do the same thing for the sum of quota we want to get rid of the sum of part of the label so right mouse click value field settings we want to change the number to be currency zero decimal places and now when we click OK we come over here and we remove the sum of and make sure that we have a space bar space after that click OK there you go so now we take it into the vice-president and said well that's fine but you know when they come in I want the regional managers to be able to just focus on their results so what we can do is we can take advantage of the report filter so in this case we'll move the territory from a row label we want to move it into the report filter so now this is going to be exactly what the vice-president wanted so when the manager from the mid-atlantic region comes in interactively we can show just the results when another manager comes in for example the West Coast comes in well we can show the results for that so now we demonstrate this to the VP of Sales he said well that's fine but before the meeting I want to be able to circulate a report I want to be able to give the West Coast territory manager this report I also want to give this to the VP of Finance and the VP of Marketing now you're thinking oh gosh we're going to have to go through create these filters and then copy and paste the report no you don't that's the beauty of what I'm going to demonstrate next so let's restore this pivot table and since we have one of the fields territory in the report filter this is how we can generate multiple reports from just one pivot table what we'll do is we'll return to pivot table tools options come over here into the pivot table group don't select options select the drop down arrow next to options and from the menu we want to say show the report filter pages so we only have one field up there now watch down here watch how multiple worksheets are going to be created when I click OK click OK and there you go it's incredible great plains Gulf Coast mid-atlantic Midwest so what you thought was going to be a nightmare turns out to be something that's very very easy now let's rename the sheet over here we'll call it the pivot table now yesterday I was training a client and she said that's exactly what my manager wants but the manager also wants me to be able to take these individual reports and show them on the same page or the same worksheet as the original pivot table can I do that yes you can so over here let's come over to the West Coast worksheet and what I want to do is I want to select the entire pivot table one way to do that is come up the pivot table tools options and we want to select the entire pivot table next we want to copy it so I'm going to come over here and say copy come back to the pivot table and now what I want to do is I want to paste it over there and I'll take another one of the reports I'll take southwest once again I want to select the entire pivot table I want to copy it this time I'll use ctrl C is the keyboard shortcut to copy the pivot table come back over here select where I want to have it placed right mouse click and say paste now these are actual real live pivot tables now as we're going through we might discover that we have one of the numbers is wrong the actual year-to-date sales let's say for Albert R are different than that so we come back to our original data source and I will sort this in ascending order so over here I want to choose to sort this in Essendon order one way to do that is the data tab on the ribbon and choose this and out okay so over here I want to change this to 50,000 so he's closer to reaching this quota and that when I come back to the pivot table pay attention over here what I want to do is I want to refresh the data so I'll go over here to pivot table tools options and I want to refresh refresh all so you see the 50,000 is changed here the 50,000 is changed here so this is a way that we can generate all of those multiple reports from just one pivot table I also showed you how to add in a calculated field change the formatting and change the label so that's typical of the reek of the tips that I offer on my video the 50 best tips for Excel 2007 and I'll see you in the next lesson

Channel: Danny Rocks

Views: 338,425

Rating: 4.8638048 out of 5

Keywords: Excel Pivot Table Reports, Report Filter, Show Report Pages, Work with Copy of Pivot Table, Software Tutorial, How-to, Microsoft Excel, Training, Pivot Tables, Management, Technology, Data, Office, Business, Computer Software

Id: djuLhm0roEA

Length: 8min 26sec (506 seconds)

Published: Fri Mar 11 2011