Excel Pivot Table Row Labels Not Refreshing

Question

To all,

I have an Excel spreadsheet pivot table that is linked to an SSAS data source. The employee counts are aggregated and return the following values:

Row Label Count

North America 7
South America 2
Europe 5
Asia 1
Australia 3

Let's say the user modifies the South America Row to read "Antarctica" by accident, and then the spreadsheet is saved. Now we have the following:

Row Label Count

North America 7
Antarctica 2
Europe 5
Asia 1
Australia 3

Which we know is wrong. I refresh the data and the counts can change, but the row label remains "Antarctica" and does not change back to "South America". I've check a number of Excel Pivot table settings but to no avail.

Anyone able to solve this problem?

Thanks - Mr. Do

Answer 1

The reason why rows label can't be updated is that pivot table can only refresh datas in values field. Solution as follows: 1. Open your PivotTable fields setting window, move the items("continent" in your case)from the row field to values field. 2. refresh PivotTable 3. move the items "continent" from values field back to rows field.

You'll see row label go back to what it is like in data source.

And this move won't change any of your other parameters settled before.

Answer 2

You can delete pivot table and create it anew
Not sure if this method is good enough for you :)

Answer 3

set sorting on the row and set autosort(default). This should solve Your problem.

Answer 4

This code search through each pivot item in each pivot table and replaces the caption with the original name for row and column field items.

Sub CleanCaption()
For Each sht In Worksheets
    For Each pvt In sht.PivotTables
        For Each fld In pvt.PivotFields
            If Not IsError(fld.Position) Then
                For Each itm In fld.PivotItems
                    If Not itm.Caption = itm.SourceNameStandard Then itm.Caption = itm.SourceNameStandard
                Next
            End If
        Next
    Next
Next
End Sub

Answer 5

Overwriting the value on the pivot table row label also works.

In your example, overwrite the value on the pivot table changing 'Antarctica' back to read "South America". Refreshing seems to work again for me OK.

Answer 6

I encountered a version of this same problem. When I added in new data to source data, off of which an existing pivot table runs, I didn't see the new data when I refreshed the pivot table.

The "native" solution I found was to:

1. Rename the problem column in the source data.
2. Refresh the pivot table, and since that column name has changed, the pivot table will remove that data field.
3. Edit the pivot table so it now is selecting the renamed column.

... and then your new data should show

Answer 7

The "PivotPower Free add-in" at http://www.contextures.com/xlPivotAddIn.html can do this (without needing the "Premium" version). After installing, use Add-Ins -> PIVOT -> Formatting -> Reset Captions.

Still looking for a "native" solution.

Answer 8

The overwritten row item can be refreshed by the following steps:

• Drag the row field out of the pivot table.
• Right click on the pivot table and select 'Refresh'.
• Drag the row field back onto the pivot table.