+ Reply to Thread
Results 1 to 13 of 13

How to split a file with big data

  1. #1
    Forum Contributor
    Join Date
    05-28-2010
    Location
    Antwerp, Belgium
    MS-Off Ver
    Office 365
    Posts
    170

    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

  2. #2
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: How to split a file with big data

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

    I think that will reduce the file size tremendesly.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  3. #3
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365
    Posts
    12,895

    Re: How to split a file with big data

    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

  4. #4
    Forum Contributor
    Join Date
    05-28-2010
    Location
    Antwerp, Belgium
    MS-Off Ver
    Office 365
    Posts
    170

    Re: How to split a file with big data

    Quote Originally Posted by oeldere View Post
    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..

  5. #5
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: How to split a file with big data

    Do you have images in your file?

    This also will increase the file size.

  6. #6
    Forum Contributor
    Join Date
    05-28-2010
    Location
    Antwerp, Belgium
    MS-Off Ver
    Office 365
    Posts
    170

    Re: How to split a file with big data

    Quote Originally Posted by oeldere View Post
    Do you have images in your file?

    This also will increase the file size.
    No, no images

  7. #7
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: How to split a file with big data

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

    Did you tried that also?

  8. #8
    Forum Contributor
    Join Date
    05-28-2010
    Location
    Antwerp, Belgium
    MS-Off Ver
    Office 365
    Posts
    170

    Re: How to split a file with big data

    Quote Originally Posted by oeldere View Post
    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)

  9. #9
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: How to split a file with big data

    Make a back up of your data => save as and save the file with another name.

    Then try the offered solutions and save the data.

    Then you will know the answer.

    If it is not working, you have the back up file.

  10. #10
    Forum Contributor
    Join Date
    05-28-2010
    Location
    Antwerp, Belgium
    MS-Off Ver
    Office 365
    Posts
    170

    Re: How to split a file with big data

    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..

  11. #11
    Forum Contributor
    Join Date
    05-28-2010
    Location
    Antwerp, Belgium
    MS-Off Ver
    Office 365
    Posts
    170

    Re: How to split a file with big data

    Quote Originally Posted by oeldere View Post
    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

  12. #12
    Forum Contributor
    Join Date
    05-28-2010
    Location
    Antwerp, Belgium
    MS-Off Ver
    Office 365
    Posts
    170

    Re: How to split a file with big data

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

  13. #13
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: How to split a file with big data

    I read a while ago that restoring the file as a binaire workbook also could help. Did not tried this myself, so again, make a back up of the file, for you try that.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. macro to split data in text file
    By geliedee in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-23-2020, 11:52 PM
  2. Format data and split unique criteria to new file
    By pauldaddyadams in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 07-02-2018, 01:41 PM
  3. Split Text File Data
    By goropd in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-20-2018, 10:56 PM
  4. [SOLVED] Automatically split file based on data
    By Tapyr in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 10-24-2017, 09:56 PM
  5. [SOLVED] need vba to split data move to particular file sheet depends upon sheetname
    By julielara in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 03-06-2016, 03:05 AM
  6. [SOLVED] VBA Split Data and Move to targeted named file from master file
    By breadwinner in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-19-2013, 01:06 PM
  7. [SOLVED] Loop for search a string, split data, save as new file.
    By johnch in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-29-2012, 01:57 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1