How to split a file with big data

I have a file with data going back till 2010. This results in lots of rows and a huge file size and a slow performance.
11681 rows and the size is 10MB.

I would like to reduce the file size.

My problem is that I have lots of pivot tables and reports based on all the data all the way back to 2010.

I could take all the rows until 2017 and paste them in another excel file, but then all my reports will not show data until 2017.

I have two questions:

1. Is there a way to significantly reduce the file size without removing the rows? maybe something to do with formatting (there's a lot of condtional formatting and generally formatting involved)
2. Otherwise, how would you split such a data file, so that you can still see all the data from 2010 till 2020 in one report?

Thanks

A first step : https://www.techrepublic.com/blog/mi...orkbook-bloat/
Do your pivot tables use the same data source ?
Formatting and CF will slow things down also

Originally Posted by oeldere

have you tried to remove the condional format for e.g. 2010 - 2015.

I think that will reduce the file size tremendesly.

I did what you suggested, but it didn't make an impact on the file size at all..

Originally Posted by oeldere

Do you have images in your file?

This also will increase the file size.

No, no images

Originally Posted by oeldere

You did not respond on the usefull reaction in #4 from Pepe Le Mokko

Did you tried that also?

Trying now!

Two questions about the formatting:

1. If I have 500 rows and one of them has another formatting will it do the formatting to 500 cells separately in that case?
2. If I format the whole column inside a table, then it's same as formatting the whole column in the worksheet, right? (I mean in terms of file size)

I have renamed the file to .zip and unpacked it and looking at the xml files and the one that takes 9MB is:

calcChain.xml

I am checking what is this file and what to do about it..

Originally Posted by oeldere

You did not respond on the usefull reaction in #4 from Pepe Le Mokko

Did you tried that also?

I don't have any unused cells problems

I did an experiment, I cleared formatting in all the sheets and the size was reduced by 2MB