Share via

Externally Referenced Data in Pivot Tables not Refreshing correctly

Anonymous
2017-07-12T13:27:17+00:00

I have a main excel file which is populated with multiple pivot tables that was originally referencing data on another sheet within the same excel file. I wanted to move the information that this pivot table was referencing to a separate file so that there could be a master file in which information could be changed/updated and all who were using the main excel file would automatically see these changes.

So I created a new document (We'll call it Test1), and made a copy of the sheet containing all of the pivot tables (from the original document) in this new file. Then I created a new query from a file that referenced the master excel file (call that Master1) with all the relevant data. This populated a separate sheet in the Test1 document in sheet 2. I then changed the data source of the Test1 pivot tables (on sheet 1) to reference the information that was brought in to sheet 2.

I set the both the pivot tables in sheet 1 and the referenced data in  sheet 2 to AutoRefresh on start, but this is where I ran into my problem. If I change data in Master1 then save the changes and then go and open my Test1 document, I find that sheet 2 (that is pulling it's data from Master1) has reflected the changes I made, but the Pivot tables in sheet 1 do not. When I manually hit the refresh button in sheet 1 the changes appear but I need this to work automatically.

Is there anything I can do to fix this or is this just a symptom of how the data is structured? Using Excel 2016 for reference. Thanks ahead of time for anyone that can help me.

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
  1. Anonymous
    2017-07-12T14:47:53+00:00

    Tom! 

    You're the best! I've been banging my head against the wall about this for hours. Thank you so much for your help.

    To anyone with this problem in the future: 

    -I went into the sheet with the imported data and selected the connections properties, and in that sub menu I unchecked "Background refresh".

    • I also went into the "Query Tools" tab and selected "properties" on the main ribbon and checked the box title "Fast Load Data"

    These things combined absolutely solved my problem.

    • Jack
    0 comments

3 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2017-07-12T14:36:03+00:00

    If I had to guess, I would guess that your pivot table updates first and then your source data updates or your source data takes a measurable amount of time to update and the pivot table updates against the non-updated data in the meantime.  I don't know if there is a definitive source of information on the order in which tables are updated.   Possibly in your source data, you are updating with a background query.   You might uncheck the background query option. 

    --

    Regards,

    Tom Ogilvy

    1 person found this answer helpful.
    0 comments
  2. Anonymous
    2017-07-12T14:25:04+00:00

    Tom, 

    I appreciate your reply but I had actually already selected that in the Pivot table options. It still does not refresh the information until I manually hit the refresh button. Any idea why that might be?

    • Jack
    0 comments
  3. Anonymous
    2017-07-12T14:21:25+00:00

    JackCortez,

    You have to set the option for the pivottable in the options available for the pivottable.  When you select a cell within the pivottable, you will get the pivottools above the ribbon.  click on this and you should see the options in the ribbon.  Under options, go to the data tab and click on the box that says refresh pivottable on file open.

    --

    Regards,

    Tom Ogilvy

    0 comments