+ Reply to Thread
Results 1 to 2 of 2

Pivot external data source -> binary excel files -> extremely slow refresh

  1. #1
    Registered User
    Join Date
    09-30-2006
    Posts
    10

    Pivot external data source -> binary excel files -> extremely slow refresh

    Hello,

    At my work we use an excel file to register quality data.
    This excel file was full of rows of the same formulas and format etc. So the loading was slow and due to the massive data and formulas also.

    I got a request to minimize the file.
    What I did was using names for the different formulas and refered to that in the cells.
    Further I stored the file as a binary file.

    Result is that it is 60% smaller file size and approx loads and saves 50% faster.

    However I stumbled upon a unforseen problem.

    A lot of other excel files are coupled by pivots to this quality data excel file.
    Normally the refreshing of these pivots took not very long... several seconds.
    But now that I turned the source excel file into a binary file the refreshing of the pivots linked to it takes ages.

    I do want to keep the fastness of the binary file since this source file is used very much, but I also want the pivots that use it as a source that they refresh fast.
    Does anyone know how I can make that happen? It seems to me that it is either a fast/slow source file or a slow/fast linked pivot refresh.

    Thanks for any solution!
    Last edited by 01mercy; 12-04-2014 at 08:24 AM.

  2. #2
    Registered User
    Join Date
    02-21-2014
    Location
    Connecticut, U.S.A.
    MS-Off Ver
    Excel 2010
    Posts
    52

    Re: Pivot external data source -> binary excel files -> extremely slow refresh

    Question for you. Why is it that you have other excel files pointing to the same source data? Can't you keep the excel files and the source data together in one file? How do the other excel files interact with the source data that require them to be separate files?

+ 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. Can't Refresh Pivot Table When Using External Data Source
    By yenping.fu in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 05-02-2014, 06:24 AM
  2. Automatic refresh of pivot table on changes in external data source.
    By namrata773 in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 12-04-2013, 04:43 AM
  3. [SOLVED] Auto Refresh Pivot Table Once External Data source Changes
    By djfatboyfats in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-11-2012, 10:33 AM
  4. Replies: 0
    Last Post: 07-08-2006, 07:45 AM
  5. Replies: 0
    Last Post: 09-28-2005, 09:05 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