Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have an Excel file with several tabs, one of which has two PivotTables. The data source for the first table is a specific range of rows (not an entire column) and the second table is based on the first. The size of the file is ~12 MB. If I remove this tab with the PivotTables the size goes down to ~1 MB.

I have two related questions:

  1. How can I select specific columns for the PivotTable in the data source? When I tried to do this in the Wizard I got a "Reference is not valid" error.
  2. Are there any other methods to reduce the size of the file? It seems ridiculous that a file would increase 12-fold in size because of this tab.

Thanks a lot.

share|improve this question
up vote 1 down vote accepted

It sounds like you have two separate pivot caches which, considering the base data is the same, would effectively double the storage requirements unnecessarily.

This article has some great tips which should help.

share|improve this answer
That's a very good article. My second pivot table is already based on the first. I actually ended up using the tip here: turn off "Save Data With Table Layout". This reduced the size of my file to 2 MB, which is very adequate. Thanks for the feedback. – alexsome Aug 18 '10 at 18:46

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.