Microsoft Excel Tutorial:
Three ways to filter a pivot table and two of them you might not have known about. Daniel Bula asks if the trick from episode # 2359 can be used in a pivot table. Here is a great pivot table hack to filter to the south region over $5K.
Table of Contents
(0:00) Welcome and shout-out to Daniel Bula
(0:24) Excel Problem: Filter to South greater than $5K
(0:35) Filtering based on Sum of Sales in Excel
(0:50) Sorting on South
(1:00) Slicer based on the detail data
(1:20) Typing criterion below South column
(1:33) Customize quick access toolbar
(1:47) Filtering from cell below pivot table
(2:01) Clearing Filter from South
(2:20) Turning off Filter drop-downs in Excel
(2:32) Wrap-up
Three ways to filter a pivot table and two of them you might not have known about. Daniel Bula asks if the trick from episode # 2359 can be used in a pivot table. Here is a great pivot table hack to filter to the south region over $5K.
Table of Contents
(0:00) Welcome and shout-out to Daniel Bula
(0:24) Excel Problem: Filter to South greater than $5K
(0:35) Filtering based on Sum of Sales in Excel
(0:50) Sorting on South
(1:00) Slicer based on the detail data
(1:20) Typing criterion below South column
(1:33) Customize quick access toolbar
(1:47) Filtering from cell below pivot table
(2:01) Clearing Filter from South
(2:20) Turning off Filter drop-downs in Excel
(2:32) Wrap-up
Transcript of the video:
Learn Excel from MrExcel podcast episode 2372.
An awesome pivot table filter hack.
Hey, welcome back to the MrExcel netcast. I am Bill Jelen.
A shout-out to Daniel Bula who watched this video from a few weeks ago. He said “Hey, what about the same trick with pivot tables?” Daniel thanks for being a subscriber. What a great idea.
So, I've been working a lot with pivot table filters this morning.
Just making sure there was no good way to do this.
I have a pivot table here with products down the left hand side and regions across the top.
My goal is to get everyone in the South whose total for the South is greater than $5000.
If I come out here to Product, Value Filters, and ask for greater than.
My only choice is greater than where Sum of Sales.
I can't say where Sum of Sales for the South is greater than 5000.
You know that we can Sort based on a single column.
They have they have the ability to do that.
But they don't have the ability to filter based on a single column.
I even tried this cool trick where I learned something that I am not sure that I knew.
I added a Slicer based on Sales.
This actually is going back to the original data.
So if I wanted to find all of the people who bought less than a thousand dollars in a single transaction, that interestingly works.
So, that's going back to the original data. This is going to the grand total.
But there's no good way to filter to just amounts in the South.
So, here's the trick.
I am going to come just below the South region grand total.
I am going to type greater than 5 thousand.
Or maybe greater than or equal to five thousand.
It seems unlikely they would have exactly five thousand but just in case we do.
And then here is the Quick Access Toolbar. Right-click.
(Sometimes it's up here at the top.) Customize Quick Access Toolbar.
Change from Popular Commands to Commands Not In The Ribbon.
The 15th item down is AutoFilter. Click that. Click Add>>.
Now it's on your Quick Access Toolbar.
Then, with the >5000 criteria cell selected, go to AutoFilter in the Quick Access Toolbar.
It hides everything that's not greater than five thousand dollars.
It is a great trick.
Now, we are breaking some rules here.
You shouldn't be allowed to use Filters in a pivot table.
So if I want to clear. I can't just click anywhere and Clear.
It won't Clear the South. I have to open this drop down and say Clear Filter From South.
To get those other records back.
Also, if I want to get rid of these filters. These are illegal. You shouldn't be allowed to have these.
You see I can't click Filter. I have to come here to cell G4.
The cell just to the right of the Grand Total column heading.
Then I can turn the filters off.
It's definitely a hack.
But there will be plenty of times where we need to do something like that.
Hey, if you like these tips, please down below the video: Like, Subscribe, and Ring the Bell.
Feel free to post any questions or comments in the comments below.
I want to thank Daniel for that awesome idea and i want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
An awesome pivot table filter hack.
Hey, welcome back to the MrExcel netcast. I am Bill Jelen.
A shout-out to Daniel Bula who watched this video from a few weeks ago. He said “Hey, what about the same trick with pivot tables?” Daniel thanks for being a subscriber. What a great idea.
So, I've been working a lot with pivot table filters this morning.
Just making sure there was no good way to do this.
I have a pivot table here with products down the left hand side and regions across the top.
My goal is to get everyone in the South whose total for the South is greater than $5000.
If I come out here to Product, Value Filters, and ask for greater than.
My only choice is greater than where Sum of Sales.
I can't say where Sum of Sales for the South is greater than 5000.
You know that we can Sort based on a single column.
They have they have the ability to do that.
But they don't have the ability to filter based on a single column.
I even tried this cool trick where I learned something that I am not sure that I knew.
I added a Slicer based on Sales.
This actually is going back to the original data.
So if I wanted to find all of the people who bought less than a thousand dollars in a single transaction, that interestingly works.
So, that's going back to the original data. This is going to the grand total.
But there's no good way to filter to just amounts in the South.
So, here's the trick.
I am going to come just below the South region grand total.
I am going to type greater than 5 thousand.
Or maybe greater than or equal to five thousand.
It seems unlikely they would have exactly five thousand but just in case we do.
And then here is the Quick Access Toolbar. Right-click.
(Sometimes it's up here at the top.) Customize Quick Access Toolbar.
Change from Popular Commands to Commands Not In The Ribbon.
The 15th item down is AutoFilter. Click that. Click Add>>.
Now it's on your Quick Access Toolbar.
Then, with the >5000 criteria cell selected, go to AutoFilter in the Quick Access Toolbar.
It hides everything that's not greater than five thousand dollars.
It is a great trick.
Now, we are breaking some rules here.
You shouldn't be allowed to use Filters in a pivot table.
So if I want to clear. I can't just click anywhere and Clear.
It won't Clear the South. I have to open this drop down and say Clear Filter From South.
To get those other records back.
Also, if I want to get rid of these filters. These are illegal. You shouldn't be allowed to have these.
You see I can't click Filter. I have to come here to cell G4.
The cell just to the right of the Grand Total column heading.
Then I can turn the filters off.
It's definitely a hack.
But there will be plenty of times where we need to do something like that.
Hey, if you like these tips, please down below the video: Like, Subscribe, and Ring the Bell.
Feel free to post any questions or comments in the comments below.
I want to thank Daniel for that awesome idea and i want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
