PivotTable Percentages - Show Unfiltered Percent when Filtering
Posted on: 03/11/2018
Bill in Smyrna, GA wants to show percentage in a PivotTable and filter the PT. That is easy. The issue is Bill wants to show the unfiltered percentages, not the filtered percentages. I had to add a helper column to do this.
YouTube text – PivotTable Percentage video
The text below was auto-generated by YouTube
Hey, Chris Menard here. I had a great question from a friend up in Smyrna about PivotTable percentages so let’s go take a look at his question he wants to filter a pivot table but show the percentages as an unfiltered amount so if you look at column B I got a list of products and I want to know how many times each product was ordered for this time period which I put it in September 2018 as an example notice that I have a total of 50 records so the first thing is make a table ctrl T now go make the pivot table insert pivot table I’m gonna keep it right here to keep this easy let’s put it right there I’m gonna check class cuz I’m worried about the products so there’s my products I’m gonna drag class and drop it into values so there’s the 50 records 50 orders and then I’m gonna right click I’m gonna take class again put it in values at your second time right click summarize values as percentage of grand total so those 50 there’s your hundred percent but here’s what Bill wants to do he wants to just filter by the top three which is actually pretty easy in fact I can just leave this here I’m gonna go and sort this by percentage you don’t actually technically have to do it I’m gonna copy and I will paste the value so I can keep up with this so they’re the percentages that Bill wants to see 28:22 and 14 the issue is when he goes to value filters top 10 changes that to 3 and hits okay 30 to 250 100% this is actually correct but Bill wants to see 28 22 in 14 he knows it’s not going to total 100% he’s okay with that so that is his dilemma I was hoping somewhere in here that pivot table tools analyzed would give me an option to keep these percentages based on the unfiltered data but I couldn’t find it so here’s my workaround I’m going to come over here name it percentage 1 and my formulas count a count a or count texts count ale account numbers and I’m going to count so right now should get the number 1 I do so let me edit that formula and I’m will divide it by the sum of count a b2 through B 51 and then I’m gonna hit the f4 function key to make those absolute reference and so she basically taking the number one dividing by 50 in this example and there is your calculated column working so let me go make these percentages just to keep it clean click back in my pivot table do a refresh I can lose this one right here because it’s not what I want to see I’m gonna take that percentage one drop it in here and watch this there you go 28:22 and fourteen and Bill doesn’t care that equals 100 he wanted to see these numbers.
Upcoming Excel Training with Chris Menard
April 20, 2018 – Chris Menard will be presenting Excel & Word tips to make your life easier! at the University of Georgia Administrative Professional Day Conference in Athens, GA. Registration is open to the public.
Chris Menard
Chris Menard is a certified Microsoft Trainer (MCT) and works as a full-time Trainer at BakerHostetler - one of the largest law firms in the US. He runs a YouTube channel with 900+ technology videos that cover various tools such as Excel, Word, Zoom, Teams, Gmail, Google Calendar, and Outlook. To date, the channel has helped over 20 million viewers.
Menard also does 2 to 3 public speaking events every year, presenting at the Administrative Professional Conference (APC), the EA Ignite Conference, the Support Staff Conference, the University of Georgia, and CPA conferences. You can connect with him on LinkedIn at https://chrismenardtraining.com/linkedin or watch his videos on YouTube at https://chrismenardtraining.com/youtube.
Categories