File size does not reduce when breaking links

[mabus]

Hi all

I have a "report" xlsx file which links to a "data" xlsx file as a data source. The links are just normal cell reference links and a few SUMIFS with no macros.

The file size of the report file is around 4.5MB. Once I have refreshed the data by having the two files open together, I close the data file and using Edit Links I break all the links in the report file to the data file.

The report file has no links to any other file and is only a couple of hundred rows long and thirteen columns wide with not all the cells being populated. However, when I save this file the size of the file remains at around 4MB. I'm not sure why the file size remains so high. If I copy the report into a new file the new file size is well below 1MB. I can reduce the file size by saving as a xlsb file but would prefer to keep as a xlsx file.

Any ideas why the file size does not reduce when the links or broken and what can I do to correct?

Thanks

Mabus

 

[mole999]

just check each sheet to find out if your last data position is that shown on the sheets

Ctrl+End (I think)

 

[mabus]

Hi

I have checked to make sure there in nothing thousands of rows below or to the side of the selection. I have deleted rows and columns to make sure but it did not help.

 

[SpillerBD]

Any ideas why the file size does not reduce when the links or broken and what can I do to correct?

Are you using Pivot Tables to generate the "report" file?
When you copy the "report" to a new file are you using a Copy\Paste Values or a Copy worksheet method?

 

[mabus]

Hi

There are pivots tables in the data file which the report file just has a normal link to the cell i.e. "=A3" rather than looking for a specific value in the pivot. There are no pivots in the report file. I have just copied into a new file using copy and paste (no paste special) and the file size drops from 4.3MB to 38KB!

Is the report file somehow keeping a record of the pivots in memory?

 

[SpillerBD]

Is the report file somehow keeping a record of the pivots in memory?

Welcome to the world of Pivot Cache! The Pivot Cache is the data Excel sets up for the Pivot Table. Even when you copy a WorkSheet with a Pivot Table to a new Work Book, a Pivot Cache is copied to the New Workbook too.
There are methods to copy a range that includes a Pivot Table that will not copy or using GETPIVOTDATA that references a different file that reduces the file size.

But it sounds like you already find a method and key in sending a small file with the desired Pivot Table results....

 

[mabus]

That makes sense - well I understand anyway!!!!!

Is there a way to clear the pivot cache in the report file or is it there forever more?

 

[SpillerBD]

If there is a Pivot Table there is a Pivot Cache.
If no Pivot Table, no Cache....

 

[mole999]

That makes sense - well I understand anyway!!!!!

Is there a way to clear the pivot cache in the report file or is it there forever more?

This might help https://support.microsoft.com/en-us/kb/104308