Reputation:

Excel Pivot Table Row Labels Not Refreshing

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

Upvotes: 5

Answers (9)

Brad

Reputation: 1

I found this after encountering a similar issue but with my measure column names. Since I can't move these measures to the Row Field, I wasn't able to implement the "move items, refresh, move back items", but after much searching I was at least able to see what the original Source Table column name was in the column's Value Field Settings:

This way you can at least check and manually rename measures back if someone has polluted them.

Upvotes: 0

Sean Summers

Reputation: 2684

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.

Upvotes: 0

Hang ZHONG

Reputation: 21

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.

Upvotes: 2

zack

Reputation: 1

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:

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

... and then your new data should show

Upvotes: 0

Dougydug

Reputation: 1

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.

Upvotes: 0

Marcus

Reputation: 1

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

Upvotes: 0

ZZZZ

Reputation:

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

Upvotes: 0