Jason from Texas asks about formatting percentages in a pivot table. I thought this would be an easy question, but Jason wants to format the grouped percentages in the row area of the pivot table. Episode 912 talks about grouping in a pivot table but doesn't come up with a good solution to the problem.
Transcript of the video:
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
I'm going to be gone for a week.
I'm heading down to Brazil-- big yacht trip there, just a fun trip, not a seminar trip or anything like that.
So if you happen to be watching this, if you have some questions for the podcast, go ahead and shoot me a note: bill@mrexcel.com.
Recording some podcasts ahead here, but I hope to do a few podcasts down in Brazil with an interesting backdrop.
So send me your note and we'll see if we can get to you on the podcast.
Now, question today sent in by Jason, and I'm going to talk about Jason's question, although I'm not going to ultimately solve the final question.
We have a data set here that you'll see has a Rate field-- that's a percentage-- and when we create a Pivot Table, it tries to use that field.
It's a little frustrating that the rate does not come through looking like an interest rate; it ends up looking like a decimal.
And so the standard solution to this, is we want to choose that field and go to the Field Settings button.
Now, in Excel 2003, the Field Settings button is this blue "i".
I think it was the second to the last icon on the PivotTable toolbar.
So I choose Field Settings, and then there's an item for a Number format, and we can go in and change this to be a Percentage, and it will now remember that that field should be formatted as a percentage.
Okay.
And that's, you know, simple enough.
I guess here, we actually have to change that to an average, click OK.
And that's good if you want your item in the data area.
But what if we're putting our item somewhere else?
So let me start again.
I'll create a new Pivot Table and this time I'm going to put the Rate down the row labels, and the Customer down to Sum Value.
So this basically shows me the number of Customers that are at every individual Rate.
Now, from here, it does start out looking like a percentage.
It takes the formatting from the original data set-- that's great.
But I'm going to go into Group Field, and with Group Field I can group things into various categories.
So I want to see it in a half percent increments.
So from 5% to 15% in .005 increments, click OK.
And now, it ignores my formatting and it comes here, and basically it gives me some text that doesn't look like an interest rate at all.
So if I go into Field Settings now, you know, again, we can't really change the number format it's already a percentage.
It just has a very strange way for Excel to have formatted that text.
Now, I guess we could come outside of the Pivot Table and try and format that, but very frustrating that Excel doesn't give us a good way to go in and change the format of the grouped field once it's over in the row area.
Still though, cool trick being able to take those individual percentage rates and roll them up, besides the fact that it doesn't look exactly as we want it.
It is a great way to take a look at that data and see it, basically, almost as the distribution showing us the number of items that fall into various buckets.
So I want to thank Jason for sending that question in, and sorry Jason that we don't actually have an answer for it.
I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel.
I'm going to be gone for a week.
I'm heading down to Brazil-- big yacht trip there, just a fun trip, not a seminar trip or anything like that.
So if you happen to be watching this, if you have some questions for the podcast, go ahead and shoot me a note: bill@mrexcel.com.
Recording some podcasts ahead here, but I hope to do a few podcasts down in Brazil with an interesting backdrop.
So send me your note and we'll see if we can get to you on the podcast.
Now, question today sent in by Jason, and I'm going to talk about Jason's question, although I'm not going to ultimately solve the final question.
We have a data set here that you'll see has a Rate field-- that's a percentage-- and when we create a Pivot Table, it tries to use that field.
It's a little frustrating that the rate does not come through looking like an interest rate; it ends up looking like a decimal.
And so the standard solution to this, is we want to choose that field and go to the Field Settings button.
Now, in Excel 2003, the Field Settings button is this blue "i".
I think it was the second to the last icon on the PivotTable toolbar.
So I choose Field Settings, and then there's an item for a Number format, and we can go in and change this to be a Percentage, and it will now remember that that field should be formatted as a percentage.
Okay.
And that's, you know, simple enough.
I guess here, we actually have to change that to an average, click OK.
And that's good if you want your item in the data area.
But what if we're putting our item somewhere else?
So let me start again.
I'll create a new Pivot Table and this time I'm going to put the Rate down the row labels, and the Customer down to Sum Value.
So this basically shows me the number of Customers that are at every individual Rate.
Now, from here, it does start out looking like a percentage.
It takes the formatting from the original data set-- that's great.
But I'm going to go into Group Field, and with Group Field I can group things into various categories.
So I want to see it in a half percent increments.
So from 5% to 15% in .005 increments, click OK.
And now, it ignores my formatting and it comes here, and basically it gives me some text that doesn't look like an interest rate at all.
So if I go into Field Settings now, you know, again, we can't really change the number format it's already a percentage.
It just has a very strange way for Excel to have formatted that text.
Now, I guess we could come outside of the Pivot Table and try and format that, but very frustrating that Excel doesn't give us a good way to go in and change the format of the grouped field once it's over in the row area.
Still though, cool trick being able to take those individual percentage rates and roll them up, besides the fact that it doesn't look exactly as we want it.
It is a great way to take a look at that data and see it, basically, almost as the distribution showing us the number of items that fall into various buckets.
So I want to thank Jason for sending that question in, and sorry Jason that we don't actually have an answer for it.
I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel.
