Refresh Pivot Tables

**jewels3059** · 07-01-2013, 04:18 PM

Hello,

I have a workbook that contains 4 pivot tables that all pull from the same worksheet. I have updated the worksheet that contains the data and I need the pivot tables to refresh the information. Every time I try to "refresh all", the pivots delete all the information and they are blank, so instead of updating the numbers it deletes everything.. any idea why or how to update the information?

Thanks!!

**teylyn** · 07-02-2013, 07:14 AM

Filter settings maybe? Pivot table source data not defined correctly? Hard to tell without a sample file.

**JosephP** · 07-02-2013, 07:20 AM

how did you update the data? did you delete the rows or clear them?

**jewels3059** · 07-02-2013, 02:20 PM

I copied and pasted the new data in the original sheet over the old data

**JosephP** · 07-02-2013, 03:13 PM

are the column headings exactly the same?

**oeldere** · 07-02-2013, 03:20 PM

Please Login or Register  to view this content.

In that case I think (untested) that the data of the pivot table are not equal to the past ones.

In that case it's not possible to refresh the pivot table.

**teylyn** · 07-03-2013, 04:30 AM

Have you checked and corrected (if required) the source of the pivot table?

When you define a pivot table in Excel 2003, the range that it refers to is set as an absolute reference, for example Sheet1!$A$1:$Z$100

If you delete the rows in sheet 1, the pivot table reference will take this into account and remove the deleted rows fro the reference. So, If you delete rows 3 to 100, the pivot table source data reference will change to Sheet1!$A$1:$Z$2

If you then paste new data into sheet 1, the pivot table reference will not automatically include that data. The pivot source still sits at Sheet1!$A$1:$Z$2

If you want the pivot table data source to grow and shrink automatically when you add and delete data from Sheet 1, then you need to set up a dynamic range as a pivot data source. Debra Dalgliesh has a tutorial about the steps involved for that here: http://www.contextures.com/xlPivot01.html

cheers, teylyn