Jeremy asks Is there a way to select multiple headings and change all of their "Summarize value field by" at once? I have hundreds of headings, each time I want to change their value by Max, Min, Average, I have to select one heading at a time, it is extremely labor intensive. Is there a way to change field calculation for multiple headings at once?
This episode shows some simple VBA to do this quickly.
How to create a personal macro workbook:
Table of Contents
(0:00) Welcome
(0:41) VBA
(1:55) Looping each field
(2:35) Copying for Sum, Max, Min
(3:10) Other calculations
(3:42) Easier way to run
(4:30) Running all 4 macros
(5:25) Upcoming Webinar
This episode shows some simple VBA to do this quickly.
How to create a personal macro workbook:
Table of Contents
(0:00) Welcome
(0:41) VBA
(1:55) Looping each field
(2:35) Copying for Sum, Max, Min
(3:10) Other calculations
(3:42) Easier way to run
(4:30) Running all 4 macros
(5:25) Upcoming Webinar
Transcript of the video:
Hey, this is a great one today. We have a hundred PivotFields and we need to change them all from sum to average, or count, or max.
From Jeremy on YouTube, he has, he says, over 100 headings, and each time he wants to change their value to max, min, average, and it is extremely labor-intensive. I bet it would be.
Is there a way to change field calculation for multiple headings at once?
Well, oh wow.
It would be so tough, because you'd have to come here, double-click, change from sum to count and just over and over and over.
Yeah. Let's switch over to VBA.
This assumes that you're on a Mac or Windows.
We're going to press Alt+F11 and then view the Project Explorer. Hopefully you have a Personal Macro Workbook.
If not, I'll put a link down in the description below of how to get a Personal Macro Workbook.
Select PersonalXLSB, insert module.
And then, this code right here is the code to change every single field in the values' area to an average.
We define two variables, PT for PivotTable, PF for PivotField.
To make it simple, I just had it work on the active sheet and it grabs your first PivotTable.
If you have multiple PivotTables, then you're going to have to come in here and give it the name. How do you get the name of the PivotTable?
Oh wow. I don't know.
Here it is. PivotTable Analyze.
This is called PivotTable1.
So if you had 10 PivotTables and you wanted to do this to the 10th PivotTable, select the PivotTable, come back here and see what the name is.
And then here where it says 1, you'd have to put "PivotTable1" in quotes like that, although that's just going to be a pain.
For me, I have one PivotTable on a sheet. I'm going to get away with doing this.
Then for each PF, that's PivotField, in the PivotTable data fields, remember the values' area used to be called the data area long ago.
And VBA is set, so we're looping through all the data fields.
And then that PivotField's function is set to XLAverage, and then the loop and then it refreshes the table. Let's come back here.
Let's press Alt+F8 and we'll find that change all to Average.
Now, these are all sums. Click run.
And, amazing at all change to average. You want to be able to do this for multiple items.
So I'll just take this little macro here, control C and paste, and we'll say, "change all to sum".
And here instead of XLAverage, we'll try XLSum we'll paste again.
"Change all max". XLMax, and then min.
You get the idea, right?
And let's see, for the others PF.function equals there we are. There's a whole list.
So it's XLAverage, XLCount, XLCountNums.
XLDistinctCount, which is unusual, that's only going to work if it's in the data model. XLMax, XLMin, XLProduct.
Standard deviation, standard deviation of a population, XLSum.
Ooh, XLUnknown - I wonder what the heck that does? XLVariants and variants of a population.
So that's the whole list right there.
Whichever ones you need, you create a little macros for those. And then, is there an easier way to run these?
What we could do is right click up here and say, "Customize quick access toolbar".
Go to the list of macros.
And we will find change all to average, change all to some, or max, change all to min, change all to some.
We need to change those little icons. So modify, this one is average.
Geez, I don't know. A for average.
Max, I'm going to use arrow pointing up, which I just saw yesterday that one. For min, I'll use an arrow pointing down.
For sum, boy, it'd be nice if they had the Greek letter Sigma in there, wouldn't it?
That would be really, really good.
I long for the days when we had 4,096 icons, instead of these stupid little small list of icons here.
I don't know, smiley face. That's stupid.
I hate the smiley face. Click okay.
All right. So now I have these four icons.
So right now we're already in average. If I click this, it'll change all to max.
There we go, the max. Change all to min.
Man, this is so much easier than the old way. Change all to sum, or change all to average.
All right. There you go.
Great question from Jeremy and great answer.
Thanks to Suat on our team who figured out that code.
I was trying to change the caption and it was just a disaster.
Nice easy code there. So thanks all around.
Hey, check out my webinar, "What's New in Excel". This is on the Retrieve Platform.
Use that "i" in the top right hand corner.
If you happen to catch this live, that's great.
If you're seeing this after the fact, you can still go back and watch the recording. The recording actually is completely searchable.
It's pretty cool. All right.
Well, Hey, I want to thank Jeremy for that great question.
I want to thank Suat for the help of the VBA code. Thanks to you for stopping by.
We'll see you next to for another netcast from MrExcel.
From Jeremy on YouTube, he has, he says, over 100 headings, and each time he wants to change their value to max, min, average, and it is extremely labor-intensive. I bet it would be.
Is there a way to change field calculation for multiple headings at once?
Well, oh wow.
It would be so tough, because you'd have to come here, double-click, change from sum to count and just over and over and over.
Yeah. Let's switch over to VBA.
This assumes that you're on a Mac or Windows.
We're going to press Alt+F11 and then view the Project Explorer. Hopefully you have a Personal Macro Workbook.
If not, I'll put a link down in the description below of how to get a Personal Macro Workbook.
Select PersonalXLSB, insert module.
And then, this code right here is the code to change every single field in the values' area to an average.
We define two variables, PT for PivotTable, PF for PivotField.
To make it simple, I just had it work on the active sheet and it grabs your first PivotTable.
If you have multiple PivotTables, then you're going to have to come in here and give it the name. How do you get the name of the PivotTable?
Oh wow. I don't know.
Here it is. PivotTable Analyze.
This is called PivotTable1.
So if you had 10 PivotTables and you wanted to do this to the 10th PivotTable, select the PivotTable, come back here and see what the name is.
And then here where it says 1, you'd have to put "PivotTable1" in quotes like that, although that's just going to be a pain.
For me, I have one PivotTable on a sheet. I'm going to get away with doing this.
Then for each PF, that's PivotField, in the PivotTable data fields, remember the values' area used to be called the data area long ago.
And VBA is set, so we're looping through all the data fields.
And then that PivotField's function is set to XLAverage, and then the loop and then it refreshes the table. Let's come back here.
Let's press Alt+F8 and we'll find that change all to Average.
Now, these are all sums. Click run.
And, amazing at all change to average. You want to be able to do this for multiple items.
So I'll just take this little macro here, control C and paste, and we'll say, "change all to sum".
And here instead of XLAverage, we'll try XLSum we'll paste again.
"Change all max". XLMax, and then min.
You get the idea, right?
And let's see, for the others PF.function equals there we are. There's a whole list.
So it's XLAverage, XLCount, XLCountNums.
XLDistinctCount, which is unusual, that's only going to work if it's in the data model. XLMax, XLMin, XLProduct.
Standard deviation, standard deviation of a population, XLSum.
Ooh, XLUnknown - I wonder what the heck that does? XLVariants and variants of a population.
So that's the whole list right there.
Whichever ones you need, you create a little macros for those. And then, is there an easier way to run these?
What we could do is right click up here and say, "Customize quick access toolbar".
Go to the list of macros.
And we will find change all to average, change all to some, or max, change all to min, change all to some.
We need to change those little icons. So modify, this one is average.
Geez, I don't know. A for average.
Max, I'm going to use arrow pointing up, which I just saw yesterday that one. For min, I'll use an arrow pointing down.
For sum, boy, it'd be nice if they had the Greek letter Sigma in there, wouldn't it?
That would be really, really good.
I long for the days when we had 4,096 icons, instead of these stupid little small list of icons here.
I don't know, smiley face. That's stupid.
I hate the smiley face. Click okay.
All right. So now I have these four icons.
So right now we're already in average. If I click this, it'll change all to max.
There we go, the max. Change all to min.
Man, this is so much easier than the old way. Change all to sum, or change all to average.
All right. There you go.
Great question from Jeremy and great answer.
Thanks to Suat on our team who figured out that code.
I was trying to change the caption and it was just a disaster.
Nice easy code there. So thanks all around.
Hey, check out my webinar, "What's New in Excel". This is on the Retrieve Platform.
Use that "i" in the top right hand corner.
If you happen to catch this live, that's great.
If you're seeing this after the fact, you can still go back and watch the recording. The recording actually is completely searchable.
It's pretty cool. All right.
Well, Hey, I want to thank Jeremy for that great question.
I want to thank Suat for the help of the VBA code. Thanks to you for stopping by.
We'll see you next to for another netcast from MrExcel.
