Excel: How to clear all renamed PivotTable labels

Question

When using PivotTables in Excel, it's often useful to overtype certain PivotTable labels with new names.

If you have a long list of items you've relabelled, it can be easy to lose track of what the original labels were. The only method I've found to restore the original labels is to go into the source data, dig all the original labels out, and change the PivotTable label back one-at-a-time.

I have over 50 renamed labels, this is tedious.

Is there any way to clear all renamed PivotTable labels for a given field, in one fell swoop?

Edit: I'm using Excel 2010, but interested in any methods available in later versions as well.

You have not mentioned which version of Excel you are using – Firee Mar 27 '14 at 11:52 — Firee, Mar 27 '14 at 11:52

score 10 · Accepted Answer · answered Mar 27 '14 at 16:53

10

Rename the column header/name in the source
Refresh the pivot. This will drop all forced labels and drop field from the pivot.
Rename the column name back to what it was
Refresh pivot again
Add the field back into your pivot.

answered Mar 27 '14 at 16:53

Madball73

2,410
1
14
16

2

If I'm using a pivot table that connects through an Analysis Services cube (i.e. I don't have direct access to the source data columns), is there an alternative method? – psyk0 Oct 31 '14 at 10:44
1

@psyk0, there doesn't seem to be an easy answer for this with SSAS... more at http://stackoverflow.com/questions/998185/excel-pivot-table-row-labels-not-refreshing – Sean Summers Dec 29 '14 at 19:44

Marcus Poetting · Answer 2 · 2017-05-29T14:23:59.663

2

This macro will remove all captions from the row labels and column labels in all pivottables in a workbook. This will NOT work with PowerPivot Tables.

Sub FixPivotItemCaptions()
Dim pi As PivotItem
Dim pt As PivotTable
Dim wb As Workbook
Dim ws As Worksheet
Dim pf As PivotField

Set wb = ActiveWorkbook

Application.ScreenUpdating = False
For Each ws In wb.Worksheets
    For Each pt In ws.PivotTables
        For Each pf In pt.PivotFields
            For Each pi In pf.PivotItems
                pi.Caption = pi.SourceName
            Next
        Next
    Next
Next
Application.ScreenUpdating = True
End Sub

edited May 29 '17 at 14:23

answered May 29 '17 at 12:17

Marcus Poetting

21
2

For me this ended up replacing the labels with their MDX representation (`[Dimension name].[...].&[999999]`) and throwing an error for a SSAS-backed pivot table in Excel 2016. – Nickolay Apr 12 '18 at 10:01

score 0 · Answer 3 · answered May 17 '17 at 11:41

If the column in question is already in several Pivots and there is some effort or troubles to re-add the column or you fear to destroy some layouts for example - then I propose the following easy way:

Rightclick anywhere in the pivot, select "Pivot Table Table Options", select the "Data" Tab and Change "Number of items to retain per Field" to none.
refresh the pivot

And there we go, old field label item ("aliases") are forgotten. You possibly want to rename the data aliaes back to where you want them, and possibly undo the "Number of items to retain per Field" to automatic.

I tried this and it didn't work. Is this perhaps because you are using an external data source? With a regular worksheet as the datasource it didn't seem to take. — rohrl77, Feb 14 '20 at 09:25

score 0 · Answer 4 · edited Nov 12 '21 at 09:46

0

Or just reset a single pivot item with some VBA code:

Sub ResetSinglePivotItemCaption()

   Dim pivItem As PivotItem
   Set pivItem = Selection.Cells(1).PivotItem
   pivItem.Caption = pivItem.SourceName

End Sub

edited Nov 12 '21 at 09:46

CentrixDE

736
5
23

answered Nov 12 '21 at 08:57

Hartmut

1

score 0 · Answer 5 · answered Mar 31 '23 at 14:27

Another option, if you can't use the 'rename the original column' option for whatever reason:

First, clear Use Custom Lists when Sorting. i.e. Unset Pivot Table Options -> Totals & Filters -> Sorting: Use Custom Lists when Sorting

Then use Sort A-Z on the Row Labels.

This will put the pivot table into the 'right' order for the original labels.

As long as you know what the original labels are, you should now be able to restore them.

Just be aware that rows will jump around as you do - you might need to repeat the sort after making each change.

score -1 · Answer 6 · answered Apr 08 '15 at 01:56

-1

you can allow excel to forget items it hasn't seen in the most recent update, then load a dummy table (select top 1 from ).

Some variant on this will work for all versions of excel.

http://www.contextures.com/xlPivot04.html

answered Apr 08 '15 at 01:56

Andrew Hill

107
2

The link talks about forgetting items no longer in the data source... that's not what my question refers to. I was asking about items that do exist in the data, but have been renamed in the pivot table. – Andi Mohr Apr 08 '15 at 08:26
i agree, that's why i also said to load a dummy table with only 1 row, -- this would move all the custom named items into the set of data to be cleared from the cache, so that when you switch back to the normal table, it's forgotten all your renaming. – Andrew Hill Apr 08 '15 at 22:35
Ah I see - actually remove all the data from the data source but retain the headings. That's probably overkill when I only want to drop all the renamed labels from a single column (and retain renamed labels in other columns) Thanks for the tip - will be useful in certain situations. – Andi Mohr Apr 09 '15 at 08:30

Excel: How to clear all renamed PivotTable labels

6 Answers6