Learn how to replicate a Pivot Table in MS Excel 2007.
Tags:excel advance training,microsoft excel 2007,microsoft windows,pivot table,replicate,total training
Grab video code:
We have created a really powerful report here but my favorite feature of pivot table is still to come. This feature will allow us to create one pivot table and then replicate it for every customer or department or any other field in our table. I am going to take the customer field and move it to the report filter section of the pivot table. Now initially, using the report filter, we can show one customer, click okay and then print. Show the next customer and then print. Show the next customer and then print. This could be a very tedious process if we have 50 or 500 customers. Excel offers a great option to allow us to create 50 different versions of this pivot table in a matter of seconds.
Now, before we do this, you should think about if you are going to print the pivot table. You would want to go in and create your print settings. For example, landscape, portrait, margins because very soon, you are about to have 50 different worksheets and you do not want to go change those page setup options on all 50 worksheets. So we have our pivot table, we have at least one field on your port filter section and we are going to the options drop down where we will find this setting for show report filter pages. Choose that, Excel shows you a list of all the fields that are in the report filter section. In this case we only have one. Click okay and in a matter of seconds, Excel has created a new worksheet for all of our customers. This is a very powerful way to create a report for all of the customers in the data set.