+ Reply to Thread
Results 1 to 4 of 4

Trying to slim down a report we current have and need suggestions on accomplishing.

  1. #1
    Valued Forum Contributor
    Join Date
    05-15-2017
    Location
    US
    MS-Off Ver
    365
    Posts
    896

    Trying to slim down a report we current have and need suggestions on accomplishing.

    So we have a report that we pull in sales data into few times a week. Currently only has 2 months worth of data and already 359k rows. Now from that raw data we have about 12 give or take tabs that are just pivot tables of that data to make key areas easy to analyses when needed. Because everything is all within the file, the 2 month file has grown to 240mb and takes about 2 minutes to open.

    So i have a few questions
    Would splitting the data into 2 file help? (one for just the raw data and one for all the pivot tables)
    Currently the raw data is stored as a named table and all pivot tables have been updated to all point to the same table (this helped reduce the file size)
    Since the data is just going to keep growing, what options do we have to make this a little more efficient to work with and open quicker?


    I copied just the raw data, no formatting and no formulas and those 359k records created a 68mb file, but that one opens in like 10 seconds, so would it make any difference in creating a file that is just all the different pivot tables that all point to that data file?

    Just looking for ways to improve or upgrade this overall process to make it user friendly.
    If you find the suggestion or solution helpful, please consider adding reputation to the post.

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Trying to slim down a report we current have and need suggestions on accomplishing.

    If 2 months of data already occupy about 360k rows, then you will only be able to have about 6 months of data in one sheet before you use up all the available rows.

    Try to think about ways of reducing the amount of raw data, and maybe using an .xlsb extension will improve the loading times for the file.

    Hope this helps.

    Pete

  3. #3
    Valued Forum Contributor
    Join Date
    05-15-2017
    Location
    US
    MS-Off Ver
    365
    Posts
    896

    Re: Trying to slim down a report we current have and need suggestions on accomplishing.

    Interesting, i never thought about saving or using binary.. what if anything would we lose in functionality by saving it as binary?

    so here are the results i got from saving different types:

    Raw Data Only:
    (xlsm) = 65mb
    (xlsx) = 65mb
    (xlsb) = 27mb

    Complete file with Pivot tables and raw data in one file:
    (xlsm) = 238mb
    (xlsx) = 238mb
    (xlsb) = 213mb


    Would there be any improvement or benefit in keeping the raw data in its own xlsx file with nothing but the one sheet and no macros and having a second workbook which pulls in the data for the pivot tables?

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Trying to slim down a report we current have and need suggestions on accomplishing.

    You might consider putting your source data into a Data Model and producing the Pivot Tables from the Data Model.
    As I understand the Data Model can hold approximately 99 M rows of data so at 1 M rows per six months or 2 M per year that should provide almost 50 years worth of storage.
    https://support.microsoft.com/en-us/...7-40896795d045
    Note that I have had some problems producing the types of Pivot Tables I would like from Data Models, so you may want to do some testing.
    Tutorial (starting at the 21:50 mark): https://www.youtube.com/watch?v=ohGFPF12Qwc&t=1275s
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ 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. http://www.healthyorder.org/radiantly-slim-diet/
    By Suesrese in forum Excel General
    Replies: 0
    Last Post: 06-28-2018, 03:27 AM
  2. Replies: 5
    Last Post: 06-20-2018, 01:30 PM
  3. Replies: 4
    Last Post: 04-07-2018, 06:28 AM
  4. Report Suggestions?
    By Lautermilch in forum Excel General
    Replies: 1
    Last Post: 08-29-2011, 02:58 PM
  5. Suggestions for lining up Day for current & prior months
    By Air_Cooled_Nut in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-09-2011, 10:43 AM
  6. Merging Macros & Accomplishing conditional data manipulation
    By Tungar in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-13-2005, 06:43 AM
  7. Slim Jim
    By Counting Records \(correct version\) in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-13-2005, 05:06 AM

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