Change source's column Name in a workbook with many pivot tables

My Excel 2010 workbook has 5 pivot tables in sheet 2 through sheet 6. All use the same data source from sheet 1. I will oversimplify and pretend the data source looks like this

Building    part   qty   cost   Total value
bldg1         bolt   500   10      5000

I want to change the column heading from "part" to "model". Must I manually revise each of the 5 tables to change "part" to "model" or is there a way to do this automatically in a single step?
LVL 5
rberkeConsultantAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ChloesDadCommented:
If they are all the same cell position on each sheet, then you can select all sheets from the sheet selection control in the bottom left. I.e. click on Sheet2, then press shift and click on Sheet6.

Then just edit the cell (B1 in your example) on the active sheet and it will change on all selected sheets. remember to then click on a single sheet again to unselect all the sheets.

If its in a different position on each sheet then this method will not work.
0
rberkeConsultantAuthor Commented:
I don't understand your instructions at all - have you tried them in Excel 2010?

In Excel 2010, when I want to change a pivot table, I click on a cell inside of it. Immediately after I click, "PivotTable Tools" appears on the ribbon. That is the essential set of tools that allow me to modify the tables.

When selecting two sheets simultaneously the "PivotTable Tools" disappears, so it is impossible to use them to affect the pivot table.

And, any attempt to alter any of the pivot table cells results in the message "cannot edit a PivotTable in group edit mode"

Try it yourself,
Create a workbook with only Sheet 1.

In sheet1 put in data like is specified in my first post. Put the word "part" is in cell b1.

Use the pivot table wizard to create two tables with both havingPart as a row label.  

Excel will create Sheet2 and Sheet3  both of which will have the word "part" in cell A5.

Now go to sheet 1 and try to change the word Part to "Model".
When you refresh the pivot tables, they all get crushed.
0
rberkeConsultantAuthor Commented:
Nobody answered my question, so I am proposing the following answer.

-----
Title: how to change the source column's Name in a workbook with many pivot tables ----

Question: My Excel 2010 workbook has 5 pivot tables in sheet 2 through sheet 6. All use this data source from sheet 1.
Column A     B      C     D         E
Building    part   qty   cost   Total value
bldg1         bolt   500   10      5000

I want to change column B's heading from "part" to "model" in sheet 1, with minimal effects on the pivot tables in sheets 2 through 6.

----- my proposed answer -------------


There is no simple answer, but adding a temporary column makes the adjustments easier.
1) Backup up your workbook and print it.  When you are done, you will want to compare your adjusted workbook to the backup and make sure nothing was inadvertantly changed.

2) Copy Column B and paste to the right of column B.  Use Paste special values because formulas can mess things up.

3) Change the Column name from "Part" to "Model" in the original column B (not the temporary column that you just pasted)

Repeat step 4 to 10 For each of the 5 pivot tables.

4) Click inside the Pivot table.  The "PivotTable Tools" will appear on the ribbon.

5) click Options > Refresh > Refresh All. This will cause every pivot table to include both "Model" and "Part" fields

6) Write down a few key subtotals and grand totals for each pivot table.

7) Click on: PivotTable Tools > Options > Display > Field List. This will display the "PivotTable Field List" which is mighty powerful in Excel 2010.

The attached JPG show the 5 window panes that are in this wizard.

8) Drag the word "Model" from the top window pane to every pane that already has the word "Part".

    If necessary, make the field consistent.  For instance if the old field was "Max of Part", you will need to do the following to "Model".
     
    Click "Model" dropdown arrow.
    click <Value Field Setting>
    Choose "Max" for the drop down list.


9) In the top window pane, remove the checkmark next to "Part".

10) verify that your new pivot table has the same subtotals and grand totals that were in the original.


----------- repeat steps 4 to 10 ---------
When you are done, delete the temporary column from Sheet 1 and compare all 5 tables to the printout you made in step 1.

With practice this only takes about 30 seconds per table. Lots of things can go wrong, but usually they don't.


rberke

P.S.  Only do Step 5 once, it does not need to be done for all 5 Pivot tables..
5-Panes.jpg
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

khobsonCommented:
I would imagine that a macro could automate it for you.  I don't work with pivot tables much so if you post a simple workbook, it would be easier to help.
0
rberkeConsultantAuthor Commented:
I have attached a workbook that demonstrates some of the problems. That workbook caused me to add the following steps to my proposed answer,  None of the other steps are changed.


4B)  (optional) If the worksheet uses cells outside of the pivot table borders it is best to surround the table with several empty columns and empty rows. Otherwise you will receive annoying messages that ask:  “Do you want to replace the contents of the destination cells?”.

 
8B)  (optional)  If worksheet cells outside of the table borders reference cells inside the borders, those formulas may suddenly show #VALUE. Ignore this for a while, they may correct themselves at step 9.

8C)    If necessary, make the Model field consistent with the original Part field.  For instance if the old field was "Max of Part", you will need to do the following to "Model".
     
    Click "Model" dropdown arrow.
    click <Value Field Setting>
    Choose "Max" for the drop down list.
pivot-demo.xlsx
0
khobsonCommented:
I am not sure what you are doing with all those steps.  

If you just want the label changed, in a Module, run this:
Sub PartToModelInAllPivotTables()
  Dim ws As Worksheet, pt As PivotTable
  On Error Resume Next
  For Each ws In Worksheets
    For Each pt In ws.PivotTables
      pt.PivotFields("part").Caption = "Model"
    Next pt
  Next
End Sub

Open in new window

0
rberkeConsultantAuthor Commented:
It turns out to be difficult to rename a column of a pivot table's source.  None of the experts had any helpful suggestions, so I am accepting my own solution.
0
khobsonCommented:
Apparently, you did not try my macro.
0
rberkeConsultantAuthor Commented:
khobson:  


If you feel you can do a good job at a macro, give it a try and post your attempts.  If they look promising, I could open a new question and we can collaborate on improving it.


We could easily automate a few of the steps, but a general solutions is beyond my abilities.

Pivot tables have a huge number of options and variations most of which are influenced by the name of the source column.  

I've successfully tackled thousands of small macros, hundreds of large macros and dozens of huge macros, but this particular one seems unlikely to be worth the effort,
0
khobsonCommented:
I already did an excellent job with the macro and posted it.  What it does is "Change source's column Name in a workbook with many pivot tables".

If there was a need to do something else other than change a column name, then it should be detailed in another question.
0
rberkeConsultantAuthor Commented:
khobson: I now understand better.  See if you agree with the following.  If you do, I will have the moderator give you points.

LATEST "ACCEPTED ANSWER":



SIMPLE WAY TO FIX JUST SHEET2 TO SHEET5:

Changing sheet1 ! b1 from "Part" to "Model" is difficult because it is likely to damage your pivot tables.

Fortunately, changing sheet2 through sheet5 to show "Model" is easy and safe.  In each of those sheets, right click on the word "part" then choose field settings and change the custom name from part to Model.  

Or, you can use the following macro from KHobson.
Sub PartToModelInAllPivotTables()
  Dim ws As Worksheet, pt As PivotTable
  On Error Resume Next
  For Each ws In Worksheets
    For Each pt In ws.PivotTables
      pt.PivotFields("part").Caption = "Model"
    Next pt
  Next
End Sub

Open in new window


Please note, both the manual approach and the macro will refresh the table, so you might want to turn on the "Preserve Cell Formatting on Update" option.

SLIGHTLY MORE DIFFICULT WAY TO FIX SHEET1


To make sheet1 ! B1 show "Model" the simplest approach is to create a text box with the word "Model" in it, and fit that text box over Sheet1!b1.  

the cell's value is still "Part", but people looking at the spreadsheet will see "Model".


DIFFICULT WAY TO FIX SHEET 1, AND SHEET2 TO SHEET5.

The 10 steps outline in my third post above will do the trick, but be careful, they are likely to damage the pivot tables.
0
khobsonCommented:
Points don't matter to me.  I just try to help people.

He is another snippet for fun.  I made it so each run will toggle it from "part" to "Model" and vice-versa. To test with multiple pivottables, I used your xlsx file and copied the sheet with the pivottable as I did for the first example code.

Sub PartToModelInAllPivotTables()
  Dim ws As Worksheet, pt As PivotTable
  Dim s As String, r As Range, s2 As String
  
  Set r = Worksheets("Sheet1").Range("B1")
  s = r.Value2
  Select Case s
    Case "part"
      s2 = "Model"
      r.Value2 = "Model"
    Case "Model"
      s2 = "part"
      r.Value2 = "part"
    Case Else
  End Select
  
  On Error Resume Next
  For Each ws In Worksheets
    For Each pt In ws.PivotTables
      pt.PivotFields(s).Caption = s2
    Next pt
  Next
End Sub

Open in new window


Since damage can be done, always test macro code on a backup copy of the workbook.
0
rberkeConsultantAuthor Commented:
Well, if points don't matter, we will not bother the moderator.  And you most certainly did help me,  Thanks.

Bob
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.