Rename Pivot Table Items

[karattop]

I've done this and I don't remember how... I am creating calculated items in my PT that are based on the "Account Number" field in my source data. The account numbers are from my POS, so they are long strings, like "000000051002". There is a limit to how many characters I can use in the Formula field, so I somehow renamed the Item to "51002" or in the case of expenses, I used something like "EX12". I did not change the source data. It still says "000000051002".

Now there are some additional fields that I need to rename, and I can't figure out how to do it.

Also, I need to figure out which account number corresponds to "EX12".

I've searched all over in MS and WWW and can't find a thing on renaming items without going through all the source data and changing all of them. I have 11,000 lines of data.....

 

[GlennUK]

To rename a field, in the Layout option of the PivotTable Wizard click or right-click the field and choose Field Settings and type over the old name in the Custom Name field. If you look at the list of availble fields you can see which field is which by position ... that's the easiest way of telling what EX12 is.

 

[karattop]

I'm not trying to rename a Field, but an Item. In 2010 I click on "Fields, Items, & Sets" and then choose "Calculated Item." In the Items box I see the list of modified Items, and some that I didn't modify. For example, I didn't modify '000000040112' but I did modify '000000040113' to '40113' so that when I used that Item to calculate the new Item in the upper-most box (Name) I wouldn't run out of characters so quickly in the "Formula" box. One of them I modified to EX12 (was it 000000070132 or 000000070345?) to further save character space. All of these items are part of the "Account" Field. I didn't change that name - it is still called "Account" and that is the header in the source data.

Now, not only do I need to modify 000000040112, I need to know what the original Item was that I modified to EX12.

I remember doing it, I just don't remember how! A bad case of OA!

BTW, how do I get the old-fashioned wizard in 2010?

 

[GlennUK]

To rename an item you just type over it in the pivottable itself.

As for finding out what an items original name was, I think you'll need VBA. This macro resets all items to their original names ( you can tweak for your purposes ):

'=============================
Sub ResetCaptions()
'retrieve original field names
'if captions have been typed into pt
On Error Resume Next
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Dim lngCountChg As Long, lngpfCount As Long, lngpiCount As Long
lngCountChg = 0: lngpfCount = 0
Set pt = ActiveSheet.PivotTables(1)
For Each pf In pt.VisibleFields
    lngpfCount = lngpfCount + 1
    lngpiCount = 0
    For Each pi In pf.PivotItems
        lngpiCount = lngpiCount + 1
        If pi.Caption <> pi.SourceName Then
            pi.Caption = pi.SourceName
            lngCountChg = lngCountChg + 1
        End If
        Application.StatusBar = "Processing " & pf.Name & " ( field " & lngpfCount & " of " & pt.VisibleFields.Count _
            & " ) item " & lngpiCount & " of " & pf.PivotItems.Count & " - changes made = " & lngCountChg
    Next pi
Next pf
pt.RefreshTable
Application.StatusBar = False
MsgBox "Processing complete. Number of changes made = " & lngCountChg
End Sub
'================================

Oh, and to get the old Wizard ( or part of it anyway ) do Alt-D-P

 

[karattop]

Thanks GlennUK!! The key there was to select the item from the drop-down at Row Labels in the pivot table so editing could be done directly in the PT on the worksheet. Since I was using the items to calculate other items, I'd deselected them. Thus I couldn't find a place to edit them.

 

[GlennUK]

Glad to help!