Pivot table: you can separate it from the source data

I don’t think I have ever seen anybody talk about this, but did you know you can separate the tab with the pivot table from the file with the source data?

Literally, separate the tab with the pivot table from the file that contains the source table of data that pivot table pulls from.

Doing such separation from the source data can done that with no issues in rearranging the pivot table. You can also change what goes into the rows and columns and the values section of the pivot table. And finally, the pivot table can be completely rearranged and no #N/A or #VALUE! will appear.

You can’t do that when you are linking files.

Do you do anything special to separate the pivot table?

No, you don’t have to do anything special. Just right click on the tab, select “Move or Copy”, and then if you want to make a copy of the tab, select “Copy”. Then you can move or copy the tab into a new book. Images A and B show what I’m talking about.

And that’s it. You will have created that tab with the pivot table separate from the source of data.

What’s the advantage of separating pivot table from source table?

In a word: size.

If you have trouble with WiFi or your email provider has a document size limit, doing such separation will reduce file size.

How did I discover this?

I don’t remember the full context of the situation, but I remember needing to send a file to my boss. I don’t think we had a network drive (we were in a small private firm). I think it might have been a variance analysis file to determine if we were operating within the budget.

The pivot table compared the actuals with the budget. As you can imagine, combining budget numbers with the actuals produced a very large file which exceeded our Outlook file size limits.

I don’t know what prompted me to do this, but I thought I would experiment separating out the pivot table from the source data with actuals and budget. I reasoned, very similarly to linked files, I might be able to send the pivot table alone, so long as my boss did not do any rearranging of the pivot table.

The file went through.

I stood over my boss and watched her open the file. And it worked! It opened without any of those errors such as #VALUE! or #N/A.

And then she reached out to try to manipulate the pivot table or something. And it didn’t break!

Imagine the shock!

A caveat

If you want to reduce file size in such manner, there is something you should be aware of: the pivot table will still have access to any sensitive data such as wages. So, if you don’t want someone to have access to sensitive data, make sure to strip them out.