How to clone a pivot table

When you have a pivot table set up, you might find that you want another pivot table to show a different view of the same data. In this video, I'll show you how to base one pivot table on another.

Here I have a set of sales data for chocolate products.

I've also got another month's worth of sales data in this second sheet. We'll get to that in a minute.

To start off, I'm going to create a pivot table that shows sales broken down by Product, with the top selling products on top.

Now that we have the pivot table set up, we have a nice breakdown of sales by Product. But suppose you want to look at sales by Customer?

Well, the obvious solution is to rearrange the pivot table to show sales by Customer, instead of sales by Product, and I can easily do this.

But what if this is a sales report that you update weekly or monthly, and you want to always see sales by Product, sales by Customer, and sales by other categories, too? And what if some pivot tables are complicated, and you don't want to monkey with these once they're working well?

You're not going to want to make all these changes manually each time.

The simplest approach is to create additional pivot tables, where each pivot table shows a different view of the same data.

That might sound like a lot of work, but let me show you an easy way to do it. First, give the worksheet that holds your first pivot table a meaningful name. In this case, I'll name the sheet "Sales by Product"

Now, simply duplicate the worksheet and give the copy a new name. I'm going to show sales by Customer in the second pivot table, so I'll name the worksheet "Sales by Customer".

Next, adjust the pivot table as needed to show the breakdown you want.

Now I have two pivot tables, both linked to the same data. What's really nice about this is that if I refresh one pivot table, the other pivot table is also refreshed.

To show you how this works, I'm going to add another month of sales data to the main data sheet. This mimics the process of updating a report on an ongoing basis.

Before I refresh the data, notice that the total in each pivot table is about $21,000. When I refresh the first pivot table, you can see we have a new total of over $24,000. And if I check the second pivot table, we see the same total there too.

You can use this same approach to create as many pivot tables as you need, all linked to the same data.