Excel: How to clear all renamed PivotTable labels
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.
- 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.
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