"Refresh" a pivot table with changed data? 1

[EcoWill]

Hi T.T. I have been frustrated with the apparent property of pivot tables (Excel 2010) that the data within them does not change when the numbers in the table they are taken from changes. It's as if the data in the pivot tables are no longer connected to the data they are created from.
Is there a way to 'refresh' (update) the pivot table when the table data changes, without completely re-creating and reformatting it? Thanks.

 

[SkipVought]

hi,

Chances are, you have not made the source data table a Structured Table, by using the Insert > Tables > Table feature. Consequently the source data REFERENCE in the PivotTable is a static range reference. You can check that by selecting in the PT and PivotTable Tools > Options > Data > Data Source to observe/change the source reference.

What do you see?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[EcoWill]

Nice to hear from you again Skip. I found what needed right there in Options>Data>refresh. Bingo. So even though I did NOT have the source data in an actual 'table', the 'refresh' function worked just fine, so now I know for next time. Always happy to do business with Tek Tips experts, and I am donating to the cause right NOW!
Problem solved.

 

[lionelhill]

Skip, I am still puzzled. Is this link between tables and pivot-tables new to Excel2010? Using Excel2007 if I define my data as a table, and then create a pivot table based on the table, I still don't have automatic refreshing and have to right-click refresh as necessary (except in one workbook where you kindly suggested a vba update solution!).

Also in the pivot tables options>Data I don't have a Data Source to look at (or am I missing something silly?). I have three check-boxes (one a helpful "refresh data when opening file") and a drop-down about what to do when data are removed from the original source (which I don't really understand) but that's all...

Thanks!

 

[BlueHorizon]

Hi all,

There is no automatic refresh built into PivotTables. It is now, and has been, up to the user to Refresh the data as necessary.



Best,
Blue Horizon

 

[SkipVought]

The refresh is not automatic.

The SOURCE DATA REFERENCE, however, is STATIC unless it is expressed as a Dynamic Named Range or Stuctured Table Reference. This is the 'automatic' that I was referring to.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[EcoWill]

Alright then. How would one go about expressing the the source data as dynamic? More to the point, can it be done without writing code and creating a macro? Because nobody in this company knows how to write VBA code (nor are we interested in learning!) Thanks.

 

[SkipVought]

I already explained that you can "made the source data table a Structured Table, by using the Insert > Tables > Table feature," and then CHANGE the PT source data reference to reflect this reference.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}