Faster Field Settings in Pivot Tables

Learn Excel from MrExcel Podcast, Episode 2071: Faster Way to Get to the Value Field Settings in Pivot Tables

Hey, welcome back to the MrExcel netcast, I’m Bill Jelen.  Again, a seminar last week in Huntsville, Alabama, I was talking about Pivot Tables and one of my standard things there in Pivot Tables is to show how to create a Pivot Table with multiple times having Revenue over in the Values area.  So I dragged Revenue down again and again and again and each one of these I'm going to change- I'm going to change this one to just be straight-out Revenue and of course, and this original one here I want to have this just called Revenue but not SUM of Revenue.  And of course, if you would call it Total Sales that's no hassle, you can type a item up there but you can't click up there and type Revenue.  It is already a name in the field so you can't reuse that. So, Revenue space is a great way to get around that.  But then for each of these other ones, I want to change the calculation.  And what I was doing in the seminar is I would click in the column and then come up here to Field Settings.  And click in the column, come up here to Field Settings, click on the column, come from here and it feels like - Becky in Row 2, the famous Row 2 just says, “Why are you doing that?  All you have to do is double-click the heading and absolutely that is a great way to go.” 

Alright, so here, this one we’re going to say is the % of Column Total.  So % of Total, like that.  Ahh, click OK and we get a nice calculation there.  Now, for this one double-click, Show Values As - I want to change this as a Rank.  And we’re going to Rank it Largest to Smallest.  And then finally, double-click the heading and we can go in here: Show Values As and showed as a % Running Total In, in the Customer field, and this will be Running Total %, click OK, alright.  I love those various settings that we can do in a Pivot Table, and now there's a faster way.  Well there is always a faster way, just that now, I know it.  Thanks to Becky in Huntsville Alabama.

Alright, tips like this, Power Excel with MrExcel, 617 Excel mystery solved.  Click that “i” in the top-right hand corner to get to it. 

Alright, so in this episode: Drag the Revenue field to a Pivot Table several times or change the Value field settings for each column to show a different calculation; but rather than choose a cell, click Field Settings or right click and choose Field Settings.  You can just double-click the heading and it'll take us right in.  A couple of other tricks here, if you want to have it called Revenue, I just put Revenue with a space.  I showed you how to create a Percentage of the Column or a Rank or a Percentage Running Total in a Pivot Table, alright. 

Hey, I want to thank you for stopping by.  I want to thank Becky in Huntsville for that awesome trick.  We'll see you next time for another netcast from MrExcel.