A new recipe on a favorite trick from Albuquerque New Mexico. First, a little roasting of Hatch chiles. Then, how to create a filter criteria in a cell below your data. This episode shows two different ways to filter - one for Ctrl+T tables and a faster way for regular ranges.
Table of Contents
(0:00) Roasting Hatch chiles
(0:37) Introduction
(0:45) Creating Criteria row for a Ctrl+T table.
(1:40) Using AutoFilter on a regular range
(2:19) Wrap up
Table of Contents
(0:00) Roasting Hatch chiles
(0:37) Introduction
(0:45) Creating Criteria row for a Ctrl+T table.
(1:40) Using AutoFilter on a regular range
(2:19) Wrap up
Transcript of the video:
Hey, this is Bill Jelen from MrExcel. Check this out.
One of the great things I learned in Albuquerque New Mexico is about the Hatch green chiles.
Once a year, they come in. We buy a case.
And we roast them.
Oh, wow I wish you could smell how awesome these smell.
Then put them six to a bag or ten to a bag. They will be used in recipes for the whole year.
After we freeze them.
Now, the other trick that I learned from Albuquerque New Mexico is the awesome trick where you can use Filter by Selection beneath the table to find everything greater than twenty thousand dollars.
Let's take a look. Learn Excel from MrExcel podcast episode 2359.
Filter Using a Criteria Below the Table. Hey, welcome back to the MrExcel netcast.
A great trick that I learned in Albuquerque New Mexico when I was doing a seminar out there. I was showing them Filter by Selection.
And they said, "Oh we got one better". So, here I have a table. A Ctrl+T Table.
And I have a cost column here. I want to see everything greater than 50.
So I type >50. That creates a new row in the table.
I keep that cell selected. I right-click and then say Filter.
Filter By Selected Cell's Value. And look at that.
They give me all of the items greater than 50. Let me undo that. I will clear that cell over here.
Let's say I want to see everybody whose name starts with A through F.
So I will type <G.
Then right-click. Filter. Filter By Selected Cell's Value.
And I get everybody who starts with "A through F". Oh, that is unfortunate.
We see the criteria row. I didn't really want to see that.
Now, those steps are if you have a Table. If you just have regular data, like this.
It's even better. So ask for everything <50.
And I have not the Filter but the Autofilter up here in the Quick Access Toolbar. So that's Customize Quick Access Toolbar.
Choose Commands Not in the Ribbon.
Find AutoFilter and click Add>>. This doesn't work in a Ctrl+T Table.
But it works in a range like this. AutoFilter is what i call Filter By Selection.
So I will choose the cell with <50. Click AutoFilter.
I get all the records less than 50.
It is a beautiful trick from Albuquerque New Mexico. That's my number one trick from Albuquerque.
Number two is roasting the Hatch Chiles. Please, down below the video, click Like.
Subscribe. And ring that bell.
Feel free to post any questions or comments in the YouTube comments below. My new book, MrExcel 2020 - Seeing Excel Clearly.
Click that "i" in the top right-hand corner for more information.
I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
One of the great things I learned in Albuquerque New Mexico is about the Hatch green chiles.
Once a year, they come in. We buy a case.
And we roast them.
Oh, wow I wish you could smell how awesome these smell.
Then put them six to a bag or ten to a bag. They will be used in recipes for the whole year.
After we freeze them.
Now, the other trick that I learned from Albuquerque New Mexico is the awesome trick where you can use Filter by Selection beneath the table to find everything greater than twenty thousand dollars.
Let's take a look. Learn Excel from MrExcel podcast episode 2359.
Filter Using a Criteria Below the Table. Hey, welcome back to the MrExcel netcast.
A great trick that I learned in Albuquerque New Mexico when I was doing a seminar out there. I was showing them Filter by Selection.
And they said, "Oh we got one better". So, here I have a table. A Ctrl+T Table.
And I have a cost column here. I want to see everything greater than 50.
So I type >50. That creates a new row in the table.
I keep that cell selected. I right-click and then say Filter.
Filter By Selected Cell's Value. And look at that.
They give me all of the items greater than 50. Let me undo that. I will clear that cell over here.
Let's say I want to see everybody whose name starts with A through F.
So I will type <G.
Then right-click. Filter. Filter By Selected Cell's Value.
And I get everybody who starts with "A through F". Oh, that is unfortunate.
We see the criteria row. I didn't really want to see that.
Now, those steps are if you have a Table. If you just have regular data, like this.
It's even better. So ask for everything <50.
And I have not the Filter but the Autofilter up here in the Quick Access Toolbar. So that's Customize Quick Access Toolbar.
Choose Commands Not in the Ribbon.
Find AutoFilter and click Add>>. This doesn't work in a Ctrl+T Table.
But it works in a range like this. AutoFilter is what i call Filter By Selection.
So I will choose the cell with <50. Click AutoFilter.
I get all the records less than 50.
It is a beautiful trick from Albuquerque New Mexico. That's my number one trick from Albuquerque.
Number two is roasting the Hatch Chiles. Please, down below the video, click Like.
Subscribe. And ring that bell.
Feel free to post any questions or comments in the YouTube comments below. My new book, MrExcel 2020 - Seeing Excel Clearly.
Click that "i" in the top right-hand corner for more information.
I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
