Tracy from Texas asks about how to filter a pivot table to only show the rows where the calculated item is not zero. With a data set of invoices and payments, I will show you how to create a pivot table, add a calculated item for Balance, hide the grand total column, then a hack to filter the pivot table to show items with a non-zero balance.
Transcript of the video:
MrExcel podcast is sponsored by eduPOW, opens the $5 courses.
Check out my Excel plan and Excel partners The Dirty Dozen at tinyurl.com/edupow5.
Learn Excel form MrExcel podcast episode 1922.
Filter Pivot Table Where Calculated Item is Not Zero.
I have a great question today sent in by Tracy from Texas.
Tracy has a data set and she is going to create a calculated item to figure out invoice minus payment and she wants to see just the invoices where the balance is not zero.
All right, so, this will be fun.
So, Insert, Pivot Table, I want to create it on an existing worksheet right out here to the right, click OK.
Down the left hand side I want the Invoice number across the top I want type and then I want amount.
So, now, I can see for each invoice what the invoice was and what the payment was.
Let's get rid of the grand total so, back here on the Design tab, Grand Totals, On for Columns Only, which keeps the grand total at the bottom and then on the Analyze tab, Fields, Items & Sets, a new calculated item.
oh, it's grayed out that's because I have to be here along this dimension.
Field, Items & Sets, it back beautiful.
Okay so, is we're gonna call it balance is equal to the invoice amount double-click minus the payment amount.
Click OK, All right, so now, we have invoice payment and balance and I need to filter just the invoices where the balance is non zero and unfortunately there is no way to do that.
Well, no legal way, but what we're gonna do is we're going to use a great trick here that I've used in the podcast a few different times.
I'm gonna come outside of the Pivot Table to the cell immediately to the right of the last set of row, headers across the top and from there I'm gonna do Data and then turn on the Filter.
If you're inside of a Pivot Table, see it's grayed out you're not allowed to use a Filter inside of a Pivot Table, but from right here they happen to filter the adjacent data sets of now balance and we will that we want not the zero values and bam and there's our answer.
All right, I wanna thank Tracy for sending that question in and I wanna thank you for stopping by.
See you next time for another netcast form MrExcel.
Check out my Excel plan and Excel partners The Dirty Dozen at tinyurl.com/edupow5.
Learn Excel form MrExcel podcast episode 1922.
Filter Pivot Table Where Calculated Item is Not Zero.
I have a great question today sent in by Tracy from Texas.
Tracy has a data set and she is going to create a calculated item to figure out invoice minus payment and she wants to see just the invoices where the balance is not zero.
All right, so, this will be fun.
So, Insert, Pivot Table, I want to create it on an existing worksheet right out here to the right, click OK.
Down the left hand side I want the Invoice number across the top I want type and then I want amount.
So, now, I can see for each invoice what the invoice was and what the payment was.
Let's get rid of the grand total so, back here on the Design tab, Grand Totals, On for Columns Only, which keeps the grand total at the bottom and then on the Analyze tab, Fields, Items & Sets, a new calculated item.
oh, it's grayed out that's because I have to be here along this dimension.
Field, Items & Sets, it back beautiful.
Okay so, is we're gonna call it balance is equal to the invoice amount double-click minus the payment amount.
Click OK, All right, so now, we have invoice payment and balance and I need to filter just the invoices where the balance is non zero and unfortunately there is no way to do that.
Well, no legal way, but what we're gonna do is we're going to use a great trick here that I've used in the podcast a few different times.
I'm gonna come outside of the Pivot Table to the cell immediately to the right of the last set of row, headers across the top and from there I'm gonna do Data and then turn on the Filter.
If you're inside of a Pivot Table, see it's grayed out you're not allowed to use a Filter inside of a Pivot Table, but from right here they happen to filter the adjacent data sets of now balance and we will that we want not the zero values and bam and there's our answer.
All right, I wanna thank Tracy for sending that question in and I wanna thank you for stopping by.
See you next time for another netcast form MrExcel.
